16

I have a simple table in mysql with the following fields:

  • id -- Primary key, int, autoincrement
  • name -- varchar(50)
  • description -- varchar(256)

Using MySQLdb, a python module, I want to insert a name and description into the table, and get back the id.

In pseudocode:

db = MySQLdb.connection(...)
queryString = "INSERT into tablename (name, description) VALUES" % (a_name, a_desc);"

db.execute(queryString);
newID = ???
grieve
  • 13,220
  • 10
  • 49
  • 61
  • Duplicate of [How do I get the IDENTITY / AUTONUMBER value for the row I inserted in pymysql](http://stackoverflow.com/questions/6802061/how-do-i-get-the-identity-autonumber-value-for-the-row-i-inserted-in-pymysql), which is a better Q&A pair; this one has a wrong answer accepted with the right answer edited in underneath, which is weird and unhelpful. – Mark Amery Nov 30 '16 at 14:51

4 Answers4

28

I think it might be

newID = db.insert_id()

Edit by Original Poster

Turns out, in the version of MySQLdb that I am using (1.2.2) You would do the following:

conn = MySQLdb(host...)

c = conn.cursor()
c.execute("INSERT INTO...")
newID = c.lastrowid

I am leaving this as the correct answer, since it got me pointed in the right direction.

grieve
  • 13,220
  • 10
  • 49
  • 61
David Z
  • 128,184
  • 27
  • 255
  • 279
  • Do this *before* the commit, if you are not automatically commiting. – kmarsh Apr 22 '14 at 19:08
  • @kmarsh I know this comment is super old, but do you remember why it should be before the commit? – Nick Zuber Aug 10 '17 at 19:24
  • I guess because the code is done with the cursor, and will lose context after the commit. Shorter answer, it worked better for me that way. – kmarsh Aug 22 '17 at 13:05
  • what if we have a scenario where two concurrent processed inserted new records in db. What is the authenticity that it will return correct record id? – Mubeen Mubarik Jul 15 '21 at 06:55
2

I don't know if there's a MySQLdb specific API for this, but in general you can obtain the last inserted id by SELECTing LAST_INSERT_ID()

It is on a per-connection basis, so you don't risk race conditions if some other client performs an insert as well.

Stefano Borini
  • 138,652
  • 96
  • 297
  • 431
0

You could also do a

conn.insert_id

Abdul Bijur V A
  • 74
  • 1
  • 1
-8

The easiest way of all is to wrap your insert with a select count query into a single stored procedure and call that in your code. You would pass in the parameters needed to the stored procedure and it would then select your row count.

TheTXI
  • 37,429
  • 10
  • 86
  • 110