sqlalchemy flush() and get inserted id?

I want to do something like this:

f = Foo(bar='x')
session.add(f)
session.flush()

# do additional queries using f.id before commit()
print f.id # should be not None

session.commit()

But f.id is None when I try it. How can I get this to work?

Answer

Your sample code should have worked as it is. SQLAlchemy should be providing a value for f.id, assuming its an autogenerating primary-key column. Primary-key attributes are populated immediately within the flush() process as they are generated, and no call to commit() should be required. So the answer here lies in one or more of the following:

1. The details of your mapping
2. If there are any odd quirks of the backend in use (such as, SQLite doesn’t generate integer values for a composite primary key)
3. What the emitted SQL says when you turn on echo