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 `dict`s and `set`s in lots of places) nor can I produce a compelling counterexample (short of the non-monotonicity in the sequence), and so +1 — SingleNegationElimination, Apr 15 '12 at 04:52

score 19 · Accepted Answer · edited May 05 '14 at 16:01

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.

@zzzeek Is there anyway to to control the ordering of statements in the case there is a circular fk? — Alex Rothberg, Sep 05 '18 at 04:59

score -3 · Answer 2 · answered Apr 14 '12 at 14:36

-3

No, it does them when you commit, not when you add.

answered Apr 14 '12 at 14:36

rapadura

5,242
7
39
57

I knew that queries are sent to db in flush() or commit(). But I guess sqlalchemy generates INSERT queries with the order of session.add() commands. (I tested with the above Entity class and postgres) – Taha Jahangir Apr 14 '12 at 14:39
Yes it generates insert queries, but the id (primary key) could be determined by the db, as there could be a trigger that executes on insert which increases the sequence nr and so on. – rapadura Apr 14 '12 at 14:41
1

The original asker is wondering whether or not the INSERT statements are emitted, upon commit, in the order that they were added. Upvoted, because I'm wondering the same. //Edit: figured it out! Answering in a bit. – munchybunch Nov 14 '13 at 23:27

Does SQLAlchemy save order when adding objects to session?

2 Answers2

Linked