Learn to use SQLAlchemy asynchronously in different scenarios
Making database requests is a typical IO-bound task as it spends most of the time waiting for the response from a database server. Therefore, if your application makes a lot of database requests, then the performance can be improved dramatically by running them concurrently, which is supported by SQLAchemy, a versatile Python SQL toolkit and Object Relational Mapper.
Besides, async programming is becoming more and more popular in Python, especially with FastAPI for web development, we often need to make database requests in coroutines, namely in functions defined with the async def statement. Unfortunately, we cannot use the classical synchronous version of SQLAlchemy but need to create asynchronous versions of engines, connections, and sessions.
In this post, we will introduce how to use SQLAlchemy asynchronously in different scenarios, namely with plain SQL queries, Core, and ORM. Importantly, we will introduce how to use it in multiple async tasks concurrently, which can improve the efficiency of IO-bound applications dramatically if used properly.
We will start a MySQL server locally with Docker in which we will create the database and table for demonstration:
# Create a volume to persist the data.
$ docker volume 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 to the local MySQL server in Docker.
$ docker exec -it mysql8 mysql -u root -proot
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.3.0 |
+-----------+
1 row in set (0.00 sec)
CREATE DATABASE sales;CREATE TABLE `sales`.`customers` (
`id` SMALLINT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`job` VARCHAR(50) DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE `UQ_name` (`name`)
);
INSERT INTO sales.customers
(name, job)
VALUES
('Lynn', 'Backend Developer')
;
Then let’s create a virtual environment so we can try out the latest versions of Python and the libraries: