Asynchronous MySQL Operations with aiomysql and SQLAlchemy in Python

The article begins by describing a migration from Tornado with MongoDB/Redis to MySQL, introducing aiomysql as the asynchronous driver and showing how to start a MySQL Docker container and connect using await aiomysql.connect(...) .

It demonstrates a simple async function that creates a connection, opens a cursor with async with conn.cursor() , executes a SELECT statement, fetches all rows, and prints the results, highlighting the use of await throughout.

Next, the guide details the three main steps of using aiomysql : creating a connection, creating a cursor, and executing SQL, emphasizing that async with automatically closes cursors and connections.

The article then addresses SQL injection risks by contrasting unsafe string interpolation with safe parameterized queries such as await cur.execute("select * from user where username = %s", username) , and explains how aiomysql escapes arguments to prevent injection.

It covers multi‑parameter queries, range queries, and join operations, showing how to pass multiple arguments, use DATE_FORMAT for date filters, and perform inner joins with SELECT ... FROM user INNER JOIN jobs ON user.id = jobs.userid . Insertion examples illustrate the need for await conn.commit() when autocommit is disabled.

For bulk inserts, the tutorial uses cur.executemany() and explains that it internally loops over execute calls. It also shows how to choose cursor types (e.g., aiomysql.cursors.DictCursor ) to receive results as dictionaries.

To avoid the overhead of repeatedly opening connections, the guide introduces aiomysql.create_pool() , demonstrates acquiring connections from the pool in concurrent coroutines with async with pool.acquire() as conn , and prints connection IDs to illustrate reuse.

Transaction handling is covered next: disabling autocommit , starting a transaction with await conn.begin() , committing with await conn.commit() , and rolling back on errors with await conn.rollback() , ensuring atomicity for multi‑statement operations.

The article then shows how to use SQLAlchemy with aiomysql.sa.create_engine , defining metadata and tables, constructing queries with sa.select() , applying where clauses, handling joins, using use_labels=True to resolve ambiguous column names, and retrieving results via attribute access.

A discussion follows on the pros and cons of foreign keys: while they enforce referential integrity, many production environments disable them for performance, so developers often enforce relationships in application code.

Finally, the guide addresses database reconnection issues by presenting a custom PMysql class with a mysql_connection_check decorator that automatically attempts to reconnect when operational errors occur, and provides example usage for resilient long‑running queries.