How do I get the "id" after INSERT into MySQL database with Python?

Question

I execute an INSERT INTO statement

cursor.execute("INSERT INTO mytable(height) VALUES(%s)",(height))

and I want to get the primary key.

My table has 2 columns:

id      primary, auto increment
height  this is the other column.

How do I get the "id", after I just inserted this?

Wimer

#707255 – Vitiligo

Answer 1

Use cursor.lastrowid to get the last row ID inserted on the cursor object, or connection.insert_id() to get the ID from the last insert on that connection.

Orfinger

What if two processes inserting a row at the same time using the same connection. Which id will insert_id return? – Herschelherself

@Herschelherself How do 2 processes use the same connection? – Sooth

Is lastrowid only available after the current transaction being committed? – Drivein

@Sooth He probably meant threads, I've done that and it can cause issues unless you properly utilize threadsafety. I've personally gone for instantiating a new connection for each thread, which is a cute workaround since for some reason committing (autocommitting actually) didn't work for me, I got some serious interweaving due to many concurrent threads all issuing a few queries per second. – Background

Does not works with duplicated records using insert, select and where. – Florin

Answer 2

Also, cursor.lastrowid (a dbapi/PEP249 extension supported by MySQLdb):

>>> import MySQLdb
>>> connection = MySQLdb.connect(user='root')
>>> cursor = connection.cursor()
>>> cursor.execute('INSERT INTO sometable VALUES (...)')
1L
>>> connection.insert_id()
3L
>>> cursor.lastrowid
3L
>>> cursor.execute('SELECT last_insert_id()')
1L
>>> cursor.fetchone()
(3L,)
>>> cursor.execute('select @@identity')
1L
>>> cursor.fetchone()
(3L,)

cursor.lastrowid is somewhat cheaper than connection.insert_id() and much cheaper than another round trip to MySQL.

Quick

Why is cursor.lastrowid cheaper than connection.insert_id()? – Amongst

Only because cursor.lastrowid is automatically set on the cursor object as part of cursor.execute() and is just an attribute lookup. connection.insert_id() is an additional unnecessary function call - an which has already been called and whose result is available on the lastrowid attribute. – Quick

I've just had a problem where cursor.lastrowid returned something different than connection.insert_id(). cursor.lastrowid returned the last insert id, connection.insert_id() returned 0. How can that be? – Amongst

@moose, maybe concurrent processes are doing parallel database insertion using the same connection. – Herschelherself

Why is there a L behind the number? @Quick – Engage

@FlyingAtom, because this was run on python2 instead of python3. – Quick

@FlyingAtom, To clarify, the 'L' suffix is for "long integer", an artifact of python2. The example here was from python2 instead of python3. The MySQLdb connector still doesn't support python3 (although there are now forks that do, but those didn't exist at the time). See PEP 237 – Quick

!!!apparently insert_id() method is now obsolete and lastrowid must be used: sourceforge.net/p/mysql-python/discussion/70461/thread/f3987b9f – Hectorhecuba

Answer 3

Python DBAPI spec also define 'lastrowid' attribute for cursor object, so...

id = cursor.lastrowid

...should work too, and it's per-connection based obviously.

Patinated

Answer 4

SELECT @@IDENTITY AS 'Identity';

or

SELECT last_insert_id();

Fortunna

this allows for race conditions because you're requesting the last row id from the server. because me, you don't want that mess. – Dachshund

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. – Fortunna

I want to point out this part is equally as important: "Each client will receive the last inserted ID for the last statement that client executed." So you'll get a different value from Workbench than running the exact same select last_insert_id() from the CLI on a Linux machine – Wigley

Answer 5

This might be just a requirement of PyMySql in Python, but I found that I had to name the exact table that I wanted the ID for:

In:

cnx = pymysql.connect(host='host',
                            database='db',
                            user='user',
                            password='pass')
cursor = cnx.cursor()
update_batch = """insert into batch set type = "%s" , records = %i, started = NOW(); """
second_query = (update_batch % ( "Batch 1", 22  ))
cursor.execute(second_query)
cnx.commit()
batch_id = cursor.execute('select last_insert_id() from batch')
cursor.close()

batch_id

Out: 5
... or whatever the correct Batch_ID value actually is

Kruller

@krzys_h Thanks for looking at this K but your edit fails in my testing and so I have rejected your edit. If you wouldn't mind also backing the edit out? – Kruller