Sessions / Queries

I’m re-loading data with my Session but it isn’t seeing changes that I committed elsewhere

The main issue regarding this behavior is that the session acts as thoughthe transaction is in the serializable isolation state, even if it’s not(and it usually is not). In practical terms, this means that the sessiondoes not alter any data that it’s already read within the scope of a transaction.

If the term “isolation level” is unfamiliar, then you first need to read this link:

Isolation Level

In short, serializable isolation level generally meansthat once you SELECT a series of rows in a transaction, you will getthe identical data back each time you re-emit that SELECT. If you are inthe next-lower isolation level, “repeatable read”, you’llsee newly added rows (and no longer see deleted rows), but for rows thatyou’ve already loaded, you won’t see any change. Only if you are in alower isolation level, e.g. “read committed”, does it become possible tosee a row of data change its value.

For information on controlling the isolation level when using theSQLAlchemy ORM, see Setting Transaction Isolation Levels.

To simplify things dramatically, the Session itself works interms of a completely isolated transaction, and doesn’t overwrite any mapped attributesit’s already read unless you tell it to. The use case of trying to re-readdata you’ve already loaded in an ongoing transaction is an uncommon usecase that in many cases has no effect, so this is considered to be theexception, not the norm; to work within this exception, several methodsare provided to allow specific data to be reloaded within the contextof an ongoing transaction.

To understand what we mean by “the transaction” when we talk about theSession, your Session is intended to only work withina transaction. An overview of this is at Managing Transactions.

Once we’ve figured out what our isolation level is, and we think thatour isolation level is set at a low enough level so that if we re-SELECT a row,we should see new data in our Session, how do we see it?

Three ways, from most common to least:

But remember, the ORM cannot see changes in rows if our isolationlevel is repeatable read or higher, unless we start a new transaction.

“This Session’s transaction has been rolled back due to a previous exception during flush.” (or similar)

This is an error that occurs when a Session.flush() raises an exception, rolls backthe transaction, but further commands upon the Session are called without anexplicit call to Session.rollback() or Session.close().

It usually corresponds to an application that catches an exceptionupon Session.flush() or Session.commit() anddoes not properly handle the exception. For example:

  1. from sqlalchemy import create_engine, Column, Integer
  2. from sqlalchemy.orm import sessionmaker
  3. from sqlalchemy.ext.declarative import declarative_base
  4.  
  5. Base = declarative_base(create_engine('sqlite://'))
  6.  
  7. class Foo(Base):
  8.     __tablename__ = 'foo'
  9.     id = Column(Integer, primary_key=True)
  10.  
  11. Base.metadata.create_all()
  12.  
  13. session = sessionmaker()()
  14.  
  15. # constraint violation
  16. session.add_all([Foo(id=1), Foo(id=1)])
  17.  
  18. try:
  19.     session.commit()
  20. except:
  21.     # ignore error
  22.     pass
  23.  
  24. # continue using session without rolling back
  25. session.commit()

The usage of the Session should fit within a structure similar to this:

  1. try:
  2.     <use session>
  3.     session.commit()
  4. except:
  5.    session.rollback()
  6.    raise
  7. finally:
  8.    session.close()  # optional, depends on use case

Many things can cause a failure within the try/except besides flushes.Applications should ensure some system of “framing” is applied to ORM-orientedprocesses so that connection and transaction resources have a definitiveboundary, and so that transactions can be explicitly rolled back if anyfailure conditions occur.

This does not mean there should be try/except blocks throughout an application,which would not be a scalable architecture. Instead, a typical approach isthat when ORM-oriented methods and functions are first called, the processthat’s calling the functions from the very top would be within a block thatcommits transactions at the successful completion of a series of operations,as well as rolls transactions back if operations fail for any reason,including failed flushes. There are also approaches using function decorators orcontext managers to achieve similar results. The kind of approach takendepends very much on the kind of application being written.

For a detailed discussion on how to organize usage of the Session,please see When do I construct a Session, when do I commit it, and when do I close it?.

But why does flush() insist on issuing a ROLLBACK?

It would be great if Session.flush() could partially complete and thennot roll back, however this is beyond its current capabilities since itsinternal bookkeeping would have to be modified such that it can be halted atany time and be exactly consistent with what’s been flushed to the database.While this is theoretically possible, the usefulness of the enhancement isgreatly decreased by the fact that many database operations require a ROLLBACKin any case. Postgres in particular has operations which, once failed, thetransaction is not allowed to continue:

  1. test=> create table foo(id integer primary key);
  2. NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
  3. CREATE TABLE
  4. test=> begin;
  5. BEGIN
  6. test=> insert into foo values(1);
  7. INSERT 0 1
  8. test=> commit;
  9. COMMIT
  10. test=> begin;
  11. BEGIN
  12. test=> insert into foo values(1);
  13. ERROR:  duplicate key value violates unique constraint "foo_pkey"
  14. test=> insert into foo values(2);
  15. ERROR:  current transaction is aborted, commands ignored until end of transaction block

What SQLAlchemy offers that solves both issues is support of SAVEPOINT, viaSession.begin_nested(). Using Session.begin_nested(), you can frame an operation that maypotentially fail within a transaction, and then “roll back” to the pointbefore its failure while maintaining the enclosing transaction.

But why isn’t the one automatic call to ROLLBACK enough? Why must I ROLLBACK again?

The rollback that’s caused by the flush() is not the end of the complete transactionblock; while it ends the database transaction in play, from the Sessionpoint of view there is still a transaction that is now in an inactive state.

Given a block such as:

  1. sess = Session()   # begins a logical transaction
  2. try:
  3.     sess.flush()
  4.  
  5.     sess.commit()
  6. except:
  7.     sess.rollback()

Above, when a Session is first created, assuming “autocommit mode”isn’t used, a logical transaction is established within the Session.This transaction is “logical” in that it does not actually use any databaseresources until a SQL statement is invoked, at which point a connection-leveland DBAPI-level transaction is started. However, whether or notdatabase-level transactions are part of its state, the logical transaction willstay in place until it is ended using Session.commit(),Session.rollback(), or Session.close().

When the flush() above fails, the code is still within the transactionframed by the try/commit/except/rollback block. If flush() were to fullyroll back the logical transaction, it would mean that when we then reach theexcept: block the Session would be in a clean state, ready toemit new SQL on an all new transaction, and the call toSession.rollback() would be out of sequence. In particular, theSession would have begun a new transaction by this point, which theSession.rollback() would be acting upon erroneously. Rather thanallowing SQL operations to proceed on a new transaction in this place wherenormal usage dictates a rollback is about to take place, the Sessioninstead refuses to continue until the explicit rollback actually occurs.

In other words, it is expected that the calling code will always callSession.commit(), Session.rollback(), or Session.close()to correspond to the current transaction block. flush() keeps theSession within this transaction block so that the behavior of theabove code is predictable and consistent.

How do I make a Query that always adds a certain filter to every query?

See the recipe at FilteredQuery.

My Query does not return the same number of objects as query.count() tells me - why?

The Query object, when asked to return a list of ORM-mapped objects,will deduplicate the objects based on primary key. That is, if wefor example use the User mapping described at Object Relational Tutorial,and we had a SQL query like the following:

  1. q = session.query(User).outerjoin(User.addresses).filter(User.name == 'jack')

Above, the sample data used in the tutorial has two rows in the addressestable for the users row with the name 'jack', primary key value 5.If we ask the above query for a Query.count(), we will get the answer2:

  1. >>> q.count()
  2. 2

However, if we run Query.all() or iterate over the query, we get backone element:

  1. >>> q.all()
  2. [User(id=5, name='jack', ...)]

This is because when the Query object returns full entities, theyare deduplicated. This does not occur if we instead request individualcolumns back:

  1. >>> session.query(User.id, User.name).outerjoin(User.addresses).filter(User.name == 'jack').all()
  2. [(5, 'jack'), (5, 'jack')]

There are two main reasons the Query will deduplicate:

  • To allow joined eager loading to work correctly - Joined Eager Loadingworks by querying rows using joins against related tables, where it then routesrows from those joins into collections upon the lead objects. In order to do this,it has to fetch rows where the lead object primary key is repeated for eachsub-entry. This pattern can then continue into further sub-collections suchthat a multiple of rows may be processed for a single lead object, such asUser(id=5). The dedpulication allows us to receive objects in the way theywere queried, e.g. all the User() objects whose name is 'jack' whichfor us is one object, withthe User.addresses collection eagerly loaded as was indicated eitherby lazy='joined' on the relationship() or via the joinedload()option. For consistency, the deduplication is still applied whether or notthe joinedload is established, as the key philosophy behind eager loadingis that these options never affect the result.

  • To eliminate confusion regarding the identity map - this is admittedlythe less critical reason. As the Sessionmakes use of an identity map, even though our SQL result set has tworows with primary key 5, there is only one User(id=5) object inside the Sessionwhich must be maintained uniquely on its identity, that is, its primary key /class combination. It doesn’t actually make much sense, if one is querying forUser() objects, to get the same object multiple times in the list. Anordered set would potentially be a better representation of what Queryseeks to return when it returns full objects.

The issue of Query deduplication remains problematic, mostly for thesingle reason that the Query.count() method is inconsistent, and thecurrent status is that joined eager loading has in recent releases beensuperseded first by the “subquery eager loading” strategy and more recently the“select IN eager loading” strategy, both of which are generally moreappropriate for collection eager loading. As this evolution continues,SQLAlchemy may alter this behavior on Query, which may also involvenew APIs in order to more directly control this behavior, and may also alterthe behavior of joined eager loading in order to create a more consistent usagepattern.

I’ve created a mapping against an Outer Join, and while the query returns rows, no objects are returned. Why not?

Rows returned by an outer join may contain NULL for part of the primary key,as the primary key is the composite of both tables. The Query object ignores incoming rowsthat don’t have an acceptable primary key. Based on the setting of the allow_partial_pksflag on mapper(), a primary key is accepted if the value has at least one non-NULLvalue, or alternatively if the value has no NULL values. See allow_partial_pksat mapper().

I’m using joinedload() or lazy=False to create a JOIN/OUTER JOIN and SQLAlchemy is not constructing the correct query when I try to add a WHERE, ORDER BY, LIMIT, etc. (which relies upon the (OUTER) JOIN)

The joins generated by joined eager loading are only used to fully load relatedcollections, and are designed to have no impact on the primary results of the query.Since they are anonymously aliased, they cannot be referenced directly.

For detail on this behavior, see The Zen of Joined Eager Loading.

Query has no len(), why not?

The Python len() magic method applied to an object allows the len()builtin to be used to determine the length of the collection. It’s intuitivethat a SQL query object would link len() to the Query.count()method, which emits a SELECT COUNT. The reason this is not possible isbecause evaluating the query as a list would incur two SQL calls instead ofone:

  1. class Iterates(object):
  2.     def __len__(self):
  3.         print("LEN!")
  4.         return 5
  5.  
  6.     def __iter__(self):
  7.         print("ITER!")
  8.         return iter([1, 2, 3, 4, 5])
  9.  
  10. list(Iterates())

output:

  1. ITER!
  2. LEN!

How Do I use Textual SQL with ORM Queries?

See:

I’m calling Session.delete(myobject) and it isn’t removed from the parent collection!

See Deleting Objects Referenced from Collections and Scalar Relationships for a description of this behavior.

why isn’t my init() called when I load objects?

See Constructors and Object Initialization for a description of this behavior.

how do I use ON DELETE CASCADE with SA’s ORM?

SQLAlchemy will always issue UPDATE or DELETE statements for dependentrows which are currently loaded in the Session. For rows whichare not loaded, it will by default issue SELECT statements to loadthose rows and update/delete those as well; in other words it assumesthere is no ON DELETE CASCADE configured.To configure SQLAlchemy to cooperate with ON DELETE CASCADE, seeUsing Passive Deletes.

I set the “foo_id” attribute on my instance to “7”, but the “foo” attribute is still None - shouldn’t it have loaded Foo with id #7?

The ORM is not constructed in such a way as to supportimmediate population of relationships driven from foreignkey attribute changes - instead, it is designed to work theother way around - foreign key attributes are handled by theORM behind the scenes, the end user sets up objectrelationships naturally. Therefore, the recommended way toset o.foo is to do just that - set it!:

  1. foo = Session.query(Foo).get(7)
  2. o.foo = foo
  3. Session.commit()

Manipulation of foreign key attributes is of course entirely legal. However,setting a foreign-key attribute to a new value currently does not triggeran “expire” event of the relationship() in which it’s involved. This meansthat for the following sequence:

  1. o = Session.query(SomeClass).first()
  2. assert o.foo is None  # accessing an un-set attribute sets it to None
  3. o.foo_id = 7

o.foo is initialized to None when we first accessed it. Settingo.foo_id = 7 will have the value of “7” as pending, but no flushhas occurred - so o.foo is still None:

  1. # attribute is already set to None, has not been
  2. # reconciled with o.foo_id = 7 yet
  3. assert o.foo is None

For o.foo to load based on the foreign key mutation is usually achievednaturally after the commit, which both flushes the new foreign key valueand expires all state:

  1. Session.commit()  # expires all attributes
  2.  
  3. foo_7 = Session.query(Foo).get(7)
  4.  
  5. assert o.foo is foo_7  # o.foo lazyloads on access

A more minimal operation is to expire the attribute individually - this canbe performed for any persistent object using Session.expire():

  1. o = Session.query(SomeClass).first()
  2. o.foo_id = 7
  3. Session.expire(o, ['foo'])  # object must be persistent for this
  4.  
  5. foo_7 = Session.query(Foo).get(7)
  6.  
  7. assert o.foo is foo_7  # o.foo lazyloads on access

Note that if the object is not persistent but present in the Session,it’s known as pending. This means the row for the object has not beenINSERTed into the database yet. For such an object, setting foo_id does nothave meaning until the row is inserted; otherwise there is no row yet:

  1. new_obj = SomeClass()
  2. new_obj.foo_id = 7
  3.  
  4. Session.add(new_obj)
  5.  
  6. # accessing an un-set attribute sets it to None
  7. assert new_obj.foo is None
  8.  
  9. Session.flush()  # emits INSERT
  10.  
  11. # expire this because we already set .foo to None
  12. Session.expire(o, ['foo'])
  13.  
  14. assert new_obj.foo is foo_7  # now it loads

Attribute loading for non-persistent objects

One variant on the “pending” behavior above is if we use the flagload_on_pending on relationship(). When this flag is set, thelazy loader will emit for new_obj.foo before the INSERT proceeds; anothervariant of this is to use the Session.enable_relationship_loading()method, which can “attach” an object to a Session in such a way thatmany-to-one relationships load as according to foreign key attributesregardless of the object being in any particular state.Both techniques are not recommended for general use; they were added to suitspecific programming scenarios encountered by users which involve the repurposingof the ORM’s usual object states.

The recipe ExpireRelationshipOnFKChange features an example using SQLAlchemy eventsin order to coordinate the setting of foreign key attributes with many-to-onerelationships.

How do I walk all objects that are related to a given object?

An object that has other objects related to it will correspond to therelationship() constructs set up between mappers. This code fragment williterate all the objects, correcting for cycles as well:

  1. from sqlalchemy import inspect
  2.  
  3.  
  4. def walk(obj):
  5.     deque = [obj]
  6.  
  7.     seen = set()
  8.  
  9.     while deque:
  10.         obj = deque.pop(0)
  11.         if obj in seen:
  12.             continue
  13.         else:
  14.             seen.add(obj)
  15.             yield obj
  16.         insp = inspect(obj)
  17.         for relationship in insp.mapper.relationships:
  18.             related = getattr(obj, relationship.key)
  19.             if relationship.uselist:
  20.                 deque.extend(related)
  21.             elif related is not None:
  22.                 deque.append(related)

The function can be demonstrated as follows:

  1. Base = declarative_base()
  2.  
  3.  
  4. class A(Base):
  5.     __tablename__ = 'a'
  6.     id = Column(Integer, primary_key=True)
  7.     bs = relationship("B", backref="a")
  8.  
  9.  
  10. class B(Base):
  11.     __tablename__ = 'b'
  12.     id = Column(Integer, primary_key=True)
  13.     a_id = Column(ForeignKey('a.id'))
  14.     c_id = Column(ForeignKey('c.id'))
  15.     c = relationship("C", backref="bs")
  16.  
  17.  
  18. class C(Base):
  19.     __tablename__ = 'c'
  20.     id = Column(Integer, primary_key=True)
  21.  
  22.  
  23. a1 = A(bs=[B(), B(c=C())])
  24.  
  25.  
  26. for obj in walk(a1):
  27.     print(obj)

Output:

  1. <__main__.A object at 0x10303b190>
  2. <__main__.B object at 0x103025210>
  3. <__main__.B object at 0x10303b0d0>
  4. <__main__.C object at 0x103025490>

Is there a way to automagically have only unique keywords (or other kinds of objects) without doing a query for the keyword and getting a reference to the row containing that keyword?

When people read the many-to-many example in the docs, they get hit with thefact that if you create the same Keyword twice, it gets put in the DB twice.Which is somewhat inconvenient.

This UniqueObject recipe was created to address this issue.

Why does post_update emit UPDATE in addition to the first UPDATE?

The postupdate feature, documented at Rows that point to themselves / Mutually Dependent Rows, involves that anUPDATE statement is emitted in response to changes to a particularrelationship-bound foreign key, in addition to the INSERT/UPDATE/DELETE thatwould normally be emitted for the target row. While the primary purpose of thisUPDATE statement is that it pairs up with an INSERT or DELETE of that row, sothat it can post-set or pre-unset a foreign key reference in order to break acycle with a mutually dependent foreign key, it currently is also bundled as asecond UPDATE that emits when the target row itself is subject to an UPDATE.In this case, the UPDATE emitted by post_update is _usually unnecessaryand will often appear wasteful.

However, some research into trying to remove this “UPDATE / UPDATE” behaviorreveals that major changes to the unit of work process would need to occur notjust throughout the post_update implementation, but also in areas that aren’trelated to post_update for this to work, in that the order of operations wouldneed to be reversed on the non-post_update side in some cases, which in turncan impact other cases, such as correctly handling an UPDATE of a referencedprimary key value (see #1063 for a proof of concept).

The answer is that “post_update” is used to break a cycle between twomutually dependent foreign keys, and to have this cycle breaking be limitedto just INSERT/DELETE of the target table implies that the ordering of UPDATEstatements elsewhere would need to be liberalized, leading to breakagein other edge cases.