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.

Transcript

1. What’s New in SQLAlchemy 2.0 Miguel Grinberg
2. About Me • Python & JavaScript Developer • Living in ☘ Ireland since 2018 • Principal Software Engineer at • Blogger at https://blog.miguelgrinberg.com • Book Author:
3. Integration With Type Checkers and IDEs
4. New Model Definition Syntax
5. New Model Syntax import sqlalchemy.orm as so import sqlalchemy as sa
6. New Model Syntax import sqlalchemy.orm as so import sqlalchemy as sa class Model(so.DeclarativeBase): pass class User(Model): pass
7. 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]]
8. 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]]
9. 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)
10. New Query Syntax
11. Query Syntax import sqlalchemy.orm as so import sqlalchemy as sa query = sa.select(User).where(User.name == 'susan')
12. 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)
13. 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()
14. 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()
15. 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()
16. 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()
17. 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()
18. 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)
19. 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()
20. 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'))
21. Relationship Improvements
22. Typed Foreign Keys class User(Model): # ... class Address(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id))
23. 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')
24. 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(...))
25. Write-Only Relationships class User(Model): # ... class Order(Model): # ... user_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey(User.id))
26. 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')
27. 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()
28. Backwards Compatibility
29. 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
30. Asyncio Support
31. 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
32. Using Alchemical
33. 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
34. Sync Alchemical Example from alchemical import Model class User(Model): # ...
35. Sync Alchemical Example from alchemical import Model class User(Model): # ... query = User.select().where(User.name == 'mary')
36. Sync Alchemical Example from alchemical import Model, Alchemical class User(Model): # ... query = User.select().where(User.name == 'mary') db = Alchemical( 'sqlite:///') db.create_all()
37. 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)
38. 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())
39. Examples For simple examples of SQLAlchemy 2, see: https://github.com/miguelgrinberg/retrofun
40. Real-World Example For a non-trivial example using SQLAlchemy 2, Flask, Alchemical, Alembic and OpenAPI documentation, see: https://github.com/miguelgrinberg/microblog-api
41. Thanks! Questions?