Populate primary key value for the row just inserted into DBAPI's lastrowid and SQLAlchemy's inserted_primary_key attributes

[amotl]

Problem

In SQLAlchemy (ORM) land, the standard is that the cursor object provides inserted_primary_key and lastrowid attributes. However, currently, both of them do not return anything sensible.

insert_stmt = table.insert(dict(name="John Doe", age=37))
cursor = engine.execute(insert_stmt)
print(cursor.inserted_primary_key)
print(cursor.lastrowid)

()
None

Motivation

dataset uses SQLAlchemy's inserted_primary_key attribute for returning the pk value from its insert method, see https://github.com/pudo/dataset/blob/1.5.2/dataset/table.py#L120-L121. It is important to make this work in order to be able to reference records just inserted, for manipulating them later, or for referencing them elsewhere.

Other statements

.inserted_primary_key returns a tuple of the primary key of the single inserted row using the connection.execute() command. A good reason to use .inserted_primary_key instead of .lastrowid: .lastrowid is database specific, it may work for some databases while not for others. But .inserted_primary_key will always work regardless of the underlying database.

-- https://stackoverflow.com/a/51258633