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

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.

Prefetch column sequence SQLAlchemy

Sign up