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

Question

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

User · Accepted Answer

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

User · Answer

Python DBAPI spec also define  lastrowid  attribute for cursor object  so     id   cursor lastrowid      should work too  and it s per-connection based obviously

User · Answer

Also  cursor lastrowid  a dbapi PEP249 extension supported by MySQLdb     gt  gt  gt  import MySQLdb  gt  gt  gt  connection   MySQLdb connect user  root    gt  gt  gt  cursor   connection cursor    gt  gt  gt  cursor execute  INSERT INTO sometable VALUES         1L  gt  gt  gt  connection insert id   3L  gt  gt  gt  cursor lastrowid 3L  gt  gt  gt  cursor execute  SELECT last insert id     1L  gt  gt  gt  cursor fetchone    3L    gt  gt  gt  cursor execute  select   identity   1L  gt  gt  gt  cursor fetchone    3L     cursor lastrowid is somewhat cheaper than connection insert id   and much cheaper than another round trip to MySQL

User · Answer

SELECT   IDENTITY AS  Identity     or  SELECT last insert id

User · Answer

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    quot  quot  quot insert into batch set type    quot  s quot    records    i  started   NOW     quot  quot  quot  second query    update batch      quot Batch 1 quot   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

[python] How do I get the "id" after INSERT into MySQL database with Python?

Examples related to python

Examples related to mysql

Examples related to database