Error Messages ¶

e.execute(
    t.insert(),
        {"a": 1, "b": 2, "c": 3},
        {"a": 2, "b": None, "c": 4},
        {"a": 3, "b": 4, "c": 5},
See also
Sending Parameters
Expected FROM clause, got Select.  To create a FROM clause, use the .subquery() method¶
This refers to a change made as of SQLAlchemy 1.4 where a SELECT statement as generated
by a function such as select(), but also including things like unions and textual
SELECT expressions are no longer considered to be FromClause objects and
can’t be placed directly in the FROM clause of another SELECT statement without them
being wrapped in a Subquery first.   This is a major conceptual change in the
Core and the full rationale is discussed at A SELECT statement is no longer implicitly considered to be a FROM clause.
Given an example as:
m = MetaData()
t = Table("t", m, Column("a", Integer), Column("b", Integer), Column("c", Integer))
stmt = select(t)
Above, stmt represents a SELECT statement.  The error is produced when we want
to use stmt directly as a FROM clause in another SELECT, such as if we
attempted to select from it:
new_stmt_1 = select(stmt)
Or if we wanted to use it in a FROM clause such as in a JOIN:
new_stmt_2 = select(some_table).select_from(some_table.join(stmt))
In previous versions of SQLAlchemy, using a SELECT inside of another SELECT
would produce a parenthesized, unnamed subquery.   In most cases, this form of
SQL is not very useful as databases like MySQL and PostgreSQL require that
subqueries in FROM clauses have named aliases, which means using the
SelectBase.alias() method or as of 1.4 using the
SelectBase.subquery() method to produce this.   On other databases, it
is still much clearer for the subquery to have a name to resolve any ambiguity
on future references to column  names inside the subquery.
Beyond the above practical reasons, there are a lot of other SQLAlchemy-oriented
reasons the change is being made.  The correct form of the above two statements
therefore requires that SelectBase.subquery() is used:
subq = stmt.subquery()
new_stmt_1 = select(subq)
new_stmt_2 = select(some_table).select_from(some_table.join(subq))
See also
A SELECT statement is no longer implicitly considered to be a FROM clause
An alias is being generated automatically for raw clauseelement¶
New in version 1.4.26.
This deprecation warning refers to a very old and likely not well known pattern
that applies to the legacy Query.join() method as well as the
2.0 style Select.join() method, where a join can be stated
in terms of a relationship() but the target is the
Table or other Core selectable to which the class is mapped,
rather than an ORM entity such as a mapped class or aliased()
construct:
a1 = Address.__table__
q = (
    s.query(User)
    .join(a1, User.addresses)
    .filter(Address.email_address == "[email protected]")
    .all()
The above pattern also allows an arbitrary selectable, such as
a Core Join or Alias object,
however there is no automatic adaptation of this element, meaning the
Core element would need to be referred towards directly:
a1 = Address.__table__.alias()
q = (
    s.query(User)
    .join(a1, User.addresses)
    .filter(a1.c.email_address == "[email protected]")
    .all()
The correct way to specify a join target is always by using the mapped
class itself or an aliased object, in the latter case using the
PropComparator.of_type() modifier to set up an alias:
# normal join to relationship entity
q = s.query(User).join(User.addresses).filter(Address.email_address == "[email protected]")
# name Address target explicitly, not necessary but legal
q = (
    s.query(User)
    .join(Address, User.addresses)
    .filter(Address.email_address == "[email protected]")
Join to an alias:
from sqlalchemy.orm import aliased
a1 = aliased(Address)
# of_type() form; recommended
q = (
    s.query(User)
    .join(User.addresses.of_type(a1))
    .filter(a1.email_address == "[email protected]")
# target, onclause form
q = s.query(User).join(a1, User.addresses).filter(a1.email_address == "[email protected]")




    

An alias is being generated automatically due to overlapping tables¶
New in version 1.4.26.
This warning is typically generated when querying using the
Select.join() method or the legacy Query.join() method
with mappings that involve joined table inheritance. The issue is that when
joining between two joined inheritance models that share a common base table, a
proper SQL JOIN between the two entities cannot be formed without applying an
alias to one side or the other; SQLAlchemy applies an alias to the right side
of the join. For example given a joined inheritance mapping as:
class Employee(Base):
    __tablename__ = "employee"
    id = Column(Integer, primary_key=True)
    manager_id = Column(ForeignKey("manager.id"))
    name = Column(String(50))
    type = Column(String(50))
    reports_to = relationship("Manager", foreign_keys=manager_id)
    __mapper_args__ = {
        "polymorphic_identity": "employee",
        "polymorphic_on": type,
class Manager(Employee):
    __tablename__ = "manager"
    id = Column(Integer, ForeignKey("employee.id"), primary_key=True)
    __mapper_args__ = {
        "polymorphic_identity": "manager",
        "inherit_condition": id == Employee.id,
The above mapping includes a relationship between the Employee and
Manager classes.  Since both classes make use of the “employee” database
table, from a SQL perspective this is a
self referential relationship.  If we wanted to
query from both the Employee and Manager models using a join, at the
SQL level the “employee” table needs to be included twice in the query, which
means it must be aliased.   When we create such a join using the SQLAlchemy
ORM, we get SQL that looks like the following:
>>> stmt = select(Employee, Manager).join(Employee.reports_to)
>>> print(stmt)
SELECT employee.id, employee.manager_id, employee.name,
employee.type, manager_1.id AS id_1, employee_1.id AS id_2,
employee_1.manager_id AS manager_id_1, employee_1.name AS name_1,
employee_1.type AS type_1
FROM employee JOIN
(employee AS employee_1 JOIN manager AS manager_1 ON manager_1.id = employee_1.id)
ON manager_1.id = employee.manager_id
Above, the SQL selects FROM the employee table, representing the
Employee entity in the query. It then joins to a right-nested join of
employee AS employee_1 JOIN manager AS manager_1, where the employee
table is stated again, except as an anonymous alias employee_1. This is the
“automatic generation of an alias” that the warning message refers towards.
When SQLAlchemy loads ORM rows that each contain an Employee and a
Manager object, the ORM must adapt rows from what above is the
employee_1 and manager_1 table aliases into those of the un-aliased
Manager class. This process is internally complex and does not accommodate
for all API features, notably when trying to use eager loading features such as
contains_eager() with more deeply nested queries than are shown
here.  As the pattern is unreliable for more complex scenarios and involves
implicit decisionmaking that is difficult to anticipate and follow,
the warning is emitted and this pattern may be considered a legacy feature. The
better way to write this query is to use the same patterns that apply to any
other self-referential relationship, which is to use the aliased()
construct explicitly.  For joined-inheritance and other join-oriented mappings,
it is usually desirable to add the use of the aliased.flat
parameter, which will allow a JOIN of two or more tables to be aliased by
applying an alias to the individual tables within the join, rather than
embedding the join into a new subquery:
>>> from sqlalchemy.orm import aliased
>>> manager_alias = aliased(Manager, flat=True)
>>> stmt = select(Employee, manager_alias).join(Employee.reports_to.of_type(manager_alias))
>>> print(stmt)
SELECT employee.id, employee.manager_id, employee.name,
employee.type, manager_1.id AS id_1, employee_1.id AS id_2,
employee_1.manager_id AS manager_id_1, employee_1.name AS name_1,
employee_1.type AS type_1
FROM employee JOIN
(employee AS employee_1 JOIN manager AS manager_1 ON manager_1.id = employee_1.id)
ON manager_1.id = employee.manager_id
If we then wanted to use contains_eager() to populate the
reports_to attribute, we refer to the alias:
>>> stmt = (
...     select(Employee)
...     .join(Employee.reports_to.of_type(manager_alias))
...     .options(contains_eager(Employee.reports_to.of_type(manager_alias)))
... )




    

Without using the explicit aliased() object, in some more nested
cases the contains_eager() option does not have enough context to
know where to get its data from, in the case that the ORM is “auto-aliasing”
in a very nested context.  Therefore it’s best not to rely on this feature
and instead keep the SQL construction as explicit as possible.
Object Relational Mapping¶
Parent instance <x> is not bound to a Session; (lazy load/deferred load/refresh/etc.) operation cannot proceed¶
This is likely the most common error message when dealing with the ORM, and it
occurs as a result of the nature of a technique the ORM makes wide use of known
as lazy loading.   Lazy loading is a common object-relational pattern
whereby an object that’s persisted by the ORM maintains a proxy to the database
itself, such that when various attributes upon the object are accessed, their
value may be retrieved from the database lazily.   The advantage to this
approach is that objects can be retrieved from the database without having
to load all of their attributes or related data at once, and instead only that
data which is requested can be delivered at that time.   The major disadvantage
is basically a mirror image of the advantage, which is that if lots of objects
are being loaded which are known to require a certain set of data in all cases,
it is wasteful to load that additional data piecemeal.
Another caveat of lazy loading beyond the usual efficiency concerns is that
in order for lazy loading to proceed, the object has to remain associated
with a Session in order to be able to retrieve its state.  This error message
means that an object has become de-associated with its Session and
is being asked to lazy load data from the database.
The most common reason that objects become detached from their Session
is that the session itself was closed, typically via the Session.close()
method.   The objects will then live on to be accessed further, very often
within web applications where they are delivered to a server-side templating
engine and are asked for further attributes which they cannot load.
Mitigation of this error is via these techniques:
Try not to have detached objects; don’t close the session prematurely - Often, applications will close
out a transaction before passing off related objects to some other system
which then fails due to this error.   Sometimes the transaction doesn’t need
to be closed so soon; an example is the web application closes out
the transaction before the view is rendered.  This is often done in the name
of “correctness”, but may be seen as a mis-application of “encapsulation”,
as this term refers to code organization, not actual actions. The template that
uses an ORM object is making use of the proxy pattern
which keeps database logic encapsulated from the caller.   If the
Session can be held open until the lifespan of the objects are done,
this is the best approach.
Otherwise, load everything that’s needed up front - It is very often impossible to
keep the transaction open, especially in more complex applications that need
to pass objects off to other systems that can’t run in the same context
even though they’re in the same process.  In this case, the application
should prepare to deal with detached objects,
and should try to make appropriate use of eager loading to ensure
that objects have what they need up front.
And importantly, set expire_on_commit to False - When using detached objects, the
most common reason objects need to re-load data is because they were expired
from the last call to Session.commit().   This expiration should
not be used when dealing with detached objects; so the
Session.expire_on_commit parameter be set to False.
By preventing the objects from becoming expired outside of the transaction,
the data which was loaded will remain present and will not incur additional
lazy loads when that data is accessed.
Note also that Session.rollback() method unconditionally expires
all contents in the Session and should also be avoided in
non-error scenarios.
See also
Relationship Loading Techniques - detailed documentation on eager loading and other
relationship-oriented loading techniques
Committing - background on session commit
Refreshing / Expiring - background on attribute expiry
This Session’s transaction has been rolled back due to a previous exception during flush¶
The flush process of the Session, described at
Flushing, will roll back the database transaction if an error is
encountered, in order to maintain internal consistency.  However, once this
occurs, the session’s transaction is now “inactive” and must be explicitly
rolled back by the calling application, in the same way that it would otherwise
need to be explicitly committed if a failure had not occurred.
This is a common error when using the ORM and typically applies to an
application that doesn’t yet have correct “framing” around its
Session operations. Further detail is described in the FAQ at
“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar).
For relationship <relationship>, delete-orphan cascade is normally configured only on the “one” side of a one-to-many relationship, and not on the “many” side of a many-to-one or many-to-many relationship.¶
This error arises when the “delete-orphan” cascade
is set on a many-to-one or many-to-many relationship, such as:
class A(Base):
    __tablename__ = "a"
    id = Column(Integer, primary_key=True)
    bs = relationship("B", back_populates="a")
class B(Base):
    __tablename__ = "b"
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey("a.id"))
    # this will emit the error message when the mapper
    # configuration step occurs
    a = relationship("A", back_populates="bs", cascade="all, delete-orphan")
configure_mappers()
Above, the “delete-orphan” setting on B.a indicates the intent that
when every B object that refers to a particular A is deleted, that the
A should then be deleted as well.   That is, it expresses that the “orphan”
which is being deleted would be an A object, and it becomes an “orphan”
when every B that refers to it is deleted.
The “delete-orphan” cascade model does not support this functionality.   The
“orphan” consideration is only made in terms of the deletion of a single object
which would then refer to zero or more objects that are now “orphaned” by
this single deletion, which would result in those objects being deleted as
well.  In other words, it is designed only to track the creation of “orphans”
based on the removal of one and only one “parent” object per orphan,  which is
the natural case in a one-to-many relationship where a deletion of the
object on the “one” side results in the subsequent deletion of the related
items on the “many” side.
The above mapping in support of this functionality would instead place the
cascade setting on the one-to-many side, which looks like:
class A(Base):
    __tablename__ = "a"
    id = Column(Integer, primary_key=True)
    bs = relationship("B", back_populates="a", cascade="all, delete-orphan")
class B(Base):
    __tablename__ = "b"
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey("a.id"))
    a = relationship("A", back_populates="bs")
Where the intent is expressed that when an A is deleted, all of the
B objects to which it refers are also deleted.
The error message then goes on to suggest the usage of the
relationship.single_parent flag.    This flag may be used
to enforce that a relationship which is capable of having many objects
refer to a particular object will in fact have only one object referring
to it at a time.   It is used for legacy or other less ideal
database schemas where the foreign key relationships suggest a “many”
collection, however in practice only one object would actually refer
to a given target object at at time.  This uncommon scenario
can be demonstrated in terms of the above example as follows:
class A(Base):
    __tablename__ = "a"
    id = Column(Integer, primary_key=True)
    bs = relationship("B", back_populates="a")
class B(Base):
    __tablename__ = "b"
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey("a.id"))
    a = relationship(
        "A",
        back_populates="bs",
        single_parent=True,
        cascade="all, delete-orphan",
The above configuration will then install a validator which will enforce
that only one B may be associated with an A at at time, within
the scope of the B.a relationship:
>>> b1 = B()
>>> b2 = B()
>>> a1 = A()
>>> b1.a = a1
>>> b2.a = a1
sqlalchemy.exc.InvalidRequestError: Instance <A at 0x7eff44359350> is
already associated with an instance of <class '__main__.B'> via its
B.a attribute, and is only allowed a single parent.




    

Note that this validator is of limited scope and will not prevent multiple
“parents” from being created via the other direction.  For example, it will
not detect the same setting in terms of A.bs:
>>> a1.bs = [b1, b2]
>>> session.add_all([a1, b1, b2])
>>> session.commit()
INSERT INTO a DEFAULT VALUES
INSERT INTO b (a_id) VALUES (?)
(1,)
INSERT INTO b (a_id) VALUES (?)
(1,)
However, things will not go as expected later on, as the “delete-orphan” cascade
will continue to work in terms of a single lead object, meaning if we
delete either of the B objects, the A is deleted.   The other B stays
around, where the ORM will usually be smart enough to set the foreign key attribute
to NULL, but this is usually not what’s desired:
>>> session.delete(b1)
>>> session.commit()
UPDATE b SET a_id=? WHERE b.id = ?
(None, 2)
DELETE FROM b WHERE b.id = ?
(1,)
DELETE FROM a WHERE a.id = ?
(1,)
COMMIT
For all the above examples, similar logic applies to the calculus of a
many-to-many relationship; if a many-to-many relationship sets single_parent=True
on one side, that side can use the “delete-orphan” cascade, however this is
very unlikely to be what someone actually wants as the point of a many-to-many
relationship is so that there can be many objects referring to an object
in either direction.
Overall, “delete-orphan” cascade is usually applied
on the “one” side of a one-to-many relationship so that it deletes objects
in the “many” side, and not the other way around.
Changed in version 1.3.18: The text of the “delete-orphan” error message
when used on a many-to-one or many-to-many relationship has been updated
to be more descriptive.
See also
Cascades
delete-orphan
Instance <instance> is already associated with an instance of <instance> via its <attribute> attribute, and is only allowed a single parent.
Instance <instance> is already associated with an instance of <instance> via its <attribute> attribute, and is only allowed a single parent.¶
This error is emitted when the relationship.single_parent flag
is used, and more than one object is assigned as the “parent” of an object at
once.
Given the following mapping:
class A(Base):
    __tablename__ = "a"
    id = Column(Integer, primary_key=True)
class B(Base):
    __tablename__ = "b"
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey("a.id"))
    a = relationship(
        "A",
        single_parent=True,
        cascade="all, delete-orphan",
The intent indicates that no more than a single B object may refer
to a particular A object at once:
>>> b1 = B()
>>> b2 = B()
>>> a1 = A()
>>> b1.a = a1
>>> b2.a = a1
sqlalchemy.exc.InvalidRequestError: Instance <A at 0x7eff44359350> is
already associated with an instance of <class '__main__.B'> via its
B.a attribute, and is only allowed a single parent.
When this error occurs unexpectedly, it is usually because the
relationship.single_parent flag was applied in response
to the error message described at For relationship <relationship>, delete-orphan cascade is normally configured only on the “one” side of a one-to-many relationship, and not on the “many” side of a many-to-one or many-to-many relationship., and the issue is in
fact a misunderstanding of the “delete-orphan” cascade setting.  See that
message for details.
See also
For relationship <relationship>, delete-orphan cascade is normally configured only on the “one” side of a one-to-many relationship, and not on the “many” side of a many-to-one or many-to-many relationship.
relationship X will copy column Q to column P, which conflicts with relationship(s): ‘Y’¶
This warning refers to the case when two or more relationships will write data
to the same columns on flush, but the ORM does not have any means of
coordinating these relationships together. Depending on specifics, the solution
may be that two relationships need to be referred towards one another using
relationship.back_populates, or that one or more of the
relationships should be configured with relationship.viewonly
to prevent conflicting writes, or sometimes that the configuration is fully
intentional and should configure relationship.overlaps to
silence each warning.
For the typical example that’s missing
relationship.back_populates, given the following mapping:
class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    children = relationship("Child")
class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey("parent.id"))
    parent = relationship("Parent")




    

The above mapping will generate warnings:
SAWarning: relationship 'Child.parent' will copy column parent.id to column child.parent_id,
which conflicts with relationship(s): 'Parent.children' (copies parent.id to child.parent_id).
The relationships Child.parent and Parent.children appear to be in conflict.
The solution is to apply relationship.back_populates:
class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    children = relationship("Child", back_populates="parent")
class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey("parent.id"))
    parent = relationship("Parent", back_populates="children")
For more customized relationships where an “overlap” situation may be
intentional and cannot be resolved, the relationship.overlaps
parameter may specify the names of relationships for which the warning should
not take effect. This typically occurs for two or more relationships to the
same underlying table that include custom
relationship.primaryjoin conditions that limit the related
items in each case:
class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    c1 = relationship(
        "Child",
        primaryjoin="and_(Parent.id == Child.parent_id, Child.flag == 0)",
        backref="parent",
        overlaps="c2, parent",
    c2 = relationship(
        "Child",
        primaryjoin="and_(Parent.id == Child.parent_id, Child.flag == 1)",
        overlaps="c1, parent",
class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey("parent.id"))
    flag = Column(Integer)
Above, the ORM will know that the overlap between Parent.c1,
Parent.c2 and Child.parent is intentional.
Object cannot be converted to ‘persistent’ state, as this identity map is no longer valid.¶
New in version 1.4.26.
This message was added to accommodate for the case where a
Result object that would yield ORM objects is iterated after
the originating Session has been closed, or otherwise had its
Session.expunge_all() method called. When a Session
expunges all objects at once, the internal identity map used by that
Session is replaced with a new one, and the original one
discarded. An unconsumed and unbuffered Result object will
internally maintain a reference to that now-discarded identity map. Therefore,
when the Result is consumed, the objects that would be yielded
cannot be associated with that Session. This arrangement is by
design as it is generally not recommended to iterate an unbuffered
Result object outside of the transactional context in which it
was created:
# context manager creates new Session
with Session(engine) as session_obj:
    result = sess.execute(select(User).where(User.id == 7))
# context manager is closed, so session_obj above is closed, identity
# map is replaced
# iterating the result object can't associate the object with the
# Session, raises this error.
user = result.first()
The above situation typically will not occur when using the asyncio
ORM extension, as when AsyncSession returns a sync-style
Result, the results have been pre-buffered when the statement
was executed.  This is to allow secondary eager loaders to invoke without needing
an additional await call.
To pre-buffer results in the above situation using the regular
Session in the same way that the asyncio extension does it,
the prebuffer_rows execution option may be used as follows:
# context manager creates new Session
with Session(engine) as session_obj:
    # result internally pre-fetches all objects
    result = sess.execute(
        select(User).where(User.id == 7), execution_options={"prebuffer_rows": True}
# context manager is closed, so session_obj above is closed, identity
# map is replaced
# pre-buffered objects are returned
user = result.first()
# however they are detached from the session, which has been closed
assert inspect(user).detached
assert inspect(user).session is None
Above, the selected ORM objects are fully generated within the session_obj
block, associated with session_obj and buffered within the
Result object for iteration. Outside the block,
session_obj is closed and expunges these ORM objects. Iterating the
Result object will yield those ORM objects, however as their
originating Session has expunged them, they will be delivered in
the detached state.
The above reference to a “pre-buffered” vs. “un-buffered”
Result object refers to the process by which the ORM
converts incoming raw database rows from the DBAPI into ORM
objects.  It does not imply whether or not the underyling cursor
object itself, which represents pending results from the DBAPI, is itself
buffered or unbuffered, as this is essentially a lower layer of buffering.
For background on buffering of the cursor results itself, see the
section Using Server Side Cursors (a.k.a. stream results).
AwaitRequired¶
The SQLAlchemy async mode requires an async driver to be used to connect to the db.
This error is usually raised when trying to use the async version of SQLAlchemy
with a non compatible DBAPI.
See also
Asynchronous I/O (asyncio)
MissingGreenlet¶
A call to the async DBAPI was initiated outside the greenlet spawn
context usually setup by the SQLAlchemy AsyncIO proxy classes. Usually this
error happens when an IO was attempted in an unexpected place, without using
the provided async api. When using the ORM this may be due to a lazy loading
attempt, which is unsupported when using SQLAlchemy with AsyncIO dialects.
See also
Preventing Implicit IO when Using AsyncSession - covers most ORM scenarios where
this problem can occur and how to mitigate.
No Inspection Available¶
Using the inspect() function directly on an
AsyncConnection or AsyncEngine object is
not currently supported, as there is not yet an awaitable form of the
Inspector object available. Instead, the object
is used by acquiring it using the
inspect() function in such a way that it refers to the underlying
AsyncConnection.sync_connection attribute of the
AsyncConnection object; the Inspector is
then used in a “synchronous” calling style by using the
AsyncConnection.run_sync() method along with a custom function
that performs the desired operations:
async def async_main():
    async with engine.connect() as conn:
        tables = await conn.run_sync(
            lambda sync_conn: inspect(sync_conn).get_table_names()
See also
Using the Inspector to inspect schema objects - additional examples of using inspect()
with the asyncio extension.
Legacy Exceptions¶
Exceptions in this section are not generated by current SQLAlchemy
versions, however are provided here to suit exception message hyperlinks.
The <some function> in SQLAlchemy 2.0 will no longer <something>¶
SQLAlchemy 2.0 represents a major shift for a wide variety of key
SQLAlchemy usage patterns in both the Core and ORM components.   The goal
of the 2.0 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.
Introduced at Migrating to SQLAlchemy 2.0, the SQLAlchemy 2.0 project includes
a comprehensive future compatibility system that’s integrated into the
1.4 series of SQLAlchemy, such that applications will have a clear,
unambiguous, and incremental upgrade path in order to migrate applications to
being fully 2.0 compatible.   The RemovedIn20Warning deprecation
warning is at the base of this system to provide guidance on what behaviors in
an existing codebase will need to be modified.  An overview of how to enable
this warning is at SQLAlchemy 2.0 Deprecations Mode.
See also
Migrating to SQLAlchemy 2.0  - An overview of the upgrade process from
the 1.x series, as well as the current goals and progress of SQLAlchemy
SQLAlchemy 2.0 Deprecations Mode - specific guidelines on how to use
“2.0 deprecations mode” in SQLAlchemy 1.4.
Object is being merged into a Session along the backref cascade¶
This message refers to the “backref cascade” behavior of SQLAlchemy,
removed in version 2.0.  This refers to the action of
an object being added into a Session as a result of another
object that’s already present in that session being associated with it.
As this behavior has been shown to be more confusing than helpful,
the relationship.cascade_backrefs and
backref.cascade_backrefs parameters were added, which can
be set to False to disable it, and in SQLAlchemy 2.0 the “cascade backrefs”
behavior has been removed entirely.
For older SQLAlchemy versions, to set
relationship.cascade_backrefs to False on a backref that
is currently configured using the relationship.backref string
parameter, the backref must be declared using the backref() function
first so that the backref.cascade_backrefs parameter may be
passed.
Alternatively, the entire “cascade backrefs” behavior can be turned off
across the board by using the Session in “future” mode,
by passing True for the Session.future parameter.
See also
cascade_backrefs behavior deprecated for removal in 2.0 - background on the change for SQLAlchemy 2.0.
select() construct created in “legacy” mode; keyword arguments, etc.¶
The select() construct has been updated as of SQLAlchemy
1.4 to support the newer calling style that is standard in
SQLAlchemy 2.0.   For backwards compatibility within
the 1.4 series, the construct accepts arguments in both the “legacy” style as well
as the “new” style.
The “new” style features that column and table expressions are passed
positionally to the select() construct only; any other
modifiers to the object must be passed using subsequent method chaining:
# this is the way to do it going forward
stmt = select(table1.c.myid).where(table1.c.myid == table2.c.otherid)




    

For comparison, a select() in legacy forms of SQLAlchemy,
before methods like Select.where() were even added, would like:
# this is how it was documented in original SQLAlchemy versions
# many years ago
stmt = select([table1.c.myid], whereclause=table1.c.myid == table2.c.otherid)
Or even that the “whereclause” would be passed positionally:
# this is also how it was documented in original SQLAlchemy versions
# many years ago
stmt = select([table1.c.myid], table1.c.myid == table2.c.otherid)
For some years now, the additional “whereclause” and other arguments that are
accepted have been removed from most narrative documentation, leading to a
calling style that is most familiar as the list of column arguments passed
as a list, but no further arguments:
# this is how it's been documented since around version 1.0 or so
stmt = select([table1.c.myid]).where(table1.c.myid == table2.c.otherid)
The document at select() no longer accepts varied constructor arguments, columns are passed positionally describes this change in terms
of 2.0 Migration.
See also
select() no longer accepts varied constructor arguments, columns are passed positionally
Migrating to SQLAlchemy 2.0
A bind was located via legacy bound metadata, but since future=True is set on this Session, this bind is ignored.¶
The concept of “bound metadata” is present up until SQLAlchemy 1.4; as
of SQLAlchemy 2.0 it’s been removed.
This error refers to the MetaData.bind parameter on the
MetaData object that in turn allows objects like the ORM
Session to associate a particular mapped class with an
Engine. In SQLAlchemy 2.0, the Session must be
linked to each Engine directly. That is, instead of instantiating
the Session or sessionmaker without any arguments,
and associating the Engine with the
MetaData:
engine = create_engine("sqlite://")
Session = sessionmaker()
metadata_obj = MetaData(bind=engine)
Base = declarative_base(metadata=metadata_obj)
class MyClass(Base):
    # ...
session = Session()
session.add(MyClass())
session.commit()




    

The Engine must instead be associated directly with the
sessionmaker or Session.  The
MetaData object should no longer be associated with any
engine:
engine = create_engine("sqlite://")
Session = sessionmaker(engine)
Base = declarative_base()
class MyClass(Base):
    # ...
session = Session()
session.add(MyClass())
session.commit()
In SQLAlchemy 1.4, this 2.0 style behavior is enabled when the
Session.future flag is set on sessionmaker
or Session.
This Compiled object is not bound to any Engine or Connection¶
This error refers to the concept of “bound metadata”, which is a legacy
SQLAlchemy pattern present only in 1.x versions. The issue occurs when one invokes
the Executable.execute() method directly off of a Core expression object
that is not associated with any Engine:
metadata_obj = MetaData()
table = Table("t", metadata_obj, Column("q", Integer))
stmt = select(table)
result = stmt.execute()  # <--- raises
What the logic is expecting is that the MetaData object has
been bound to a Engine:
engine = create_engine("mysql+pymysql://user:pass@host/db")
metadata_obj = MetaData(bind=engine)
Where above, any statement that derives from a Table which
in turn derives from that MetaData will implicitly make use of
the given Engine in order to invoke the statement.
Note that the concept of bound metadata is not present in SQLAlchemy 2.0.
The correct way to invoke statements is via
the Connection.execute() method of a Connection:
with engine.connect() as conn:
    result = conn.execute(stmt)
When using the ORM, a similar facility is available via the Session:
result = session.execute(stmt)
See also
Basics of Statement Execution
This connection is on an inactive transaction.  Please rollback() fully before proceeding¶
This error condition was added to SQLAlchemy as of version 1.4, and does not
apply to SQLAlchemy 2.0.    The error
refers to the state where a Connection is placed into a
transaction using a method like Connection.begin(), and then a
further “marker” transaction is created within that scope; the “marker”
transaction is then rolled back using Transaction.rollback() or closed
using Transaction.close(), however the outer transaction is still
present in an “inactive” state and must be rolled back.
The pattern looks like:
engine = create_engine(...)
connection = engine.connect()
transaction1 = connection.begin()
# this is a "sub" or "marker" transaction, a logical nesting
# structure based on "real" transaction transaction1
transaction2 = connection.begin()
transaction2.rollback()
# transaction1 is still present and needs explicit rollback,
# so this will raise
connection.execute(text("select 1"))
Above, transaction2 is a “marker” transaction, which indicates a logical
nesting of transactions within an outer one; while the inner transaction
can roll back the whole transaction via its rollback() method, its commit()
method has no effect except to close the scope of the “marker” transaction
itself.   The call to transaction2.rollback() has the effect of
deactivating transaction1 which means it is essentially rolled back
at the database level, however is still present in order to accommodate
a consistent nesting pattern of transactions.
The correct resolution is to ensure the outer transaction is also
rolled back:
transaction1.rollback()
This pattern is not commonly used in Core.  Within the ORM, a similar issue can
occur which is the product of the ORM’s “logical” transaction structure; this
is described in the FAQ entry at “This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar).
The “subtransaction” pattern is removed in SQLAlchemy 2.0 so that this
particular programming pattern is no longer be available, preventing
this error message.
    
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
        Created using Sphinx 7.2.6.
    Documentation last generated: Wed 30 Oct 2024 02:18:58 PM  EDT
        
Website content copyright © by SQLAlchemy authors and contributors.
            SQLAlchemy and its documentation are licensed under the MIT license.
        
SQLAlchemy is a trademark of Michael Bayer.  mike(&)zzzcomputing.com
        All rights reserved. 
        
Website generation by
        zeekofile, with
        huge thanks to the Blogofile
        project.
        Mastodon
        Mastodon