sqlalchemy flush() and get inserted id?

Question

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?

-Dan

Answer 1

I'm no expert, but you might need to commit before you try to access the ID. Just flushing the session may not be enough.

f = Foo(bar='x')
session.add(f)
session.commit()
print f.id # should be not None

Answer 2

You should try using session.save_or_update(f) instead of session.add(f).

Answer 3

The answer is to not use sqlalchemy flush, or to even add the objects to the session. Doing the insert query manually in combination with postgres extension RETURNING id works well.

bind_params = []
for obj in new:
    d = obj.to_dict()
    d.pop('id', None)                
    bind_params.append(d)

r = session.execute(table.insert(postgres_returning=[table.c.id]), bind_params)
insert_ids = list(row[0] for row in r.fetchall())

for obj, id in izip(new, insert_ids):
    obj.__dict__['id'] = id

Answer 4

your sample code 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 the details of your mapping, if there are any odd quirks of the backend in use (such as, SQLite doesn't generate integer values for a composite primary key) and/or what the emitted SQL says when you turn on echo.