Trent Trent - 24 days ago 7
MySQL Question

Prefetch column sequence SQLAlchemy

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

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.