How to Execute Plain SQL Queries With SQLAlchemy in Python | by Lynn Kwong

Picture from Pixabay.

There might be many instances the place you don’t need to use the superior Object Relational Mapper (ORM) options of SQLAlchemy and simply need to execute plain SQL queries. That is widespread for legacy techniques the place plain queries are used in every single place. Moreover, for information analysis-oriented tasks, it’s additionally extra widespread and extra handy to make use of plain SQL queries. For SQL queries with plenty of JOIN clauses and subqueries, it’s rather more simple to execute them straight than to translate them into advanced ORM model-based code. Moreover, we are able to retailer SQL queries in a file after which execute them with sqlparse and SQLAlchemy.

There are a lot of instruments that can be utilized to execute plain SQL queries in Python, comparable to PyMySQL, mysql-connector-python, MySQL-python, and so on. Nevertheless, it’s advisable to make use of SQLAlchemy to execute plain SQL queries. It’s because SQLAlchemy may be very versatile and can be utilized for every kind of databases. Moreover, even if you’re reluctant to be taught and use ORM fashions in your utility now, you’ll very seemingly have the possibility to learn or write ORM code sooner or later. Upon getting realized use SQLAlchemy to execute plain SQL queries, it will likely be extra simple so that you can discover different options of SQLAlchemy, together with the ORM fashions.

For this tutorial, we have to have a MySQL server working domestically. It’s advisable to make use of Docker to start out a MySQL container domestically. On this means, you should utilize any model of MySQL and might keep away from potential port conflicts.

# Create a quantity to persist the info.
$ docker quantity create mysql8-data
# Create the container for MySQL.
$ docker run --name mysql8 -d -e MYSQL_ROOT_PASSWORD=root -p 13306:3306 -v mysql8-data:/var/lib/mysql mysql:8
# Connect with the native MySQL server in Docker.
$ docker exec -it mysql8 mysql -u root -proot
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.00 sec)

Notes:

  • A quantity is created and mounted for the MySQL container so the database and tables we created later might be persistent even when the container is restarted.
  • A excessive port 13306 is used for the container to keep away from potential port battle with the system. If 13306 can be used, please select a unique one.
  • A password is specified for the root on the command line. In follow, you shouldn’t expose the root password on the command line. And you need to keep away from utilizing root to your utility straight, however use an account with restricted permissions as an alternative.
  • docker exec is used to start out a MySQL console contained in the Docker container straight. On this means, we don’t want to put in a MySQL shopper domestically. You possibly can execute the SQL queries launched beneath on this console to examine the updates we are going to make to the tables later.

If we simply need to execute plain SQL queries, the database and tables are usually already created and also you solely have to carry out SELECT/UPDATE/INSERT/DELETE (CRUD) actions on desk data. Please run the next queries within the MySQL console opened above to organize the database and desk for this tutorial:

Earlier than we use SQLAlchemy to connect with our database and execute SQL queries, we have to set up the package deal and its dependencies. It’s advisable to put in the packages in a virtual environment so it received’t mess up your system libraries. You should use venv or conda to create a digital surroundings in Python. It’s advisable to make use of conda to create a digital surroundings as a result of you’ll be able to set up a selected model of Python within the digital surroundings:

Packages put in for this tutorial:

  • SQLAlchemy — The primary package deal that might be used to execute plain SQL queries.
  • PyMySQL — Utilized by SQLAlchemy to connect with and work together with a MySQL database, as might be launched in additional element later.
  • cryptography — Utilized by SQLAlchemy for authentication.
  • sqlparse — Used to parse a uncooked string into SQL queries.
  • codetiming — Used to examine the execution time of some code.
  • ipython — Used to execute interactive Python code extra conveniently.

Create an SQLAlchemy Engine and Connection

To execute plain SQL queries with SQLAlchemy, we have to create an Engine and a Connection occasion first.

The Engine is the start line for any SQLAlchemy utility. The engine combines a Pool and a Dialect and supplies a means to connect with and work together with a database.

A connection pool is a set of lively long-running database connections that may be effectively reused. That is obligatory and essential as a result of there may be an overhead in creating new connections. By re-using a connection from the pool the applying can work a lot sooner. Usually, there may be an higher restrict for what number of connections might be created and used concurrently, as we are going to see later.

Since SQLAlchemy can work with many varieties of databases, every kind of database is seen as a dialect. Technically, a dialect defines the title of a selected database and the underlying Python Database API Specification (DBAPI). For instance, the dialect might be mysql, postgresql, oracle, sqlite, and so on.

To create an Engine occasion, we have to outline a database URL first, which has the format of:

dialect[+driver]://consumer:password@host:port/dbname
  • dialect — The dialect launched above, might be mysql, postgresql, and so on.
  • driver — The title of the DBAPI used to connect with and work together with the database. If a driver will not be specified, the default driver might be used. For MySQL, the default driver is MySQL-Python. Nevertheless, MySQL-Python doesn’t help Python3 correctly and ought to be seen as deprecated now. Subsequently, we should always at all times present a driver for finest practices. For MySQL, generally used drivers embrace mysqlclient, PyMySQL, MySQL-Connector, and so on. Usually it doesn’t matter which driver is used, so long as it’s actively maintained. Nevertheless, completely different drivers have completely different system dependencies and a few is probably not installable in your laptop. If one driver can’t be put in, you’ll be able to attempt to set up one other one. On this tutorial, we are going to use PyMySQL, which is an actively maintained and used driver for MySQL. It may be simply put in on most techniques.
  • consumer, password, host, port, dbname — The username, password, hostname, port, and default database/schema for the goal database. Please use the corresponding ones to your personal case in follow.

Let’s now create an engine for our MySQL database began in a Docker container above:

Be aware that we are able to go a set of key/worth pairs defining particular configurations to the create_engine() operate. Probably the most generally used secret’s echo, which logs the SQL queries executed by the engine. It’s useful for debugging functions, however don’t use it in a manufacturing surroundings. Moreover, for MySQL, we usually have to specify pool_size and pool_recycle, which outline what number of connections might be saved open within the pool, and after how lengthy a connection might be recycled again to the pool, respectively. On this means, we are able to hold the variety of lively connections beneath management and received’t have stale connections which can be already closed. That is obligatory as a result of MySQL will disconnect a connection routinely if no exercise is detected on a connection for eight hours.

With an Engine occasion created, we are able to then create a Connection occasion from it. We must always not execute SQL queries with the engine straight however ought to accomplish that by a Connection occasion which supplies high-level performance for the wrapped DBAPI connection.

A Connection object is created with the Engine.join() methodology. The created Connection object is a single DBAPI connection checked out from the connection pool launched above. And when the connection is closed, it’s recycled again to the pool so it may be re-used. By re-using an already created connection from the pool, we save the overhead of making a brand new one each time, which is fairly heavy. You possibly can attempt to first a connection, then shut it after which create a brand new one once more. The second time ought to be a lot sooner than the primary time as a result of the connection is checked out from the pool straight.

Let’s check it with code. You possibly can copy the next code and run it in iPython:

We are able to see that the second connection is created a lot sooner than the primary one. It’s because the second is fetched from the pool straight which is saved in reminiscence for environment friendly reuse. The time distinction might be extra distinguished in case your database is a distant one. To be taught slightly extra about logging and timer in Python, please examine the corresponding articles within the hyperlinks.

Now that we’ve got realized create an engine and connection, we are able to begin to execute plain SQL queries now.

Though generally utilized in legacy techniques, passing a plain string on to Connection.execute() is deprecated and we should always use text() to specify a plain SQL question string as an alternative. Let’s attempt to choose from the product_stocks desk created above:

Be aware which you can omit the database title, which is known as schema in MySQL, within the plain SQL question if the schema is identical because the one specified within the db_url. Nevertheless, usually we may have a couple of schema in a MySQL database. Subsequently, it’s advisable at all times to specify the schema within the plain SQL queries for readability.

Moreover, we have to shut the connection after it’s used so it may be recycled
again to the pool. On this means, different processes can reuse the connection extra effectively, as proved above. As a substitute, we are able to put it in a with() context block so the connection might be closed routinely after the code inside is executed.

It’s advisable to create a connection utilizing the with() context supervisor so that you received’t neglect to shut the connection in the long run.

Typically we have to go some parameters to a plain SQL question to solely choose some rows primarily based on specified circumstances. This may be completed with the TextClause.bindparams() methodology. As a facet notice, the textual content() assemble returns a TextClause object which represents a literal SQL textual content fragment in SQlAlchemy.

The syntax for specifying a parameter with textual content() is =:VAR_NAME. Be aware that the colon is put after the equal signal and there may be NO house after the colon! VAR_NAME might be any legitimate variable title in Python. The variable title is used to specify a worth when the question is executed with the Connection.execute() methodology.

As we see it’s not handy to specify a number of arguments with this syntax. And we can not specify non-equal arguments (better than, smaller than, in a set, and so on) with this syntax. Certainly, we should always not use textual content() and Connection.execute() for these events, as might be discovered here. As an alternative, we should always use Connection.exec_driver_sql() for these instances.

For Connection.exec_driver_sql(), we don’t go a TextClause object constructed by textual content(), however go a plain SQL question straight. Moreover, we are able to use a dictionary to specify the parameters and use an inventory of dictionaries for multiple-execute help.

Connection.exec_driver_sql() may be very helpful for SELECT queries with a number of parameters, and in addition for INSERT/UPDATE queries, which usually have a number of parameters. Let’s see it in follow.

Cheers, it really works! If you’re unfamiliar with the p.c signal (%) string formatting in Python, you’ll be able to take a look at this article. Particularly, please notice that %()s is at all times used within the plain SQL question it doesn’t matter what the kind of the worth is. The kinds are dealt with by SQLAlchemy routinely.

We are able to use different operators within the plain SQL question, not simply the equal (=) operator:

For an inventory of values to be checked, as with the IN operator, we are able to go an inventory or a tuple of parts.

We’ve got now coated execute plain SELECT queries on completely different events, which can be the most typical use case of utilizing SQLAlchemy to execute plain SQL queries. Nevertheless, you may additionally need to execute plain INSERT/UPDATE/DELETE queries in your sensible instances. Let’s discover them now.

The syntax for executing plain INSERT/UPDATE/DELETE queries is just about the identical because the one launched above utilizing Connection.exec_driver_sql(). For instance, let’s insert a brand new report to the desk:

With the above code, we inserted a brand new row to the product_stocks desk. Particularly lastrowidreturns the worth for the first key of the final inserted row.

We are able to insert a number of rows by passing in an inventory of dictionaries:

Now let’s attempt to replace a row:

And that is how a row is deleted:

Earlier than we go to the following part relating to execute SQL queries saved in a file, let’s examine a function particular to MySQL. If a recorded being inserted already exists within the database, you’ll get the duplicate entry error:

IntegrityError: (pymysql.err.IntegrityError) (1062, "Duplicate entry 'Cellphone-2022-01-10 08:00:00' for key 'product_stocks.uq_category_check_time'")

You possibly can reproduce this error by inserting a report with current class and check_time within the database. It will set off this error as a result of class and check_time is the distinctive key within the desk information.product_stocks.

To unravel this downside, we are able to use the ON DUPLICATE KEY UPDATE syntax:

Now you can execute this question a number of occasions and received’t have the “Duplicate Entry” error.

Lastly, let’s see execute a number of SQL queries saved in a textual content file. That is very helpful if you wish to execute some SQL queries repeatedly with CRON jobs, or with Airflow. As an information engineer or information scientist, you could discover it very helpful in your work.

To execute a number of SQL queries from a textual content file, we have to use the sqlparse module put in in our digital surroundings at the start of this tutorial. sqlparse can strip feedback from a string and break up it into a number of SQL queries that may be executed individually:

As demonstrated, we are able to use sqlparse.format() to strip the feedback from a uncooked string after which use sqlparse.break up() to separate it into a number of executable plain SQL queries.

The dummy SQL file we are going to use for a demo has the content material as follows:

Now let’s now use sqlparse to parse the file after which use SQLAlchemy to execute the queries. Be aware that since we usually don’t have to go parameters for SQL queries saved in a file, we are able to use the textual content() assemble as launched earlier on this tutorial.

Be aware that we’ve got added echo=Truewhen the engine is created, so the queries executed might be printed within the console:

Cheers! We’ve got now efficiently executed all of the SQL queries saved within the textual content file.

On this article, we’ve got launched the fundamental ideas of SQLAlchemy and execute plain CRUD SQL queries with the Connection.execute() and Connection.exec_driver_sql() strategies. Importantly, you have got additionally realized use sqlparse to parse uncooked information from a file and generate executable plain SQL queries. This method might be helpful for information piping and evaluation, particularly for legacy techniques.

With the data of this tutorial, you shall be pretty assured to work with databases in Python and are additionally nicely ready to be taught the extra superior Object Associated Mappers (ORM) that might be launched in later tutorials. As you can see later, we are able to use ORM to work together with databases in a extra handy and extra Pythonic means.

More Posts