How to Get the "Id" After Insert into MySQL Database with Python

How do I get the id after INSERT into MySQL database with Python?

Use cursor.lastrowid to get the last row ID inserted on the cursor object, or connection.insert_id() to get the ID from the last insert on that connection.

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

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.

Getting last insert id from MySQL stored in Python

cursor.lastrowid is set from mysql_insert_id() instead of LAST_INSERT_ID().

You can manually set it from LAST_INSERT_ID():

cursor.callproc(procedure_name, params_list)
cursor.execute('SELECT LAST_INSERT_ID()')) # Add this
cursor.lastrowid = cursor.fetchone()[0] # Add this
print('last id:', cursor.lastrowid)


mysql_insert_id() returns 0 following a CALL statement for a stored procedure that generates an AUTO_INCREMENT value because in this case mysql_insert_id() applies to CALL and not the statement within the procedure. Within the procedure, you can use LAST_INSERT_ID() at the SQL level to obtain the AUTO_INCREMENT value.

The reason for the differences between LAST_INSERT_ID() and mysql_insert_id() is that LAST_INSERT_ID() is made easy to use in scripts while mysql_insert_id() tries to provide more exact information about what happens to the AUTO_INCREMENT column.

Get the new record primary key ID from MySQL insert query?

You need to use the LAST_INSERT_ID() function:


INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);

This will get you back the PRIMARY KEY value of the last row that you inserted:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

How can the id automaticly inserted in python mysql?

remove your column id_settings during insert since its auto increment.

 query = """INSERT INTO setting (name, address) VALUES (%s, %s) """

Related Topics

Leave a reply