CODEWITHSUNDEEP
pythonmysqlsqlalchemy
user3450049
user3450049

Reputation: 865

How to get lastrowid from sqlalchemy after INSERT .. ON DUPLICATE KEY UPDATE?

I'm using sqlalchemy to do an INSERT ... ON DUPLICATE KEY UPDATE in MySQL, and I'm trying to find the lastrowid of the primary key that was inserted or updated.

However, when the DUPLICATE clause is triggered, the returned lastrowid is 0, when I expected it to be the primary key of the record that was just updated. Minimal code to reproduce -- assume for this example that the combination of first_name+last_name is the unique key:

from sqlalchemy import create_engine
eng = create_engine(connection_string)
query = "INSERT INTO user (first_name, last_name) VALUES ('Jim', 'Brown') ON DUPLICATE KEY UPDATE first_name='Jim' "
record_id = eng.execute(query).lastrowid

After this runs, if there is a duplicate, then record_id is 0 instead of the primary key value. I expect to see. How can I get the primary key of the updated row?

I've also tried inserted_primary_key, which throws the error: 

sqlalchemy.exc.InvalidRequestError: Statement is not a compiled expression construct.

Upvotes: 1

Views: 4185

Answers (1)

totalhack
totalhack

Reputation: 2608

The comments hint at this but figured I'd reference a good example that shows how to do insert ... on duplicate key update with LAST_INSERT_ID in sqlalchemy . Check out this gist. Lifting the code here for reference but please give the author a star.

from app import db
from sqlalchemy import func
from sqlalchemy.dialects.mysql import insert

def upsert(model, insert_dict):
    """model can be a db.Model or a table(), insert_dict should contain a primary or unique key."""
    inserted = insert(model).values(**insert_dict)
    upserted = inserted.on_duplicate_key_update(
        id=func.LAST_INSERT_ID(model.id), **{k: inserted.inserted[k]
                               for k, v in insert_dict.items()})
    res = db.engine.execute(upserted)
    return res.lastrowid

Note that inserted_primary_key also works for me with this approach.

Upvotes: 3

Related Questions