MySQL via SqlAlchemy¶
Description:
- Work with data in a MySQL database (synchronously and async)
Resources:
- [GitHub] https://github.com/sqlalchemy/sqlalchemy
- [Docs] https://docs.sqlalchemy.org/en/20/dialects/mysql.html
- [Tutorial] https://docs.sqlalchemy.org/en/20/tutorial/index.html
- [Examples] https://github.com/sqlalchemy/sqlalchemy/tree/main/examples
Notes:
- Example 1 uses the
asyncmyasync driver (mysql+asyncmy://). SQLAlchemy's MySQL dialect also supportsmysqlclient(mysql+mysqldb://),PyMySQL,aiomysqlandmysql-connector-python; see the dialect docs above.
Source and license:
- Some of these examples are adapted from the SQLAlchemy documentation (asyncio extension and ORM tutorial) (https://docs.sqlalchemy.org/en/20/orm/extensions/asyncio.html), Copyright 2005-2026 SQLAlchemy authors and contributors, licensed under MIT. That code stays under its own license; see THIRD_PARTY_NOTICES.md.
Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
MYSQL_HOST: hostname of your MySQL serverMYSQL_USER: MySQL user nameMYSQL_PASSWORD: password for that user
The test_db database must already exist (the asyncmy.ipynb notebook creates it).
Example 1: asyncmy¶
Connect to a remote mysql database and perform operations asynchronously
In [ ]:
Copied!
import asyncio
import os
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import VARCHAR
from sqlalchemy import Table
from sqlalchemy.ext.asyncio import create_async_engine
userpwd = os.environ["MYSQL_PASSWORD"]
host = os.environ["MYSQL_HOST"]
user = os.environ["MYSQL_USER"]
dsn = f"mysql+asyncmy://{user}:{userpwd}@{host}/test_db"
meta = MetaData()
t1 = Table(
"t1", meta, Column("id", Integer, primary_key=True), Column("name", VARCHAR(255))
)
async def async_main() -> None:
engine = create_async_engine(
dsn,
echo=True,
)
# conn is an instance of AsyncConnection
async with engine.begin() as conn:
# to support SQLAlchemy DDL methods as well as legacy functions, the
# AsyncConnection.run_sync() awaitable method will pass a "sync"
# version of the AsyncConnection object to any synchronous method,
# where synchronous IO calls will be transparently translated for
# await.
await conn.run_sync(meta.drop_all)
await conn.run_sync(meta.create_all)
# for normal statement execution, a traditional "await execute()"
# pattern is used.
await conn.execute(
t1.insert(), [{"name": "some name 1"}, {"name": "some name 2"}]
)
async with engine.connect() as conn:
# the default result object is the
# sqlalchemy.engine.Result object
result = await conn.execute(t1.select())
# the results are buffered so no await call is necessary
# for this case.
print(result.fetchall())
# for a streaming result that buffers only segments of the
# result at time, the AsyncConnection.stream() method is used.
# this returns a sqlalchemy.ext.asyncio.AsyncResult object.
async_result = await conn.stream(t1.select())
# this object supports async iteration and awaitable
# versions of methods like .all(), fetchmany(), etc.
async for row in async_result:
print(row)
# for AsyncEngine created in function scope, close and
# clean-up pooled connections
await engine.dispose()
asyncio.run(async_main())
import asyncio
import os
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import VARCHAR
from sqlalchemy import Table
from sqlalchemy.ext.asyncio import create_async_engine
userpwd = os.environ["MYSQL_PASSWORD"]
host = os.environ["MYSQL_HOST"]
user = os.environ["MYSQL_USER"]
dsn = f"mysql+asyncmy://{user}:{userpwd}@{host}/test_db"
meta = MetaData()
t1 = Table(
"t1", meta, Column("id", Integer, primary_key=True), Column("name", VARCHAR(255))
)
async def async_main() -> None:
engine = create_async_engine(
dsn,
echo=True,
)
# conn is an instance of AsyncConnection
async with engine.begin() as conn:
# to support SQLAlchemy DDL methods as well as legacy functions, the
# AsyncConnection.run_sync() awaitable method will pass a "sync"
# version of the AsyncConnection object to any synchronous method,
# where synchronous IO calls will be transparently translated for
# await.
await conn.run_sync(meta.drop_all)
await conn.run_sync(meta.create_all)
# for normal statement execution, a traditional "await execute()"
# pattern is used.
await conn.execute(
t1.insert(), [{"name": "some name 1"}, {"name": "some name 2"}]
)
async with engine.connect() as conn:
# the default result object is the
# sqlalchemy.engine.Result object
result = await conn.execute(t1.select())
# the results are buffered so no await call is necessary
# for this case.
print(result.fetchall())
# for a streaming result that buffers only segments of the
# result at time, the AsyncConnection.stream() method is used.
# this returns a sqlalchemy.ext.asyncio.AsyncResult object.
async_result = await conn.stream(t1.select())
# this object supports async iteration and awaitable
# versions of methods like .all(), fetchmany(), etc.
async for row in async_result:
print(row)
# for AsyncEngine created in function scope, close and
# clean-up pooled connections
await engine.dispose()
asyncio.run(async_main())