233

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?

Martin Thoma
  • 124,992
  • 159
  • 614
  • 958
TIMEX
  • 259,804
  • 351
  • 777
  • 1,080
  • 1
    http://stackoverflow.com/questions/706755/how-do-you-safely-and-efficiently-get-the-row-id-after-an-insert-with-mysql-usin – pmav99 Nov 11 '13 at 14:43

5 Answers5

327

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.

Amber
  • 507,862
  • 82
  • 626
  • 550
  • 4
    What if two processes inserting a row at the same time using the same connection. Which id will `insert_id` return? – xiaohan2012 Jun 18 '14 at 12:08
  • 40
    @xiaohan2012 How do 2 processes use the same connection? – hienbt88 Aug 13 '14 at 03:47
  • 7
    Is `lastrowid` only available after the current transaction being committed? – John Wang Jan 19 '17 at 02:31
  • 8
    @hienbt88 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. – Milan Velebit Aug 08 '18 at 09:40
  • Does not works with duplicated records using insert, select and where. – e-info128 Oct 27 '18 at 19:20
134

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.

warvariuc
  • 57,116
  • 41
  • 173
  • 227
Andrew
  • 1,715
  • 2
  • 13
  • 7
  • 5
    Why is `cursor.lastrowid` cheaper than `connection.insert_id()`? – Martin Thoma Mar 18 '14 at 09:10
  • 3
    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. – Andrew Mar 26 '14 at 19:58
  • 6
    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? – Martin Thoma Apr 25 '14 at 08:55
  • 1
    @moose, maybe concurrent processes are doing parallel database insertion using the same connection. – xiaohan2012 Jun 18 '14 at 12:10
  • Why is there a L behind the number? @Andrew – CodeGuru Feb 15 '16 at 00:28
  • 1
    @FlyingAtom, because this was run on python2 instead of python3. – Andrew Feb 16 '16 at 20:05
  • 1
    @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](https://www.python.org/dev/peps/pep-0237/) – Andrew Feb 16 '16 at 20:14
  • !!!apparently insert_id() method is now obsolete and lastrowid must be used: https://sourceforge.net/p/mysql-python/discussion/70461/thread/f3987b9f/ – Wanting to be anAndroidDevelor Jan 18 '20 at 18:45
41

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

id = cursor.lastrowid

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

Htechno
  • 5,901
  • 4
  • 27
  • 37
8
SELECT @@IDENTITY AS 'Identity';

or

SELECT last_insert_id();
Keith
  • 924
  • 6
  • 14
  • 3
    this allows for race conditions because you're requesting the last row id from the server. because me, you don't want that mess. – Joshua Burns Feb 10 '12 at 21:14
  • 9
    [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.](http://dev.mysql.com/doc/refman/5.5/en/getting-unique-id.html) – Keith Feb 15 '12 at 20:18
  • 1
    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 – simplycoding Jan 19 '17 at 06:42
0

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

Edward
  • 179
  • 2
  • 12
  • @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? – Edward Jul 13 '20 at 20:58