PostgreSQL via Sqlalchemy (with asyncio)¶
Description:
- Work with data in PostgreSQL
Resources:
- [GitHub] https://github.com/sqlalchemy/sqlalchemy
- [Docs] https://docs.sqlalchemy.org/en/20/
- [Tutorial] https://docs.sqlalchemy.org/en/20/tutorial/index.html
- [Examples] https://github.com/sqlalchemy/sqlalchemy/tree/main/examples
Notes:
- Example 1 uses the
asyncpgasync driver (postgresql+asyncpg://) and Example 2 usespsycopg2(postgresql+psycopg2://). SQLAlchemy's PostgreSQL dialect supports other drivers as well; see the 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:
POSTGRES_HOST: hostname of your PostgreSQL serverPOSTGRES_USER: PostgreSQL user namePOSTGRES_PASSWORD: password for that userPOSTGRES_DB: database to connect to (must exist)
Both examples drop and re-create a table named t1 in that database.
Example 1: sqlalchemy with asyncio¶
Read data asynchronously from a postgres database using sqlalchemy
In [ ]:
Copied!
import sqlalchemy
sqlalchemy.__version__
import os
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.ext.asyncio import create_async_engine
userpwd = os.environ["POSTGRES_PASSWORD"]
host = os.environ["POSTGRES_HOST"]
user = os.environ["POSTGRES_USER"]
database = os.environ["POSTGRES_DB"]
dsn = f"postgresql+asyncpg://{user}:{userpwd}@{host}/{database}"
meta = MetaData()
t1 = Table(
"t1", meta, Column("id", Integer, primary_key=True), Column("name", String)
)
async def async_main():
# engine is an instance of AsyncEngine
engine = create_async_engine(
dsn,
echo=True,
)
# conn is an instance of AsyncConnection
async with engine.begin() as conn:
print("Connected to database")
# 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:
print("Connected to database")
# 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)
# Jupyter already runs an event loop, so await the coroutine directly
# (in a plain Python script, use asyncio.run(async_main()) instead)
await async_main()
import sqlalchemy
sqlalchemy.__version__
import os
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.ext.asyncio import create_async_engine
userpwd = os.environ["POSTGRES_PASSWORD"]
host = os.environ["POSTGRES_HOST"]
user = os.environ["POSTGRES_USER"]
database = os.environ["POSTGRES_DB"]
dsn = f"postgresql+asyncpg://{user}:{userpwd}@{host}/{database}"
meta = MetaData()
t1 = Table(
"t1", meta, Column("id", Integer, primary_key=True), Column("name", String)
)
async def async_main():
# engine is an instance of AsyncEngine
engine = create_async_engine(
dsn,
echo=True,
)
# conn is an instance of AsyncConnection
async with engine.begin() as conn:
print("Connected to database")
# 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:
print("Connected to database")
# 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)
# Jupyter already runs an event loop, so await the coroutine directly
# (in a plain Python script, use asyncio.run(async_main()) instead)
await async_main()
Example 2: sqlalchemy with psycopg2¶
Read data synchronously from a postgres database using sqlalchemy
In [ ]:
Copied!
import sqlalchemy
sqlalchemy.__version__
import os
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy import create_engine
userpwd = os.environ["POSTGRES_PASSWORD"]
host = os.environ["POSTGRES_HOST"]
user = os.environ["POSTGRES_USER"]
database = os.environ["POSTGRES_DB"]
dsn = f"postgresql+psycopg2://{user}:{userpwd}@{host}/{database}"
meta = MetaData()
t1 = Table(
"t1", meta, Column("id", Integer, primary_key=True), Column("name", String)
)
engine = create_engine(dsn, echo=True)
# engine.begin() opens a connection and a transaction that commits at the end of the block
with engine.begin() as conn:
print("Connected to database")
meta.drop_all(conn)
meta.create_all(conn)
conn.execute(t1.insert(), [{"name": "some name 1"}, {"name": "some name 2"}])
with engine.connect() as conn:
result = conn.execute(t1.select())
for row in result:
print(row)
engine.dispose()
import sqlalchemy
sqlalchemy.__version__
import os
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy import create_engine
userpwd = os.environ["POSTGRES_PASSWORD"]
host = os.environ["POSTGRES_HOST"]
user = os.environ["POSTGRES_USER"]
database = os.environ["POSTGRES_DB"]
dsn = f"postgresql+psycopg2://{user}:{userpwd}@{host}/{database}"
meta = MetaData()
t1 = Table(
"t1", meta, Column("id", Integer, primary_key=True), Column("name", String)
)
engine = create_engine(dsn, echo=True)
# engine.begin() opens a connection and a transaction that commits at the end of the block
with engine.begin() as conn:
print("Connected to database")
meta.drop_all(conn)
meta.create_all(conn)
conn.execute(t1.insert(), [{"name": "some name 1"}, {"name": "some name 2"}])
with engine.connect() as conn:
result = conn.execute(t1.select())
for row in result:
print(row)
engine.dispose()