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

Question

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.

I assume the combination of `first_name`,`last_name` is the primary/unique key? So for duplicates/`record_id=0` it has to be looked up. Also names aren't unique, trust someone called Daniel Black to know :-) — danblack, May 13 '20 at 04:21
Your construction is not valid. Perform SELECT with new values in WHERE and retrieve according `id`. — Akina, May 13 '20 at 04:49
Can you [edit] the question to include the output of `DESCRIBE user;`? — snakecharmerb, May 13 '20 at 07:14
Check https://stackoverflow.com/questions/778534/mysql-on-duplicate-key-last-insert-id, the workaround for when `ON DUPLICATE KEY UPDATE` is triggered based on unique key instead of the primary is to include `ID = LAST_INSERT_ID(ID)` in the UPDATE. — Ilja Everilä, May 13 '20 at 09:43

score 3 · Answer 1 · answered Nov 24 '20 at 21:25

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.

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

1 Answers1