Migrating to SQLAlchemy 2.0

About this document

SQLAlchemy 2.0 presents a major shift for a wide variety of key SQLAlchemy usage patterns in both the Core and ORM components. The goal of this release is to make a slight readjustment in some of the most fundamental assumptions of SQLAlchemy since its early beginnings, and to deliver a newly streamlined usage model that is hoped to be significantly more minimalist and consistent between the Core and ORM components, as well as more capable. The move of Python to be Python 3 only as well as the emergence of gradual typing systems for Python 3 are the initial inspirations for this shift, as is the changing nature of the Python community which now includes not just hardcore database programmers but a vast new community of data scientists and students of many different disciplines.

SQLAlchemy started with Python 2.3 which had no context managers, no function decorators, Unicode as a second class feature, and a variety of other shortcomings that would be unknown today. The biggest changes in SQLAlchemy 2.0 are targeting the residual assumptions left over from this early period in SQLAlchemy’s development as well as the leftover artifacts resulting from the incremental introduction of key API features such as Query and Declarative. It also hopes standardize some newer capabilities that have proven to be very effective.

Overview

The SQLAlchemy 2.0 transition presents itself in the SQLAlchemy 1.4 release as a series of steps that allow an application of any size or complexity to be migrated to SQLAlchemy 2.0 using a gradual, iterative process. Lessons learned from the Python 2 to Python 3 transition have inspired a system that intends to as great a degree as possible to not require any “breaking” changes, or any change that would need to be made universally or not at all.

As a means of both proving the 2.0 architecture as well as allowing a fully iterative transition environment, the entire scope of 2.0’s new APIs and features are present and available within the 1.4 series; this includes major new areas of functionality such as the SQL caching system, the new ORM statement execution model, new transactional paradigms for both ORM and Core, a new ORM declarative system that unifies classical and declarative mapping, support for Python dataclasses, and asyncio support for Core and ORM.

The steps to achieve 2.0 migration are in the following subsections; overall, the general strategy is that once an application runs on 1.4 with all warning flags turned on and does not emit any 2.0-deprecation warnings, it is now mostly cross-compatible with SQLAlchemy 2.0. Please note there may be additional API and behavioral changes that may behave differently when running against SQLAlchemy 2.0; always test code against an actual SQLAlchemy 2.0 release as the final step in migrating.

First Prerequisite, step one - A Working 1.3 Application

The first step is getting an existing application onto 1.4, in the case of a typical non trivial application, is to ensure it runs on SQLAlchemy 1.3 with no deprecation warnings. Release 1.4 does have a few changes linked to conditions that warn in previous version, including some warnings that were introduced in 1.3, in particular some changes to the behavior of the relationship.viewonly and relationship.sync_backref flags.

For best results, the application should be able to run, or pass all of its tests, with the latest SQLAlchemy 1.3 release with no SQLAlchemy deprecation warnings; these are warnings emitted for the SADeprecationWarning class.

First Prerequisite, step two - A Working 1.4 Application

Once the application is good to go on SQLAlchemy 1.3, the next step is to get it running on SQLAlchemy 1.4. In the vast majority of cases, applications should run without problems from SQLAlchemy 1.3 to 1.4. However, it’s always the case between any 1.x and 1.y release, APIs and behaviors have changed either subtly or in some cases a little less subtly, and the SQLAlchemy project always gets a good deal of regression reports for the first few months.

The 1.x->1.y release process usually has a few changes around the margins that are a little bit more dramatic and are based around use cases that are expected to be very seldom if at all used. For 1.4, the changes identified as being in this realm are as follows:

For the full overview of SQLAlchemy 1.4 changes, see the What’s New in SQLAlchemy 1.4? document.

Migration to 2.0 Step One - Python 3 only (Python 3.7 minimum for 2.0 compatibility)

SQLAlchemy 2.0 was first inspired by the fact that Python 2’s EOL was in 2020. SQLAlchemy is taking a longer period of time than other major projects to drop Python 2.7 support. However, in order to use SQLAlchemy 2.0, the application will need to be runnable on at least Python 3.7. SQLAlchemy 1.4 supports Python 3.6 or newer within the Python 3 series; throughout the 1.4 series, the application can remain running on Python 2.7 or on at least Python 3.6. Version 2.0 however starts at Python 3.7.

Migration to 2.0 Step Two - Turn on RemovedIn20Warnings

SQLAlchemy 1.4 features a conditional deprecation warning system inspired by the Python “-3” flag that would indicate legacy patterns in a running application. For SQLAlchemy 1.4, the RemovedIn20Warning deprecation class is emitted only when an environment variable SQLALCHEMY_WARN_20 is set to either of true or 1.

Given the example program below:

from sqlalchemy import column
from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy import table


engine = create_engine("sqlite://")

engine.execute("CREATE TABLE foo (id integer)")
engine.execute("INSERT INTO foo (id) VALUES (1)")


foo = table("foo", column("id"))
result = engine.execute(select([foo.c.id]))

print(result.fetchall())

The above program uses several patterns that many users will already identify as “legacy”, namely the use of the Engine.execute() method that’s part of the “connectionless execution” API. When we run the above program against 1.4, it returns a single line:

$ python test3.py
[(1,)]

To enable “2.0 deprecations mode”, we enable the SQLALCHEMY_WARN_20=1 variable, and additionally ensure that a warnings filter that will not suppress any warnings is selected:

SQLALCHEMY_WARN_20=1 python -W always::DeprecationWarning test3.py

Since the reported warning location is not always in the correct place, locating the offending code may be difficult without the full stacktrace. This can be achieved by transforming the warnings to exceptions by specifying the error warning filter, using Python option -W error::DeprecationWarning.

With warnings turned on, our program now has a lot to say:

$ SQLALCHEMY_WARN_20=1 python2 -W always::DeprecationWarning test3.py
test3.py:9: RemovedIn20Warning: The Engine.execute() function/method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by the Connection.execute() method of Connection, or in the ORM by the Session.execute() method of Session. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  engine.execute("CREATE TABLE foo (id integer)")
/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:2856: RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  return connection.execute(statement, *multiparams, **params)
/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:1639: RemovedIn20Warning: The current statement is being autocommitted using implicit autocommit.Implicit autocommit will be removed in SQLAlchemy 2.0.   Use the .begin() method of Engine or Connection in order to use an explicit transaction for DML and DDL statements. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  self._commit_impl(autocommit=True)
test3.py:10: RemovedIn20Warning: The Engine.execute() function/method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by the Connection.execute() method of Connection, or in the ORM by the Session.execute() method of Session. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  engine.execute("INSERT INTO foo (id) VALUES (1)")
/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:2856: RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  return connection.execute(statement, *multiparams, **params)
/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py:1639: RemovedIn20Warning: The current statement is being autocommitted using implicit autocommit.Implicit autocommit will be removed in SQLAlchemy 2.0.   Use the .begin() method of Engine or Connection in order to use an explicit transaction for DML and DDL statements. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  self._commit_impl(autocommit=True)
/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/selectable.py:4271: RemovedIn20Warning: The legacy calling style of select() is deprecated and will be removed in SQLAlchemy 2.0.  Please use the new calling style described at select(). (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  return cls.create_legacy_select(*args, **kw)
test3.py:14: RemovedIn20Warning: The Engine.execute() function/method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. All statement execution in SQLAlchemy 2.0 is performed by the Connection.execute() method of Connection, or in the ORM by the Session.execute() method of Session. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  result = engine.execute(select([foo.c.id]))
[(1,)]

With the above guidance, we can migrate our program to use 2.0 styles, and as a bonus our program is much clearer:

from sqlalchemy import column
from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy import table
from sqlalchemy import text


engine = create_engine("sqlite://")

# don't rely on autocommit for DML and DDL
with engine.begin() as connection:
    # use connection.execute(), not engine.execute()
    # use the text() construct to execute textual SQL
    connection.execute(text("CREATE TABLE foo (id integer)"))
    connection.execute(text("INSERT INTO foo (id) VALUES (1)"))


foo = table("foo", column("id"))

with engine.connect() as connection:
    # use connection.execute(), not engine.execute()
    # select() now accepts column / table expressions positionally
    result = connection.execute(select(foo.c.id))

print(result.fetchall())

The goal of “2.0 deprecations mode” is that a program which runs with no RemovedIn20Warning warnings with “2.0 deprecations mode” turned on is then ready to run in SQLAlchemy 2.0.

Migration to 2.0 Step Three - Resolve all RemovedIn20Warnings

Code can be developed iteratively to resolve these warnings. Within the SQLAlchemy project itself, the approach taken is as follows:

  1. enable the SQLALCHEMY_WARN_20=1 environment variable in the test suite, for SQLAlchemy this is in the tox.ini file

  2. Within the setup for the test suite, set up a series of warnings filters that will select for particular subsets of warnings to either raise an exception, or to be ignored (or logged). Work with just one subgroup of warnings at a time. Below, a warnings filter is configured for an application where the change to the Core level .execute() calls will be needed in order for all tests to pass, but all other 2.0-style warnings will be suppressed:

    import warnings
from sqlalchemy import exc

# for warnings not included in regex-based filter below, just log
warnings.filterwarnings("always", category=exc.RemovedIn20Warning)

# for warnings related to execute() / scalar(), raise
for msg in [
    r"The (?:Executable|Engine)\.(?:execute|scalar)\(\) function",
    r"The current statement is being autocommitted using implicit " "autocommit,",
    r"The connection.execute\(\) method in SQLAlchemy 2.0 will accept "
    "parameters as a single dictionary or a single sequence of "
    "dictionaries only.",
    r"The Connection.connect\(\) function/method is considered legacy",
    r".*DefaultGenerator.execute\(\)",
]:
    warnings.filterwarnings(
        "error",
        message=msg,
        category=exc.RemovedIn20Warning,
    )

  3. As each sub-category of warnings are resolved in the application, new warnings that are caught by the “always” filter can be added to the list of “errors” to be resolved.

  4. Once no more warnings are emitted, the filter can be removed.

Migration to 2.0 Step Four - Use the future flag on Engine

The Engine object features an updated transaction-level API in version 2.0. In 1.4, this new API is available by passing the flag future=True to the create_engine() function.

When the create_engine.future flag is used, the Engine and Connection objects support the 2.0 API fully and not at all any legacy features, including the new argument format for Connection.execute(), the removal of “implicit autocommit”, string statements require the text() construct unless the Connection.exec_driver_sql() method is used, and connectionless execution from the Engine is removed.

If all RemovedIn20Warning warnings have been resolved regarding use of the Engine and Connection, then the create_engine.future flag may be enabled and there should be no errors raised.

The new engine is described at Engine which delivers a new Connection object. In addition to the above changes, the, Connection object features Connection.commit() and Connection.rollback() methods, to support the new “commit-as-you-go” mode of operation:

from sqlalchemy import create_engine

engine = create_engine("postgresql:///")

with engine.connect() as conn:
    conn.execute(text("insert into table (x) values (:some_x)"), {"some_x": 10})

    conn.commit()  # commit as you go

Migration to 2.0 Step Five - Use the future flag on Session

The Session object also features an updated transaction/connection level API in version 2.0. This API is available in 1.4 using the Session.future flag on Session or on sessionmaker.

The Session object supports “future” mode in place, and involves these changes:

  1. The Session no longer supports “bound metadata” when it resolves the engine to be used for connectivity. This means that an Engine object must be passed to the constructor (this may be either a legacy or future style object).

  2. The Session.begin.subtransactions flag is no longer supported.

  3. The Session.commit() method always emits a COMMIT to the database, rather than attempting to reconcile “subtransactions”.

  4. The Session.rollback() method always rolls back the full stack of transactions at once, rather than attempting to keep “subtransactions” in place.

The Session also supports more flexible creational patterns in 1.4 which are now closely matched to the patterns used by the Connection object. Highlights include that the Session may be used as a context manager:

from sqlalchemy.orm import Session

with Session(engine) as session:
    session.add(MyObject())
    session.commit()

In addition, the sessionmaker object supports a sessionmaker.begin() context manager that will create a Session and begin /commit a transaction in one block:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(engine)

with Session.begin() as session:
    session.add(MyObject())

See the section Session-level vs. Engine level transaction control for a comparison of Session creational patterns compared to those of Connection.

Once the application passes all tests/ runs with SQLALCHEMY_WARN_20=1 and all exc.RemovedIn20Warning occurrences set to raise an error, the application is ready!.

The sections that follow will detail the specific changes to make for all major API modifications.

Migration to 2.0 Step Six - Add __allow_unmapped__ to explicitly typed ORM models

SQLAlchemy 2.0 has new support for runtime interpretation of PEP 484 typing annotations on ORM models. A requirement of these annotations is that they must make use of the Mapped generic container. Annotations which don’t use Mapped which link to constructs such as relationship() will raise errors in Python, as they suggest mis-configurations.

SQLAlchemy applications that use the Mypy plugin with explicit annotations that don’t use Mapped in their annotations are subject to these errors, as would occur in the example below:

Base = declarative_base()


class Foo(Base):
    __tablename__ = "foo"

    id: int = Column(Integer, primary_key=True)

    # will raise
    bars: list["Bar"] = relationship("Bar", back_populates="foo")


class Bar(Base):
    __tablename__ = "bar"

    id: int = Column(Integer, primary_key=True)
    foo_id = Column(ForeignKey("foo.id"))

    # will raise
    foo: Foo = relationship(Foo, back_populates="bars", cascade="all")

Above, the Foo.bars and Bar.foo relationship() declarations will raise an error at class construction time because they don’t use Mapped (by contrast, the annotations that use Column are ignored by 2.0, as these are able to be recognized as a legacy configuration style). To allow all annotations that don’t use Mapped to pass without error, the __allow_unmapped__ attribute may be used on the class or any subclasses, which will cause the annotations in these cases to be ignored completely by the new Declarative system.

Note

The __allow_unmapped__ directive applies only to the runtime behavior of the ORM. It does not affect the behavior of Mypy, and the above mapping as written still requires that the Mypy plugin be installed. For fully 2.0 style ORM models that will type correctly under Mypy without a plugin, see the section named “Migrating an Existing Mapping” in the “What’s New in SQLAlchemy 2.0?” document of the SQLAlchemy 2.0 documentation; this is the SQLAlchemy 1.4 documentation.

The example below illustrates the application of __allow_unmapped__ to the Declarative Base class, where it will take effect for all classes that descend from Base:

# qualify the base with __allow_unmapped__.  Can also be
# applied to classes directly if preferred
class Base:
    __allow_unmapped__ = True


Base = declarative_base(cls=Base)

# existing mapping proceeds, Declarative will ignore any annotations
# which don't include ``Mapped[]``
class Foo(Base):
    __tablename__ = "foo"

    id: int = Column(Integer, primary_key=True)

    bars: list["Bar"] = relationship("Bar", back_populates="foo")


class Bar(Base):
    __tablename__ = "bar"

    id: int = Column(Integer, primary_key=True)
    foo_id = Column(ForeignKey("foo.id"))

    foo: Foo = relationship(Foo, back_populates="bars", cascade="all")

Changed in version 2.0.0beta3: - improved the __allow_unmapped__ attribute support to allow for 1.4-style explicit annotated relationships that don’t use Mapped to remain usable.

Migration to 2.0 Step Seven - Test against a SQLAlchemy 2.0 Release

As mentioned previously, SQLAlchemy 2.0 has additional API and behavioral changes that are intended to be backwards compatible, however may introduce some incompatibilities nonetheless. Therefore after the overall porting process is complete, the final step is to test against the most recent release of SQLAlchemy 2.0 to correct for any remaining issues that might be present.

2.0 Migration - Core Connection / Transaction

Library-level (but not driver level) “Autocommit” removed from both Core and ORM

Synopsis

In SQLAlchemy 1.x, the following statements will automatically commit the underlying DBAPI transaction, but in SQLAlchemy 2.0 this will not occur:

conn = engine.connect()

# won't autocommit in 2.0
conn.execute(some_table.insert().values(foo="bar"))

Nor will this autocommit:

conn = engine.connect()

# won't autocommit in 2.0
conn.execute(text("INSERT INTO table (foo) VALUES ('bar')"))

The common workaround for custom DML that requires commit, the “autocommit” execution option, will be removed:

conn = engine.connect()

# won't autocommit in 2.0
conn.execute(text("EXEC my_procedural_thing()").execution_options(autocommit=True))

Migration to 2.0

The method that is cross-compatible with 1.x style and 2.0 style execution is to make use of the Connection.begin() method, or the Engine.begin() context manager:

with engine.begin() as conn:
    conn.execute(some_table.insert().values(foo="bar"))
    conn.execute(some_other_table.insert().values(bat="hoho"))

with engine.connect() as conn:
    with conn.begin():
        conn.execute(some_table.insert().values(foo="bar"))
        conn.execute(some_other_table.insert().values(bat="hoho"))

with engine.begin() as conn:
    conn.execute(text("EXEC my_procedural_thing()"))

When using 2.0 style with the create_engine.future flag, “commit as you go” style may also be used, as the Connection features autobegin behavior, which takes place when a statement is first invoked in the absence of an explicit call to Connection.begin():

with engine.connect() as conn:
    conn.execute(some_table.insert().values(foo="bar"))
    conn.execute(some_other_table.insert().values(bat="hoho"))

    conn.commit()

When 2.0 deprecations mode is enabled, a warning will emit when the deprecated “autocommit” feature takes place, indicating those places where an explicit transaction should be noted.

Discussion

SQLAlchemy’s first releases were at odds with the spirit of the Python DBAPI (PEP 249) in that it tried to hide PEP 249’s emphasis on “implicit begin” and “explicit commit” of transactions. Fifteen years later we now see this was essentially a mistake, as SQLAlchemy’s many patterns that attempt to “hide” the presence of a transaction make for a more complex API which works inconsistently and is extremely confusing to especially those users who are new to relational databases and ACID transactions in general. SQLAlchemy 2.0 will do away with all attempts to implicitly commit transactions, and usage patterns will always require that the user demarcate the “beginning” and the “end” of a transaction in some way, in the same way as reading or writing to a file in Python has a “beginning” and an “end”.

In the case of autocommit for a pure textual statement, there is actually a regular expression that parses every statement in order to detect autocommit! Not surprisingly, this regex is continuously failing to accommodate for various kinds of statements and stored procedures that imply a “write” to the database, leading to ongoing confusion as some statements produce results in the database and others don’t. By preventing the user from being aware of the transactional concept, we get a lot of bug reports on this one because users don’t understand that databases always use a transaction, whether or not some layer is autocommitting it.

SQLAlchemy 2.0 will require that all database actions at every level be explicit as to how the transaction should be used. For the vast majority of Core use cases, it’s the pattern that is already recommended:

with engine.begin() as conn:
    conn.execute(some_table.insert().values(foo="bar"))

For “commit as you go, or rollback instead” usage, which resembles how the Session is normally used today, the “future” version of Connection, which is the one that is returned from an Engine that was created using the create_engine.future flag, includes new Connection.commit() and Connection.rollback() methods, which act upon a transaction that is now begun automatically when a statement is first invoked:

# 1.4 / 2.0 code

from sqlalchemy import create_engine

engine = create_engine(..., future=True)

with engine.connect() as conn:
    conn.execute(some_table.insert().values(foo="bar"))
    conn.commit()

    conn.execute(text("some other SQL"))
    conn.rollback()

Above, the engine.connect() method will return a Connection that features autobegin, meaning the begin() event is emitted when the execute method is first used (note however that there is no actual “BEGIN” in the Python DBAPI). “autobegin” is a new pattern in SQLAlchemy 1.4 that is featured both by Connection as well as the ORM Session object; autobegin allows that the Connection.begin() method may be called explicitly when the object is first acquired, for schemes that wish to demarcate the beginning of the transaction, but if the method is not called, then it occurs implicitly when work is first done on the object.

The removal of “autocommit” is closely related to the removal of “connectionless” execution discussed at “Implicit” and “Connectionless” execution, “bound metadata” removed. All of these legacy patterns built up from the fact that Python did not have context managers or decorators when SQLAlchemy was first created, so there were no convenient idiomatic patterns for demarcating the use of a resource.

Driver-level autocommit remains available

True “autocommit” behavior is now widely available with most DBAPI implementations, and is supported by SQLAlchemy via the Connection.execution_options.isolation_level parameter as discussed at Setting Transaction Isolation Levels including DBAPI Autocommit. True autocommit is treated as an “isolation level” so that the structure of application code does not change when autocommit is used; the Connection.begin() context manager as well as methods like Connection.commit() may still be used, they are simply no-ops at the database driver level when DBAPI-level autocommit is turned on.

“Implicit” and “Connectionless” execution, “bound metadata” removed

Synopsis

The ability to associate an Engine with a MetaData object, which then makes available a range of so-called “connectionless” execution patterns, is removed:

from sqlalchemy import MetaData

metadata_obj = MetaData(bind=engine)  # no longer supported

metadata_obj.create_all()  # requires Engine or Connection

metadata_obj.reflect()  # requires Engine or Connection

t = Table("t", metadata_obj, autoload=True)  # use autoload_with=engine

result = engine.execute(t.select())  # no longer supported

result = t.select().execute()  # no longer supported

Migration to 2.0

For schema level patterns, explicit use of an Engine or Connection is required. The Engine may still be used directly as the source of connectivity for a MetaData.create_all() operation or autoload operation. For executing statements, only the Connection object has a Connection.execute() method (in addition to the ORM-level Session.execute() method):

from sqlalchemy import MetaData

metadata_obj = MetaData()

# engine level:

# create tables
metadata_obj.create_all(engine)

# reflect all tables
metadata_obj.reflect(engine)

# reflect individual table
t = Table("t", metadata_obj, autoload_with=engine)


# connection level:


with engine.connect() as connection:
    # create tables, requires explicit begin and/or commit:
    with connection.begin():
        metadata_obj.create_all(connection)

    # reflect all tables
    metadata_obj.reflect(connection)

    # reflect individual table
    t = Table("t", metadata_obj, autoload_with=connection)

    # execute SQL statements
    result = conn.execute(t.select())

Discussion

The Core documentation has already standardized on the desired pattern here, so it is likely that most modern applications would not have to change much in any case, however there are likely many applications that still rely upon engine.execute() calls that will need to be adjusted.

“Connectionless” execution refers to the still fairly popular pattern of invoking .execute() from the Engine:

result = engine.execute(some_statement)

The above operation implicitly procures a Connection object, and runs the .execute() method on it. While this appears to be a simple convenience feature, it has been shown to give rise to several issues:

  • Programs that feature extended strings of engine.execute() calls have become prevalent, overusing a feature that was intended to be seldom used and leading to inefficient non-transactional applications. New users are confused as to the difference between engine.execute() and connection.execute() and the nuance between these two approaches is often not understood.

  • The feature relies upon the “application level autocommit” feature in order to make sense, which itself is also being removed as it is also inefficient and misleading.

  • In order to handle result sets, Engine.execute returns a result object with unconsumed cursor results. This cursor result necessarily still links to the DBAPI connection which remains in an open transaction, all of which is released once the result set has fully consumed the rows waiting within the cursor. This means that Engine.execute does not actually close out the connection resources that it claims to be managing when the call is complete. SQLAlchemy’s “autoclose” behavior is well-tuned enough that users don’t generally report any negative effects from this system, however it remains an overly implicit and inefficient system left over from SQLAlchemy’s earliest releases.

The removal of “connectionless” execution then leads to the removal of an even more legacy pattern, that of “implicit, connectionless” execution:

result = some_statement.execute()

The above pattern has all the issues of “connectionless” execution, plus it relies upon the “bound metadata” pattern, which SQLAlchemy has tried to de-emphasize for many years. This was SQLAlchemy’s very first advertised usage model in version 0.1, which became obsolete almost immediately when the Connection object was introduced and later Python context managers provided a better pattern for using resources within a fixed scope.

With implicit execution removed, “bound metadata” itself also no longer has a purpose within this system. In modern use “bound metadata” tends to still be somewhat convenient for working within MetaData.create_all() calls as well as with Session objects, however having these functions receive an Engine explicitly provides for clearer application design.

Many Choices becomes One Choice

Overall, the above executional patterns were introduced in SQLAlchemy’s very first 0.1 release before the Connection object even existed. After many years of de-emphasizing these patterns, “implicit, connectionless” execution and “bound metadata” are no longer as widely used so in 2.0 we seek to finally reduce the number of choices for how to execute a statement in Core from “many choices”:

# many choices

# bound metadata?
metadata_obj = MetaData(engine)

# or not?
metadata_obj = MetaData()

# execute from engine?
result = engine.execute(stmt)

# or execute the statement itself (but only if you did
# "bound metadata" above, which means you can't get rid of "bound" if any
# part of your program uses this form)
result = stmt.execute()

# execute from connection, but it autocommits?
conn = engine.connect()
conn.execute(stmt)

# execute from connection, but autocommit isn't working, so use the special
# option?
conn.execution_options(autocommit=True).execute(stmt)

# or on the statement ?!
conn.execute(stmt.execution_options(autocommit=True))

# or execute from connection, and we use explicit transaction?
with conn.begin():
    conn.execute(stmt)

to “one choice”, where by “one choice” we mean “explicit connection with explicit transaction”; there are still a few ways to demarcate transaction blocks depending on need. The “one choice” is to procure a Connection and then to explicitly demarcate the transaction, in the case that the operation is a write operation:

# one choice - work with explicit connection, explicit transaction
# (there remain a few variants on how to demarcate the transaction)

# "begin once" - one transaction only per checkout
with engine.begin() as conn:
    result = conn.execute(stmt)

# "commit as you go" - zero or more commits per checkout
with engine.connect() as conn:
    result = conn.execute(stmt)
    conn.commit()

# "commit as you go" but with a transaction block instead of autobegin
with engine.connect() as conn:
    with conn.begin():
        result = conn.execute(stmt)

execute() method more strict, execution options are more prominent

Synopsis

The argument patterns that may be used with the sqlalchemy.engine.Connection() execute method in SQLAlchemy 2.0 are highly simplified, removing many previously available argument patterns. The new API in the 1.4 series is described at sqlalchemy.future.Connection(). The examples below illustrate the patterns that require modification:

connection = engine.connect()

# direct string SQL not supported; use text() or exec_driver_sql() method
result = connection.execute("select * from table")

# positional parameters no longer supported, only named
# unless using exec_driver_sql()
result = connection.execute(table.insert(), ("x", "y", "z"))

# **kwargs no longer accepted, pass a single dictionary
result = connection.execute(table.insert(), x=10, y=5)

# multiple *args no longer accepted, pass a list
result = connection.execute(
    table.insert(), {"x": 10, "y": 5}, {"x": 15, "y": 12}, {"x": 9, "y": 8}
)

Migration to 2.0

The new Connection.execute() method now accepts a subset of the argument styles that are accepted by the 1.x Connection.execute() method, so the following code is cross-compatible between 1.x and 2.0:

connection = engine.connect()

from sqlalchemy import text

result = connection.execute(text("select * from table"))

# pass a single dictionary for single statement execution
result = connection.execute(table.insert(), {"x": 10, "y": 5})

# pass a list of dictionaries for executemany
result = connection.execute(
    table.insert(), [{"x": 10, "y": 5}, {"x": 15, "y": 12}, {"x": 9, "y": 8}]
)

Discussion

The use of *args and **kwargs has been removed both to remove the complexity of guessing what kind of arguments were passed to the method, as well as to make room for other options, namely the Connection.execute.execution_options dictionary that is now available to provide options on a per statement basis. The method is also modified so that its use pattern matches that of the Session.execute() method, which is a much more prominent API in 2.0 style.

The removal of direct string SQL is to resolve an inconsistency between Connection.execute() and Session.execute(), where in the former case the string is passed to the driver raw, and in the latter case it is first converted to a text() construct. By allowing only text() this also limits the accepted parameter format to “named” and not “positional”. Finally, the string SQL use case is becoming more subject to scrutiny from a security perspective, and the text() construct has come to represent an explicit boundary into the textual SQL realm where attention to untrusted user input must be given.

Result rows act like named tuples

Synopsis

Version 1.4 introduces an all new Result object that in turn returns Row objects, which behave like named tuples when using “future” mode:

engine = create_engine(..., future=True)  # using future mode

with engine.connect() as conn:
    result = conn.execute(text("select x, y from table"))

    row = result.first()  # suppose the row is (1, 2)

    "x" in row  # evaluates to False, in 1.x / future=False, this would be True

    1 in row  # evaluates to True, in 1.x / future=False, this would be False

Migration to 2.0

Application code or test suites that are testing for a particular key being present in a row would need to test the row.keys() collection instead. This is however an unusual use case as a result row is typically used by code that already knows what columns are present within it.

Discussion

Already part of 1.4, the previous KeyedTuple class that was used when selecting rows from the Query object has been replaced by the Row class, which is the base of the same Row that comes back with Core statement results when using the create_engine.future flag with Engine (when the create_engine.future flag is not set, Core result sets use the LegacyRow subclass, which maintains backwards-compatible behaviors for the __contains__() method; ORM exclusively uses the Row class directly).

This Row behaves like a named tuple, in that it acts as a sequence but also supports attribute name access, e.g. row.some_column. However, it also provides the previous “mapping” behavior via the special attribute row._mapping, which produces a Python mapping such that keyed access such as row["some_column"] can be used.

In order to receive results as mappings up front, the mappings() modifier on the result can be used:

from sqlalchemy.future.orm import Session

session = Session(some_engine)

result = session.execute(stmt)
for row in result.mappings():
    print("the user is: %s" % row["User"])

The Row class as used by the ORM also supports access via entity or attribute:

from sqlalchemy.future import select

stmt = select(User, Address).join(User.addresses)

for row in session.execute(stmt).mappings():
    print("the user is: %s  the address is: %s" % (row[User], row[Address]))

See also

RowProxy is no longer a “proxy”; is now called Row and behaves like an enhanced named tuple

2.0 Migration - Core Usage

select() no longer accepts varied constructor arguments, columns are passed positionally

synopsis

The select() construct as well as the related method FromClause.select() will no longer accept keyword arguments to build up elements such as the WHERE clause, FROM list and ORDER BY. The list of columns may now be sent positionally, rather than as a list. Additionally, the case() construct now accepts its WHEN criteria positionally, rather than as a list:

# select_from / order_by keywords no longer supported
stmt = select([1], select_from=table, order_by=table.c.id)

# whereclause parameter no longer supported
stmt = select([table.c.x], table.c.id == 5)

# whereclause parameter no longer supported
stmt = table.select(table.c.id == 5)

# list emits a deprecation warning
stmt = select([table.c.x, table.c.y])

# list emits a deprecation warning
case_clause = case(
    [(table.c.x == 5, "five"), (table.c.x == 7, "seven")],
    else_="neither five nor seven",
)

Migration to 2.0

Only the “generative” style of select() will be supported. The list of columns / tables to SELECT from should be passed positionally. The select() construct in SQLAlchemy 1.4 accepts both the legacy styles and the new styles using an auto-detection scheme, so the code below is cross-compatible with 1.4 and 2.0:

# use generative methods
stmt = select(1).select_from(table).order_by(table.c.id)

# use generative methods
stmt = select(table).where(table.c.id == 5)

# use generative methods
stmt = table.select().where(table.c.id == 5)

# pass columns clause expressions positionally
stmt = select(table.c.x, table.c.y)

# case conditions passed positionally
case_clause = case(
    (table.c.x == 5, "five"), (table.c.x == 7, "seven"), else_="neither five nor seven"
)

Discussion

SQLAlchemy has for many years developed a convention for SQL constructs accepting an argument either as a list or as positional arguments. This convention states that structural elements, those that form the structure of a SQL statement, should be passed positionally. Conversely, data elements, those that form the parameterized data of a SQL statement, should be passed as lists. For many years, the select() construct could not participate in this convention smoothly because of the very legacy calling pattern where the “WHERE” clause would be passed positionally. SQLAlchemy 2.0 finally resolves this by changing the select() construct to only accept the “generative” style that has for many years been the only documented style in the Core tutorial.

Examples of “structural” vs. “data” elements are as follows:

# table columns for CREATE TABLE - structural
table = Table("table", metadata_obj, Column("x", Integer), Column("y", Integer))

# columns in a SELECT statement - structural
stmt = select(table.c.x, table.c.y)

# literal elements in an IN clause - data
stmt = stmt.where(table.c.y.in_([1, 2, 3]))

See also

select(), case() now accept positional expressions

select() construct created in “legacy” mode; keyword arguments, etc.

insert/update/delete DML no longer accept keyword constructor arguments

Synopsis

In a similar way as to the previous change to select(), the constructor arguments to insert(), update() and delete() other than the table argument are essentially removed:

# no longer supported
stmt = insert(table, values={"x": 10, "y": 15}, inline=True)

# no longer supported
stmt = insert(table, values={"x": 10, "y": 15}, returning=[table.c.x])

# no longer supported
stmt = table.delete(table.c.x > 15)

# no longer supported
stmt = table.update(table.c.x < 15, preserve_parameter_order=True).values(
    [(table.c.y, 20), (table.c.x, table.c.y + 10)]
)

Migration to 2.0

The following examples illustrate generative method use for the above examples:

# use generative methods, **kwargs OK for values()
stmt = insert(table).values(x=10, y=15).inline()

# use generative methods, dictionary also still  OK for values()
stmt = insert(table).values({"x": 10, "y": 15}).returning(table.c.x)

# use generative methods
stmt = table.delete().where(table.c.x > 15)

# use generative methods, ordered_values() replaces preserve_parameter_order
stmt = (
    table.update()
    .where(
        table.c.x < 15,
    )
    .ordered_values((table.c.y, 20), (table.c.x, table.c.y + 10))
)

Discussion

The API and internals is being simplified for the DML constructs in a similar manner as that of the select() construct.

2.0 Migration - ORM Configuration

Declarative becomes a first class API

Synopsis

The sqlalchemy.ext.declarative package is mostly, with some exceptions, moved to the sqlalchemy.orm package. The declarative_base() and declared_attr() functions are present without any behavioral changes. A new super-implementation of declarative_base() known as registry now serves as the top-level ORM configurational construct, which also provides for decorator-based declarative and new support for classical mappings that integrate with the declarative registry.

Migration to 2.0

Change imports:

from sqlalchemy.ext import declarative_base, declared_attr

To:

from sqlalchemy.orm import declarative_base, declared_attr

Discussion

After ten years or so of popularity, the sqlalchemy.ext.declarative package is now integrated into the sqlalchemy.orm namespace, with the exception of the declarative “extension” classes which remain as Declarative extensions. The change is detailed further in the 1.4 migration guide at Declarative is now integrated into the ORM with new features.

See also

ORM Mapped Class Overview - all new unified documentation for Declarative, classical mapping, dataclasses, attrs, etc.

Declarative is now integrated into the ORM with new features

The original “mapper()” function now a core element of Declarative, renamed

Synopsis

The mapper() function moves behind the scenes to be invoked by higher level APIs. The new version of this function is the method registry.map_imperatively() taken from a registry object.

Migration to 2.0

Code that works with classical mappings should change imports and code from:

from sqlalchemy.orm import mapper


mapper(SomeClass, some_table, properties={"related": relationship(SomeRelatedClass)})

To work from a central registry object:

from sqlalchemy.orm import registry

mapper_reg = registry()

mapper_reg.map_imperatively(
    SomeClass, some_table, properties={"related": relationship(SomeRelatedClass)}
)

The above registry is also the source for declarative mappings, and classical mappings now have access to this registry including string-based configuration on relationship():

from sqlalchemy.orm import registry

mapper_reg = registry()

Base = mapper_reg.generate_base()


class SomeRelatedClass(Base):
    __tablename__ = "related"

    # ...


mapper_reg.map_imperatively(
    SomeClass,
    some_table,
    properties={
        "related": relationship(
            "SomeRelatedClass",
            primaryjoin="SomeRelatedClass.related_id == SomeClass.id",
        )
    },
)

Discussion

By popular demand, “classical mapping” is staying around, however the new form of it is based off of the registry object and is available as registry.map_imperatively().

In addition, the primary rationale used for “classical mapping” is that of keeping the Table setup distinct from the class. Declarative has always allowed this style using so-called hybrid declarative. However, to remove the base class requirement, a first class decorator form has been added.

As yet another separate but related enhancement, support for Python dataclasses is added as well to both declarative decorator and classical mapping forms.

See also

ORM Mapped Class Overview - all new unified documentation for Declarative, classical mapping, dataclasses, attrs, etc.

2.0 Migration - ORM Usage

The biggest visible change in SQLAlchemy 2.0 is the use of Session.execute() in conjunction with select() to run ORM queries, instead of using Session.query(). As mentioned elsewhere, there is no plan to actually remove the Session.query() API itself, as it is now implemented by using the new API internally it will remain as a legacy API, and both APIs can be used freely.

The table below provides an introduction to the general change in calling form with links to documentation for each technique presented. The individual migration notes are in the embedded sections following the table, and may include additional notes not summarized here.

Overview of Major ORM Querying Patterns

1.x style form

2.0 style form

See Also

 
session.query(User).get(42)
 
session.get(User, 42)

ORM Query - get() method moves to Session

 
session.query(User).all()
 
session.execute(
  select(User)
).scalars().all()

# or

session.scalars(
  select(User)
).all()

ORM Query Unified with Core Select

Session.scalars() Result.scalars()

 
session.query(User).\
  filter_by(name="some user").\
  one()
 
session.execute(
  select(User).
  filter_by(name="some user")
).scalar_one()

ORM Query Unified with Core Select

Result.scalar_one()

 
session.query(User).\
  filter_by(name="some user").\
  first()
 
session.scalars(
  select(User).
  filter_by(name="some user").
  limit(1)
).first()

ORM Query Unified with Core Select

Result.first()

 
session.query(User).options(
  joinedload(User.addresses)
).all()
 
session.scalars(
  select(User).
  options(
    joinedload(User.addresses)
  )
).unique().all()

ORM Rows not uniquified by default

 
session.query(User).\
  join(Address).\
  filter(
    Address.email == "e@sa.us"
  ).\
  all()
 
session.execute(
  select(User).
  join(Address).
  where(
    Address.email == "e@sa.us"
  )
).scalars().all()

ORM Query Unified with Core Select

Joins

 
session.query(User).\
  from_statement(
    text("select * from users")
  ).\
  all()
 
session.scalars(
  select(User).
  from_statement(
    text("select * from users")
  )
).all()

Getting ORM Results from Textual and Core Statements

 
session.query(User).\
  join(User.addresses).\
  options(
    contains_eager(User.addresses)
  ).\
  populate_existing().all()
 
session.execute(
  select(User)
  .join(User.addresses)
  .options(
    contains_eager(User.addresses)
  )
  .execution_options(
      populate_existing=True
  )
).scalars().all()

ORM Execution Options

Populate Existing

 
session.query(User).\
  filter(User.name == "foo").\
  update(
    {"fullname": "Foo Bar"},
    synchronize_session="evaluate"
  )
 
session.execute(
  update(User)
  .where(User.name == "foo")
  .values(fullname="Foo Bar")
  .execution_options(
    synchronize_session="evaluate"
  )
)

UPDATE and DELETE with arbitrary WHERE clause

 
session.query(User).count()
 
session.scalars(
  select(func.count()).
  select_from(User)
).one()
session.scalars(
  select(func.count(User.id))
).one()

Session.scalar()

ORM Query Unified with Core Select

Synopsis

The Query object (as well as the BakedQuery and ShardedQuery extensions) become long term legacy objects, replaced by the direct usage of the select() construct in conjunction with the Session.execute() method. Results that are returned from Query in the form of lists of objects or tuples, or as scalar ORM objects are returned from Session.execute() uniformly as Result objects, which feature an interface consistent with that of Core execution.

Legacy code examples are illustrated below:

session = Session(engine)

# becomes legacy use case
user = session.query(User).filter_by(name='some user').one()

# becomes legacy use case
user = session.query(User).filter_by(name='some user').first()

# becomes legacy use case
user = session.query(User).get(5)

# becomes legacy use case
for user in session.query(User).join(User.addresses).filter(Address.email == 'some@email.com'):
    # ...

# becomes legacy use case
users = session.query(User).options(joinedload(User.addresses)).order_by(User.id).all()

# becomes legacy use case
users = session.query(User).from_statement(
    text("select * from users")
).all()

# etc

Migration to 2.0

Because the vast majority of an ORM application is expected to make use of Query objects as well as that the Query interface being available does not impact the new interface, the object will stay around in 2.0 but will no longer be part of documentation nor will it be supported for the most part. The select() construct now suits both the Core and ORM use cases, which when invoked via the Session.execute() method will return ORM-oriented results, that is, ORM objects if that’s what was requested.

The Select() construct adds many new methods for compatibility with Query, including Select.filter() Select.filter_by(), newly reworked Select.join() and Select.outerjoin() methods, Select.options(), etc. Other more supplemental methods of Query such as Query.populate_existing() are implemented via execution options.

Return results are in terms of a Result object, the new version of the SQLAlchemy ResultProxy object, which also adds many new methods for compatibility with Query, including Result.one(), Result.all(), Result.first(), Result.one_or_none(), etc.

The Result object however does require some different calling patterns, in that when first returned it will always return tuples and it will not deduplicate results in memory. In order to return single ORM objects the way Query does, the Result.scalars() modifier must be called first. In order to return uniqued objects, as is necessary when using joined eager loading, the Result.unique() modifier must be called first.

Documentation for all new features of select() including execution options, etc. are at ORM Querying Guide.

Below are some examples of how to migrate to select():

session = Session(engine)

user = session.execute(
    select(User).filter_by(name="some user")
).scalar_one()

# for first(), no LIMIT is applied automatically; add limit(1) if LIMIT
# is desired on the query
user = session.execute(
    select(User).filter_by(name="some user").limit(1)
).scalars().first()

# get() moves to the Session directly
user = session.get(User, 5)

for user in session.execute(
    select(User).join(User.addresses).filter(Address.email == "some@email.case")
).scalars():
    # ...

# when using joinedload() against collections, use unique() on the result
users = session.execute(
    select(User).options(joinedload(User.addresses)).order_by(User.id)
).unique().all()

# select() has ORM-ish methods like from_statement() that only work
# if the statement is against ORM entities
users = session.execute(
    select(User).from_statement(text("select * from users"))
).scalars().all()

Discussion

The fact that SQLAlchemy has both a select() construct as well as a separate Query object that features an extremely similar, but fundamentally incompatible interface is likely the greatest inconsistency in SQLAlchemy, one that arose as a result of small incremental additions over time that added up to two major APIs that are divergent.

In SQLAlchemy’s first releases, the Query object didn’t exist at all. The original idea was that the Mapper construct itself would be able to select rows, and that Table objects, not classes, would be used to create the various criteria in a Core-style approach. The Query came along some months / years into SQLAlchemy’s history as a user proposal for a new, “buildable” querying object originally called SelectResults was accepted. Concepts like a .where() method, which SelectResults called .filter(), were not present in SQLAlchemy previously, and the select() construct used only the “all-at-once” construction style that’s now deprecated at select() no longer accepts varied constructor arguments, columns are passed positionally.

As the new approach took off, the object evolved into the Query object as new features such as being able to select individual columns, being able to select multiple entities at once, being able to build subqueries from a Query object rather than from a select object were added. The goal became that Query should have the full functionality of select in that it could be composed to build SELECT statements fully with no explicit use of select() needed. At the same time, select() had also evolved “generative” methods like Select.where() and Select.order_by().

In modern SQLAlchemy, this goal has been achieved and the two objects are now completely overlapping in functionality. The major challenge to unifying these objects was that the select() object needed to remain completely agnostic of the ORM. To achieve this, the vast majority of logic from Query has been moved into the SQL compile phase, where ORM-specific compiler plugins receive the Select construct and interpret its contents in terms of an ORM-style query, before passing off to the core-level compiler in order to create a SQL string. With the advent of the new SQL compilation caching system <change_4639>, the majority of this ORM logic is also cached.

See also

ORM Query is internally unified with select, update, delete; 2.0 style execution available

ORM Query - get() method moves to Session

Synopsis

The Query.get() method remains for legacy purposes, but the primary interface is now the Session.get() method:

# legacy usage
user_obj = session.query(User).get(5)

Migration to 2.0

In 1.4 / 2.0, the Session object adds a new Session.get() method:

# 1.4 / 2.0 cross-compatible use
user_obj = session.get(User, 5)

Discussion

The Query object is to be a legacy object in 2.0, as ORM queries are now available using the select() object. As the Query.get() method defines a special interaction with the Session and does not necessarily even emit a query, it’s more appropriate that it be part of Session, where it is similar to other “identity” methods such as refresh and merge.

SQLAlchemy originally included “get()” to resemble the Hibernate Session.load() method. As is so often the case, we got it slightly wrong as this method is really more about the Session than with writing a SQL query.

ORM Query - Joining / loading on relationships uses attributes, not strings

Synopsis

This refers to patterns such as that of Query.join() as well as query options like joinedload() which currently accept a mixture of string attribute names or actual class attributes. The string forms will all be removed in 2.0:

# string use removed
q = session.query(User).join("addresses")

# string use removed
q = session.query(User).options(joinedload("addresses"))

# string use removed
q = session.query(Address).filter(with_parent(u1, "addresses"))

Migration to 2.0

Modern SQLAlchemy 1.x versions support the recommended technique which is to use mapped attributes:

# compatible with all modern SQLAlchemy versions

q = session.query(User).join(User.addresses)

q = session.query(User).options(joinedload(User.addresses))

q = session.query(Address).filter(with_parent(u1, User.addresses))

The same techniques apply to 2.0-style style use:

# SQLAlchemy 1.4 / 2.0 cross compatible use

stmt = select(User).join(User.addresses)
result = session.execute(stmt)

stmt = select(User).options(joinedload(User.addresses))
result = session.execute(stmt)

stmt = select(Address).where(with_parent(u1, User.addresses))
result = session.execute(stmt)

Discussion

The string calling form is ambiguous and requires that the internals do extra work to determine the appropriate path and retrieve the correct mapped property. By passing the ORM mapped attribute directly, not only is the necessary information passed up front, the attribute is also typed and is more potentially compatible with IDEs and pep-484 integrations.

ORM Query - Chaining using lists of attributes, rather than individual calls, removed

Synopsis

“Chained” forms of joining and loader options which accept multiple mapped attributes in a list will be removed:

# chaining removed
q = session.query(User).join("orders", "items", "keywords")

Migration to 2.0

Use individual calls to Query.join() for 1.x /2.0 cross compatible use:

q = session.query(User).join(User.orders).join(Order.items).join(Item.keywords)

For 2.0-style use, Select has the same behavior of Select.join(), and also features a new Select.join_from() method that allows an explicit left side:

# 1.4 / 2.0 cross compatible

stmt = select(User).join(User.orders).join(Order.items).join(Item.keywords)
result = session.execute(stmt)

# join_from can also be helpful
stmt = select(User).join_from(User, Order).join_from(Order, Item, Order.items)
result = session.execute(stmt)

Discussion

Removing the chaining of attributes is in line with simplifying the calling interface of methods such as Select.join().

ORM Query - join(…, aliased=True), from_joinpoint removed

Synopsis

The aliased=True option on Query.join() is removed, as is the from_joinpoint flag:

# no longer supported
q = session.query(Node).\
  join("children", aliased=True).filter(Node.name == "some sub child").
  join("children", from_joinpoint=True, aliased=True).\
  filter(Node.name == 'some sub sub child')

Migration to 2.0

Use explicit aliases instead:

n1 = aliased(Node)
n2 = aliased(Node)

q = (
    select(Node)
    .join(Node.children.of_type(n1))
    .where(n1.name == "some sub child")
    .join(n1.children.of_type(n2))
    .where(n2.name == "some sub child")
)

Discussion

The aliased=True option on Query.join() is another feature that seems to be almost never used, based on extensive code searches to find actual use of this feature. The internal complexity that the aliased=True flag requires is enormous, and will be going away in 2.0.

Most users aren’t familiar with this flag, however it allows for automatic aliasing of elements along a join, which then applies automatic aliasing to filter conditions. The original use case was to assist in long chains of self-referential joins, as in the example shown above. However, the automatic adaption of the filter criteria is enormously complicated internally and almost never used in real world applications. The pattern also leads to issues such as if filter criteria need to be added at each link in the chain; the pattern then must use the from_joinpoint flag which SQLAlchemy developers could absolutely find no occurrence of this parameter ever being used in real world applications.

The aliased=True and from_joinpoint parameters were developed at a time when the Query object didn’t yet have good capabilities regarding joining along relationship attributes, functions like PropComparator.of_type() did not exist, and the aliased() construct itself didn’t exist early on.

Using DISTINCT with additional columns, but only select the entity

Synopsis

Query will automatically add columns in the ORDER BY when distinct is used. The following query will select from all User columns as well as “address.email_address” but only return User objects:

# 1.xx code

result = (
    session.query(User)
    .join(User.addresses)
    .distinct()
    .order_by(Address.email_address)
    .all()
)

In version 2.0, the “email_address” column will not be automatically added to the columns clause, and the above query will fail, since relational databases won’t allow you to ORDER BY “address.email_address” when using DISTINCT if it isn’t also in the columns clause.

Migration to 2.0

In 2.0, the column must be added explicitly. To resolve the issue of only returning the main entity object, and not the extra column, use the Result.columns() method:

# 1.4 / 2.0 code

stmt = (
    select(User, Address.email_address)
    .join(User.addresses)
    .distinct()
    .order_by(Address.email_address)
)

result = session.execute(stmt).columns(User).all()

Discussion

This case is an example of the limited flexibility of Query leading to the case where implicit, “magical” behavior needed to be added; the “email_address” column is implicitly added to the columns clause, then additional internal logic would omit that column from the actual results returned.

The new approach simplifies the interaction and makes what’s going on explicit, while still making it possible to fulfill the original use case without inconvenience.

Selecting from the query itself as a subquery, e.g. “from_self()”

Synopsis

The Query.from_self() method will be removed from Query:

# from_self is removed
q = (
    session.query(User, Address.email_address)
    .join(User.addresses)
    .from_self(User)
    .order_by(Address.email_address)
)

Migration to 2.0

The aliased() construct may be used to emit ORM queries against an entity that is in terms of any arbitrary selectable. It has been enhanced in version 1.4 to smoothly accommodate being used multiple times against the same subquery for different entities as well. This can be used in 1.x style with Query as below; note that since the final query wants to query in terms of both the User and Address entities, two separate aliased() constructs are created:

from sqlalchemy.orm import aliased

subq = session.query(User, Address.email_address).join(User.addresses).subquery()

ua = aliased(User, subq)

aa = aliased(Address, subq)

q = session.query(ua, aa).order_by(aa.email_address)

The same form may be used in 2.0 style:

from sqlalchemy.orm import aliased

subq = select(User, Address.email_address).join(User.addresses).subquery()

ua = aliased(User, subq)

aa = aliased(Address, subq)

stmt = select(ua, aa).order_by(aa.email_address)

result = session.execute(stmt)

Discussion

The Query.from_self() method is a very complicated method that is rarely used. The purpose of this method is to convert a Query into a subquery, then return a new Query which SELECTs from that subquery. The elaborate aspect of this method is that the returned query applies automatic translation of ORM entities and columns to be stated in the SELECT in terms of the subquery, as well as that it allows the entities and columns to be SELECTed from to be modified.

Because Query.from_self() packs an intense amount of implicit translation into the SQL it produces, while it does allow a certain kind of pattern to be executed very succinctly, real world use of this method is infrequent as it is not simple to understand.

The new approach makes use of the aliased() construct so that the ORM internals don’t need to guess which entities and columns should be adapted and in what way; in the example above, the ua and aa objects, both of which are AliasedClass instances, provide to the internals an unambiguous marker as to where the subquery should be referred towards as well as what entity column or relationship is being considered for a given component of the query.

SQLAlchemy 1.4 also features an improved labeling style that no longer requires the use of long labels that include the table name in order to disambiguate columns of same names from different tables. In the above examples, even if our User and Address entities have overlapping column names, we can select from both entities at once without having to specify any particular labeling:

# 1.4 / 2.0 code

subq = select(User, Address).join(User.addresses).subquery()

ua = aliased(User, subq)
aa = aliased(Address, subq)

stmt = select(ua, aa).order_by(aa.email_address)
result = session.execute(stmt)

The above query will disambiguate the .id column of User and Address, where Address.id is rendered and tracked as id_1:

SELECT anon_1.id AS anon_1_id, anon_1.id_1 AS anon_1_id_1,
       anon_1.user_id AS anon_1_user_id,
       anon_1.email_address AS anon_1_email_address
FROM (
  SELECT "user".id AS id, address.id AS id_1,
  address.user_id AS user_id, address.email_address AS email_address
  FROM "user" JOIN address ON "user".id = address.user_id
) AS anon_1 ORDER BY anon_1.email_address

#5221

Selecting entities from alternative selectables; Query.select_entity_from()

Synopsis

The Query.select_entity_from() method will be removed in 2.0:

subquery = session.query(User).filter(User.id == 5).subquery()

user = session.query(User).select_entity_from(subquery).first()

Migration to 2.0

As is the case described at Selecting from the query itself as a subquery, e.g. “from_self()”, the aliased() object provides a single place that operations like “select entity from a subquery” may be achieved. Using 1.x style:

from sqlalchemy.orm import aliased

subquery = session.query(User).filter(User.name.like("%somename%")).subquery()

ua = aliased(User, subquery)

user = session.query(ua).order_by(ua.id).first()

Using 2.0 style:

from sqlalchemy.orm import aliased

subquery = select(User).where(User.name.like("%somename%")).subquery()

ua = aliased(User, subquery)

# note that LIMIT 1 is not automatically supplied, if needed
user = session.execute(select(ua).order_by(ua.id).limit(1)).scalars().first()

Discussion

The points here are basically the same as those discussed at Selecting from the query itself as a subquery, e.g. “from_self()”. The Query.select_from_entity() method was another way to instruct the query to load rows for a particular ORM mapped entity from an alternate selectable, which involved having the ORM apply automatic aliasing to that entity wherever it was used in the query later on, such as in the WHERE clause or ORDER BY. This intensely complex feature is seldom used in this way, where as was the case with Query.from_self(), it’s much easier to follow what’s going on when using an explicit aliased() object, both from a user point of view as well as how the internals of the SQLAlchemy ORM must handle it.

ORM Rows not uniquified by default

Synopsis

ORM rows returned by session.execute(stmt) are no longer automatically “uniqued”. This will normally be a welcome change, except in the case where the “joined eager loading” loader strategy is used with collections:

# In the legacy API, many rows each have the same User primary key, but
# only one User per primary key is returned
users = session.query(User).options(joinedload(User.addresses))

# In the new API, uniquing is available but not implicitly
# enabled
result = session.execute(select(User).options(joinedload(User.addresses)))

# this actually will raise an error to let the user know that
# uniquing should be applied
rows = result.all()

Migrating to 2.0

When using a joined load of a collection, it’s required that the Result.unique() method is called. The ORM will actually set a default row handler that will raise an error if this is not done, to ensure that a joined eager load collection does not return duplicate rows while still maintaining explicitness:

# 1.4 / 2.0 code

stmt = select(User).options(joinedload(User.addresses))

# statement will raise if unique() is not used, due to joinedload()
# of a collection.  in all other cases, unique() is not needed.
# By stating unique() explicitly, confusion over discrepancies between
# number of objects/ rows returned vs. "SELECT COUNT(*)" is resolved
rows = session.execute(stmt).unique().all()

Discussion

The situation here is a little bit unusual, in that SQLAlchemy is requiring that a method be invoked that it is in fact entirely capable of doing automatically. The reason for requiring that the method be called is to ensure the developer is “opting in” to the use of the Result.unique() method, such that they will not be confused when a straight count of rows does not conflict with the count of records in the actual result set, which has been a long running source of user confusion and bug reports for many years. That the uniquifying is not happening in any other case by default will improve performance and also improve clarity in those cases where automatic uniquing was causing confusing results.

To the degree that having to call Result.unique() when joined eager load collections are used is inconvenient, in modern SQLAlchemy the selectinload() strategy presents a collection-oriented eager loader that is superior in most respects to joinedload() and should be preferred.

Making use of “dynamic” relationship loads without using Query

Synopsis

The lazy="dynamic" relationship loader strategy, discussed at Dynamic Relationship Loaders, makes use of the Query object which is legacy in 2.0.

Migration to 2.0

This pattern is still under adjustment for SQLAlchemy 2.0, and it is expected that new APIs will be introduced. In the interim, there are two ways to achieve 2.0 style querying that’s in terms of a specific relationship:

  • Make use of the Query.statement attribute on an existing lazy="dynamic" relationship. We can use methods like Session.scalars() with the dynamic loader straight away as follows:

    class User(Base):
    __tablename__ = "user"

    posts = relationship(Post, lazy="dynamic")


jack = session.get(User, 5)

# filter Jack's blog posts
posts = session.scalars(jack.posts.statement.where(Post.headline == "this is a post"))

  • Use the with_parent() function to construct a select() construct directly:

    from sqlalchemy.orm import with_parent

jack = session.get(User, 5)

posts = session.scalars(
    select(Post)
    .where(with_parent(jack, User.posts))
    .where(Post.headline == "this is a post")
)

Discussion

The original idea was that the with_parent() function should be sufficient, however continuing to make use of special attributes on the relationship itself remains appealing, and there’s no reason a 2.0 style construct can’t be made to work here as well. There will likely be a new loader strategy name that sets up an API similar to the example above that uses the .statement attribute, such as jack.posts.select().where(Post.headline == 'headline').

Autocommit mode removed from Session; autobegin support added

Synopsis

The Session will no longer support “autocommit” mode, that is, this pattern:

from sqlalchemy.orm import Session

sess = Session(engine, autocommit=True)

# no transaction begun, but emits SQL, won't be supported
obj = sess.query(Class).first()


# session flushes in a transaction that it begins and
# commits, won't be supported
sess.flush()

Migration to 2.0

The main reason a Session is used in “autocommit” mode is so that the Session.begin() method is available, so that framework integrations and event hooks can control when this event happens. In 1.4, the Session now features autobegin behavior which resolves this issue; the Session.begin() method may now be called:

from sqlalchemy.orm import Session

sess = Session(engine)

sess.begin()  # begin explicitly; if not called, will autobegin
# when database access is needed

sess.add(obj)

sess.commit()

Discussion

The “autocommit” mode is another holdover from the first versions of SQLAlchemy. The flag has stayed around mostly in support of allowing explicit use of Session.begin(), which is now solved by 1.4, as well as to allow the use of “subtransactions”, which are also removed in 2.0.

Session “subtransaction” behavior removed

Synopsis

The “subtransaction” pattern that was often used with autocommit mode is also deprecated in 1.4. This pattern allowed the use of the Session.begin() method when a transaction were already begun, resulting in a construct called a “subtransaction”, which was essentially a block that would prevent the Session.commit() method from actually committing.

Migration to 2.0

To provide backwards compatibility for applications that make use of this pattern, the following context manager or a similar implementation based on a decorator may be used:

import contextlib


@contextlib.contextmanager
def transaction(session):
    if not session.in_transaction():
        with session.begin():
            yield
    else:
        yield

The above context manager may be used in the same way the “subtransaction” flag works, such as in the following example:

# method_a starts a transaction and calls method_b
def method_a(session):
    with transaction(session):
        method_b(session)


# method_b also starts a transaction, but when
# called from method_a participates in the ongoing
# transaction.
def method_b(session):
    with transaction(session):
        session.add(SomeObject("bat", "lala"))


Session = sessionmaker(engine)

# create a Session and call method_a
with Session() as session:
    method_a(session)

To compare towards the preferred idiomatic pattern, the begin block should be at the outermost level. This removes the need for individual functions or methods to be concerned with the details of transaction demarcation:

def method_a(session):
    method_b(session)


def method_b(session):
    session.add(SomeObject("bat", "lala"))


Session = sessionmaker(engine)

# create a Session and call method_a
with Session() as session:
    with session.begin():
        method_a(session)

Discussion

This pattern has been shown to be confusing in real world applications, and it is preferable for an application to ensure that the top-most level of database operations are performed with a single begin/commit pair.

2.0 Migration - ORM Extension and Recipe Changes

Dogpile cache recipe and Horizontal Sharding uses new Session API

As the Query object becomes legacy, these two recipes which previously relied upon subclassing of the Query object now make use of the SessionEvents.do_orm_execute() hook. See the section Re-Executing Statements for an example.

Baked Query Extension Superseded by built-in caching

The baked query extension is superseded by the built in caching system and is no longer used by the ORM internals.

See SQL Compilation Caching for full background on the new caching system.

Asyncio Support

SQLAlchemy 1.4 includes asyncio support for both Core and ORM. The new API exclusively makes use of the “future” patterns noted above. See Asynchronous IO Support for Core and ORM for background.