9

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

Balthazar Rouberol
  • 6,822
  • 2
  • 35
  • 41
Trent
  • 2,909
  • 1
  • 31
  • 46
  • What is `UserStatuses`? Are you trying to create a user session with a user that does not exist or something? What's wrong with just starting a transaction? – Spencer Rathbun Feb 25 '13 at 19:32
  • Your solution is great. Another option to solve your problem could be to directly pass the objects `user` and `status` to the `UserStatuses` constructor. Even though they don't have an `id` yet, the session will be able to resolve all the relationships appropriately. – mtth Feb 26 '13 at 13:50
  • Spencer, unfortunately my example was not too good. In reality I have 7 or 8 completely separate objects to create, but it's all or nothing. If one part fails, the whole transaction needs to fail. Therefore, I cannot commit the User object before I know the UserStatus object has been successfully created. – Trent Feb 26 '13 at 16:15
  • mtth, unfortunately it doesn't in this specific way. I read somewhere yesterday that I can also pass an Object (User), I will test and see if that does what I'm looking for. – Trent Feb 26 '13 at 16:17

2 Answers2

19

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)
Trent
  • 2,909
  • 1
  • 31
  • 46
4

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.

András Aszódi
  • 8,948
  • 5
  • 48
  • 51