Trent Trent - 4 months ago 30
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

table: 1d, name
I have
table: id, name
I have
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')

# Oooopa! This is where it fails
user_session = UserStatuses(,, datetime.utcnow(), datetime(9999,01,01,00,00,00))
# both and = 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):
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


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


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