How do you safely and efficiently get the row id after an insert with mysql using MySQLdb in python?

16
votes

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 = ???
4
pythonmysql
Duplicate of 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

4 Answers

28
votes

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.

2
votes

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.

0
votes

You could also do a

conn.insert_id

-7
votes

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.