How do I get the "id" after INSERT into MySQL database with Python?
PythonMysqlDatabasePython Problem Overview
I execute an INSERT INTO statement
cursor.execute("INSERT INTO mytable(height) VALUES(%s)",(height))
and I want to get the primary key.
My table has 2 columns:
id primary, auto increment
height this is the other column.
How do I get the "id", after I just inserted this?
Python Solutions
Solution 1 - 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.
Solution 2 - Python
Also, cursor.lastrowid (a dbapi/PEP249 extension supported by MySQLdb):
>>> import MySQLdb
>>> connection = MySQLdb.connect(user='root')
>>> cursor = connection.cursor()
>>> cursor.execute('INSERT INTO sometable VALUES (...)')
1L
>>> connection.insert_id()
3L
>>> cursor.lastrowid
3L
>>> cursor.execute('SELECT last_insert_id()')
1L
>>> cursor.fetchone()
(3L,)
>>> cursor.execute('select @@identity')
1L
>>> cursor.fetchone()
(3L,)
cursor.lastrowid is somewhat cheaper than connection.insert_id() and much cheaper than another round trip to MySQL.
Solution 3 - Python
Python DBAPI spec also define 'lastrowid' attribute for cursor object, so...
id = cursor.lastrowid
...should work too, and it's per-connection based obviously.
Solution 4 - Python
SELECT @@IDENTITY AS 'Identity';
or
SELECT last_insert_id();
Solution 5 - Python
This might be just a requirement of PyMySql in Python, but I found that I had to name the exact table that I wanted the ID for:
In:
cnx = pymysql.connect(host='host',
database='db',
user='user',
password='pass')
cursor = cnx.cursor()
update_batch = """insert into batch set type = "%s" , records = %i, started = NOW(); """
second_query = (update_batch % ( "Batch 1", 22 ))
cursor.execute(second_query)
cnx.commit()
batch_id = cursor.execute('select last_insert_id() from batch')
cursor.close()
batch_id
Out:
5
... or whatever the correct Batch_ID value actually is