How to retrieve the ID after a MySQL Insert in Python-pymysql

When you have an auto_increment field on a table, and you’re doing a batch import of new data, you often want to insert a row, then insert more rows that have a foreign key referencing the first row. For example, you want to insert a COMPANY entity, then insert a few dozen PERSON entities under that company. Assuming you have no unique and not-null fields on the COMPANY entity (if company_name were unique and not-null, then you could just issue a select immediately after inserting to get its ID), and assuming you want a thread-safe solution (you could just select for the highest ID in the table immediately after inserting, but since MySQL is by default not transaction-safe, another thread could come in and insert another company right after you insert and before your select), you simply need to have mysql_insert_id().

The MySQLdb documentation mentions conn.insert_id(), however this appears to have been deprecated, maybe? This really should be on the cursor object anyway. And behold! There is a lastrowid on it! It’s just a little bit undocumented:

We are using here pymysql client library:

import pymysql

conn = pymysql.connect(
db='student_record',
user='student_record',
passwd='student_record_123456',
host='localhost')
c = conn.cursor()

name='john doe'

dept='information technology'

query="INSERT INTO student(name,dept) VALUES(%s,%s)"
c.execute(query,(name,dept))
conn.commit()
print c.lastrowid  #will print last insert mysql auto_increment id

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s