sqlalchemy flush() and get inserted id?

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

I've just run across the same problem, and after testing I have found that NONE of these answers are sufficient.

Currently, or as of sqlalchemy .6+, there is a very simple solution (I don't know if this exists in prior version, though I imagine it does):

session.refresh()

So, your code would look something like this:

f = Foo(bar=x)
session.add(f)
session.flush()
# At this point, the object f has been pushed to the DB, 
# and has been automatically assigned a unique primary key id

f.id
# is None

session.refresh(f)
# refresh updates given object in the session with its state in the DB
# (and can also only refresh certain attributes - search for documentation)

f.id
# is the automatically assigned primary key ID given in the database.

That's how to do it.