python - Does SQLAlchemy save order when adding objects to session?

Question

Does this assert always pass or not? In other words, does SQLAlchemy save the order (in generating INSERT queries) when adding new objects to session?

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.engine import create_engine
from sqlalchemy.types import Integer
from sqlalchemy.schema import Column

engine = create_engine('sqlite://')
Base = declarative_base(engine)
Session = sessionmaker(bind=engine)
session = Session()

class Entity(Base):
    __tablename__ = 'entity'
    id = Column(Integer(), primary_key=True)
Entity.__table__.create(checkfirst=True)


first = Entity()
session.add(first)

second = Entity()
session.add(second)

session.commit()
assert second.id > first.id
print(first.id, second.id)

None, in production I'm using postgresql, sqlite is for testing.

I can neither find the code in sqlalchemy that causes this to be so (its dicts and sets in lots of places) nor can I produce a compelling counterexample (short of the non-monotonicity in the sequence), and so +1 — SingleNegationElimination

munchybunch munchybunch · Accepted Answer · 2013-11-14T23:42:36

After looking a bit at the SQLAlchemy source, it looks like add() records when it was inserted: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/session.py#L1719

The relevant snippet:

def _save_impl(self, state):
    if state.key is not None:
        raise sa_exc.InvalidRequestError(
            "Object '%s' already has an identity - it can't be registered "
            "as pending" % state_str(state))

    self._before_attach(state)
    if state not in self._new:
        self._new[state] = state.obj()
        state.insert_order = len(self._new) # THE INSERT ORDER IS SAVED!
    self._attach(state)

And this is called from Session.add => self._save_or_update_state => self._save_or_update_impl => self._save_impl.

It's then used in _sort_states when saving: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/persistence.py#L859

Unfortunately, this is only implementation-level proof. I couldn't find anything in the documentation that guarantees it...

Update: I've since looked a bit more into this, it turns out there's a concept called Unit of Work in SQLAlchemy that somewhat defines the order during a flush: http://www.aosabook.org/en/sqlalchemy.html (search for Unit of Work).

Within the same class, the order is indeed determined by the order that add was called. However, you may see different orderings in the INSERTs between different classes. If you add object a of type A and later add object b of type B, but a turns out to have a foreign key to b, you'll see an INSERT for b before the INSERT for a.

python - Does SQLAlchemy save order when adding objects to session?

2 Answers