Transitioning to the 2.0 Execution Pattern
In SQLAlchemy 2.0, the legacy Session.query() method is replaced by a decoupled pattern where the query is defined as a select() construct and executed via session.execute(). This shift separates the definition of the SQL statement from the session that executes it.
Replacing filter_by() and order_by()
The select() construct supports the same filtering and ordering methods previously found on the Query object. The primary difference is that these methods are now called on the Select object before it is passed to the session.
Legacy 1.x Pattern:
user = session.query(User).filter_by(username='sato').order_by(User.created_at.desc()).first()
SQLAlchemy 2.0 Pattern:
from sqlalchemy import select
stmt = select(User).filter_by(username='sato').order_by(User.created_at.desc())
user = session.execute(stmt).scalar_one_or_none()
Handling Results with scalars()
Unlike Session.query(), which returned model instances directly, session.execute() returns a Result object containing Row objects (essentially tuples). To retrieve the ORM mapped objects, you must use the .scalars() method to flatten the result set.
.scalars().all(): Replaces .query().all()..scalars().first(): Replaces .query().first()..scalar_one_or_none(): The recommended way to fetch a single unique object or None.
Impact on Third-Party Extensions
The removal of the query attribute and the Query object API creates a breaking change for extensions that rely on internal orm.Query state or attempt to monkey-patch Session.query(). If a library has not been updated for 2.0, it will likely raise an AttributeError when attempting to access legacy query methods. In these cases, the extension must be updated to use the select() pattern or a compatibility layer must be implemented.
Verification Steps
To verify your migration is correct and avoid tuple-unpacking errors, check your return types:
- Ensure
sqlalchemy.__version__ is 2.0.0 or higher.
- Verify that
session.execute(stmt) is followed by .scalars() or .scalar_one() when expecting model instances.
- Confirm that
filter_by is used for simple keyword filtering and filter is used for complex expressions.
Diagnostic Note: Are you using any custom Query subclasses? If so, those must be migrated to custom Select constructs or session-level wrappers, as Query subclassing is no longer the primary extension point.
python
sqlalchemy