这篇文章上次修改于 824 天前，可能其部分内容已经发生变化，如有疑问可询问作者。

Why should we use SQLAlchemy with Python Async

As we all know, SQL operation is usually considered as the most time-consuming work in a whole project. To alleviate and reduce the impact to the overall performance caused by SQL operation, we should consider using an async way to operate SQL database.

Also, async API could help us build an concurrent database operation, that's means the program wouldn't stuck by a single time-consuming database operation. The database operation could be executed with other program features at the same time.

Next, we are going to discuss how to create an async engine, and use the engine to connect MySQL database and operate the database.

Async Engine & sessionmaker

What is engine

In SQLAlchemy, all SQL application is start from an SQL database engine, which could be created using create_engine or its async version create_async_engine(). Typically, engine is created for a single specified SQL database.

What does engine do

The engine act as a factory to produce connection which is used to operate the database. Also, the engine has a holding space for created connections called connection pool, which helps to reuse the created connections and improve performance.

How to create an engine

Below is code example to create a new async engine.

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker

# async database engine, and use aiomysql as operation core
# 
# Notice:
# If you want to use async with sqlalchemy,
# then the operation core must be async-competible
engine = create_async_engine(
    f"mysql+aiomysql://"
    f"{configs.sql.username}:{configs.sql.password}"
    f"@{configs.sql.host}/{configs.sql.dbname}"
)

# async version sessionmaker
session_maker = async_sessionmaker(_engine_instance)

Here is some description of code above.

URL-like string param

Notice that when we create engine, the primary param is a URL-like string, which contains the necessary info of the database we want to connect. It consists of several part.

• dialect Type of the database, could be mysql, sqllite etc.
• DBAPI The operation core of the engine, all operation will be execute through this DBAPI, for example, could be aiomysql.
• Database Location Provide database info like host, username, dbname, password and port. port is usually to be 3306 for MySQL.

echo param

If you want to add a logger to the SQL engine, you can add echo = True param in create_async_engine() method. Then all the operation executed will be output to stdout.

Lazy Initialization

SQLAlchemy will NOT immediately create an engine and connect to database when you call create_async_engine() method, the connection will be established when the first time you operate the database using this engine.

To learn more about creating engine, please check out SQLAlchemy Docs - Create Engine