In this session I will introduce developers, data engineers, and database enthusiasts to the new version 2 release of SQLAlchemy, the most advanced and versatile database library in the Python ecosystem.
as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.execute(query)
sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.execute(query).all()
sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.execute(query).first()
import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.execute(query).one()
so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.execute(query).one_or_none()
so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.scalars(query) .all() .first() .one() .one_or_none()
so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.scalar(query)
as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session: results = session.scalar(query) session.add(User(name='mary')) session.commit()
as sa query = sa.select(User).where(User.name == 'susan') engine = sa.create_engine('postgresql+psycopg2://user:pw@localhost/') with so.Session(engine) as session, session.begin(): results = session.scalar(query) session.add(User(name='mary'))
should work in 2.x with minor changes • To access new features in 2.x you must upgrade your code to 2.x style • SQLAlchemy 1.4 is a bridge between the 1.x and 2.x releases ◦ Early release of some 2.x features ◦ Warnings for features that will be deprecated or removed • Migration path: 1. Upgrade to latest 1.4.x release, test and fix all issues and warnings 2. Upgrade to latest 2.x release (very minor issues expected, if any) 3. Upgrade legacy code to take advantage of new 2.x features
Use a database driver compatible with Asyncio, for example: ◦ aiosqlite for sqlite ◦ aiomysql for MySQL ◦ asyncpg for Postgres • Use create_async_engine() instead of create_engine() • Use AsyncSession instead of Session • Use await for all blocking database calls • Use stream() and stream_scalars() instead of execute() and scalars() for async iteration • Ensure all relationships are preloaded or write-only to avoid implicit I/O
• Applies recommended naming conventions to all constraints • Supports sync and async interfaces • Integrates nicely with Flask, FastAPI and other frameworks • Integrates with Alembic for database migration support • Greatly simplifies managing multiple databases • Add to your project with pip install alchemical • Disclaimer: I’m the author
# ... query = User.select().where(User.name == 'mary') db = Alchemical( 'sqlite:///') db.create_all() with db.Session() as session: user = session.scalar(query)