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
Gatelockservice 