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

Question

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 = ???

Answer 1

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.

Answer 2

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.

Answer 3

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.