Prefetch column sequence SQLAlchemy

Question

My model is quite complex and I'm trying to take logic from existing stored procedures and convert them to SQLAlchemy (for portability reasons).

I'm struggling however with uncommitted data.

I have user table: 1d, name I have status table: id, name I have user_statuses table: id, user_id, status_id, from_dt, to_dt

Now, I need to populate all of these tables inside a single transaction, or fail. The problem:

user = User(name = 'Test')
status = Status(name = 'Active')
db.session.add(user)
db.session.add(status)

# Oooopa! This is where it fails
user_session = UserStatuses(user_id=user.id, status_id=status.id, datetime.utcnow(), datetime(9999,01,01,00,00,00))
# both user.id and status.id = None as it's uncommited!

Essentially, I need to be able to access the table sequence WITHOUT explicit SQL. Why? For portability. Currently I use PGSQL and could do this:

class User(Base):
    ....
    @staticmethod
    def prefetch_id():
        db.session.execute("SELECT NEXTVAL('user_id_seq');").scalar()

Change the engine to MySQL & BANG! Application broken.

Any ideas on how to do this? Keeping in mind, this may be a very high transaction application being accessed by thousands of users at a time

Answer 1

Found the answer - don't know why I didn't see this before!

The Sequence object also has the ability to be executed standalone like a SQL expression, which has the effect of calling its “next value” function:

seq = Sequence('some_sequence')
nextid = connection.execute(seq)

Answer 2

If you flush the session after adding model objects but before committing:

db.session.add(user)
db.session.add(status)
db.session.flush()

then the objects add()-ed will get their sequence columns (id) updated so user.id, status.id won't be None any more.