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
– SingleNegationElimination, Commented Apr 15, 2012 at 4:52

ddaa · Accepted Answer · 2014-05-05 16:01:01Z

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.

rapadura · Accepted Answer · 2012-04-14 14:36:26Z

-3

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

answered Apr 14, 2012 at 14:36

rapadura

5,3408 gold badges42 silver badges58 bronze badges

3 Comments

Taha Jahangir Over a year ago

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)

rapadura Over a year ago

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.

munchybunch Over a year ago

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.

Collectives™ on Stack Overflow

Does SQLAlchemy save order when adding objects to session?

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related