getting the id of a created record in SQLAlchemy

Question

How can I get the id of the created record in SQLAlchemy?

I'm doing:

engine.execute("insert into users values  (1,'john')")

Answer 1

When you execute a plain text statement, you're at the mercy of the DBAPI you're using as to whether or not the new PK value is available and via what means. With SQlite and MySQL DBAPIs you'll have it as result.lastrowid, which just gives you the value of .lastrowid for the cursor. With PG, Oracle, etc., there's no ".lastrowid" - as someone else said you can use "RETURNING" for those in which case results are available via result.fetchone() (although using RETURNING with oracle, again not taking advantage of SQLAlchemy expression constructs, requires several awkward steps), or if RETURNING isn't available you can use direct sequence access (NEXTVAL in pg), or a "post fetch" operation (CURRVAL in PG, @@identity or scope_identity() in MSSQL).

Sounds complicated right ? That's why you're better off using table.insert(). SQLAlchemy's primary system of providing newly generated PKs is designed to work with these constructs. One you're there, the result.last_inserted_ids() method gives you the newly generated (possibly composite) PK in all cases, regardless of backend. The above methods of .lastrowid, sequence execution, RETURNING etc. are all dealt with for you (0.6 uses RETURNING when available).

Answer 2

There's an extra clause you can add: RETURNING

ie

INSERT INTO users (name, address) VALUES ('richo', 'beaconsfield') RETURNING id

Then just retrieve a row like your insert was a SELECT statement.