Upgrade to Pro — share decks privately, control downloads, hide ads and more …

What's New in SQLAlchemy 2.0

Miguel Grinberg
November 12, 2023
1.4k

What's New in SQLAlchemy 2.0

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.

Miguel Grinberg

November 12, 2023
Tweet

Transcript

  1. About Me • Python & JavaScript Developer • Living in

    ☘ Ireland since 2018 • Principal Software Engineer at • Blogger at https://blog.miguelgrinberg.com • Book Author:
  2. New Model Syntax import sqlalchemy.orm as so import sqlalchemy as

    sa class Model(so.DeclarativeBase): pass class User(Model): pass
  3. New Column Syntax import sqlalchemy.orm as so import sqlalchemy as

    sa class Model(so.DeclarativeBase): pass class User(Model): id: so.Mapped[int] name: so.Mapped[str] age: so.Mapped[Optional[int]]
  4. New Column Syntax import sqlalchemy.orm as so import sqlalchemy as

    sa class Model(so.DeclarativeBase): pass class User(Model): id: so.Mapped[int] = so.mapped_column(primary_key=True) name: so.Mapped[str] = so.mapped_column(sa.String(50)) age: so.Mapped[Optional[int]]
  5. Dataclass Integration import sqlalchemy.orm as so import sqlalchemy as sa

    class Model(so.DeclarativeBase): pass class User(so.MappedAsDataclass, Model): id: so.Mapped[int] = so.mapped_column(primary_key=True, init=False) name: so.Mapped[str] = so.mapped_column(sa.String(50)) age: so.Mapped[Optional[int]] = so.mapped_column(default=None)
  6. Query Syntax import sqlalchemy.orm as so import sqlalchemy as sa

    query = sa.select(User).where(User.name == 'susan')
  7. Query Syntax: Results Iterator import sqlalchemy.orm as 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)
  8. Query Syntax: All Result Rows import sqlalchemy.orm as 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).all()
  9. Query Syntax: First Result Row import sqlalchemy.orm as 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).first()
  10. Query Syntax: Exactly One Result Row import sqlalchemy.orm as 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()
  11. Query Syntax: At Most One Result Row import sqlalchemy.orm as

    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()
  12. Query Syntax: First Item In Each Row import sqlalchemy.orm as

    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()
  13. Query Syntax: First Item In First Row import sqlalchemy.orm as

    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)
  14. Query Syntax: Explicit Commit import sqlalchemy.orm as 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) session.add(User(name='mary')) session.commit()
  15. Query Syntax: Implicit Commit import sqlalchemy.orm as 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, session.begin(): results = session.scalar(query) session.add(User(name='mary'))
  16. Typed Foreign Keys class User(Model): # ... class Address(Model): #

    ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id))
  17. Typed Relationships class User(Model): # ... addresses: so.Mapped[list['Address']] = so.relationship(back_populates='user')

    class Address(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id)) user: so.Mapped[User] = so.relationship(back_populates='addresses')
  18. Typed Relationships class User(Model): # ... addresses: so.Mapped[list['Address']] = so.relationship(back_populates='user')

    class Address(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id)) user: so.Mapped[User] = so.relationship(back_populates='addresses') with so.Session(engine) as session, session.begin(): # ... print(address.user) print(user.addresses) user.addresses.append(Address(...))
  19. Write-Only Relationships class User(Model): # ... class Order(Model): # ...

    user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id))
  20. Write-Only Relationships class User(Model): # ... orders: so.WriteOnlyMapped[ 'Order'] =

    so.relationship(back_populates= 'user') class Order(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id)) user: so.Mapped[User] = so.relationship(back_populates= 'orders')
  21. Write-Only Relationships class User(Model): # ... orders: so.WriteOnlyMapped[ 'Order'] =

    so.relationship(back_populates= 'user') class Order(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id)) user: so.Mapped[User] = so.relationship(back_populates= 'orders') with so.Session(engine) as session, session.begin(): # ... user.orders.add(Order(...)) orders = session.scalars(user.orders.select().limit(2)).all()
  22. Backwards Compatibility • Code written for legacy SQLAlchemy 1.x releases

    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
  23. Asyncio Support • No changes in models or queries •

    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
  24. Introducing Alchemical • Reduces boilerplate code to set up SQLAlchemy

    • 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
  25. Sync Alchemical Example from alchemical import Model class User(Model): #

    ... query = User.select().where(User.name == 'mary')
  26. Sync Alchemical Example from alchemical import Model, Alchemical class User(Model):

    # ... query = User.select().where(User.name == 'mary') db = Alchemical( 'sqlite:///') db.create_all()
  27. Sync Alchemical Example from alchemical import Model, Alchemical class User(Model):

    # ... query = User.select().where(User.name == 'mary') db = Alchemical( 'sqlite:///') db.create_all() with db.Session() as session: user = session.scalar(query)
  28. Async Alchemical Example from alchemical.aio import Model, Alchemical class User(Model):

    # ... query = User.select().where(User.name == 'mary') async def main(): db = Alchemical( 'sqlite:///') await db.create_all() async with db.Session() as session: user = await session.scalar(query) asyncio.run(main())
  29. Real-World Example For a non-trivial example using SQLAlchemy 2, Flask,

    Alchemical, Alembic and OpenAPI documentation, see: https://github.com/miguelgrinberg/microblog-api