SQLAlchemy - Return Generated Keys for INSERT?

Question

I am messing around with plain text SQL in SQLAlchemy. I'd prefer not to use ORM unless that is the only way to do this, but is there a way I can return the generated primary key value for a new record when I do an INSERT? Java's JDBC utility does this, and I'd be a bit surprised if there is not a way to do this with SQLAlchemy...

from sqlalchemy import create_engine, text


engine = create_engine('sqlite:///rexon_metals.db')
conn = engine.connect()

# I want this to return the generated primary key values
def insert_new_customer(customer_name, region, street_address, city, state, zip_code):
    stmt = text("INSERT INTO CUSTOMER (NAME, REGION, STREET_ADDRESS, CITY, STATE, ZIP) VALUES ("
                ":customer_name, :region, :street_address, :city, :state, :zip_code)")

    return conn.execute(stmt, customer_name=customer_name, region=region, street_address=street_address, city=city, state=state, zip_code=zip_code)

Answer 1

Maybe because it has been more than a year since the accepted answer, the answer did not solve the issue. So, I dug a little deeper into the docs of sqlalchemy and found .inserted_primary_key. It returns a tuple of the primary key of the single inserted row using the connection.execute() command.

Edit: Another 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.

Answer 2

You can get it from .lastrowid:

result = insert_new_customer(...)
result.lastrowid