SQLAlchemy: Object Mappings lost after commit?

Question

Hey everyone,

I got a simple problem in SQLAlchemy. I have one model in a table, lets call it Model1 here. I want to add a row in this table, and get the autoincremented key, so I can create another model with it, and use this key. This is not a flawed database design (1:1 relation etc). I simply need this key in another table, because the other table is being transferred to a remote host, and I need the matching keys so the servers will understand each other. There will be no further local reference between these 2 tables, and it's also not possible to create relations because of that.

Consider the following code:

object1 = model.Model1(param)
DBSession.add(object1)

# if I do this, the line below fails with an UnboundExecutionError.
# and if I dont do this, object1.id won't be set yet
#transaction.commit()

object2 = model.AnotherModel(object1.id) #id holds the primary, autoincremented key

I wished I wouldn't even have to commit "manually". Basically what I would like to achieve is, "Model1" is constantly growing, with increasing Model.id primary key. AnotherModel is always only a little fraction of Model1, which hasn't been processed yet. Of course I could add a flag in "Model1", a boolean field in the table to mark already processed elements, but I was hoping this would not be necessary.

How can I get my above code working?

Greets,

Tom

Answer 1

I've only used this with ForeignKeys, so you in the second case rather would do model.AnotherModel(model1=object1), and then it just worked (tm). So I suspect this may be a problem with your models, so maybe you could post them too?

Answer 2

A couple of things:

• Could you please explain what the variable transaction is bound to?
• Exactly what statement raises the UnboundExecutionError?
• Please provide the full exception message, including stack trace.
• The 'normal' thing to do in this case, would be to call DBSession.flush(). Have you tried that?

Example:

object1 = Model1(param)
DBSession.add(object1)
DBSession.flush()
assert object1.id != None # flushing the session populates the id

object2 = AnotherModel(object1.id)

For a great explanation to the SA session and what flush() does, see Using the Session.

Basically, flush() causes Pending instances to become Persistent - meaning that new objects are INSERTed into the database tables. flush() also UPDATEs the tables with values for instances that the session tracks that has changes.

commit() always issues flush() first.

Within a transaction, you can flush multiple times. Each flush() causes UPDATEs and/or INSERTs in the database. The entire transaction can be commited or rolled back.

Answer 3

http://stackoverflow.com/questions/620610/sqlalchemy-obtain-primary-key-with-autoincrement-before-commit

This represented my original problem, that I need an autoincremented primary key before the actual commit.

I must also say, I am using turbogears, which implicitly commits at the end of your controller method, and forbids committing directly.

you can import transactions and then do transactions.commit() but this messes up my object bindings, as stated above.

Can anyone tell me please what the difference between flush and commit is?

Answer 4

if you want to get new primary key identifiers to generate without anything being committed, just call session.flush(). That will emit everything pending to the database within the current transaction.