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)
From https://dev.mysql.com/doc/c-api/8.0/en/mysql-insert-id.html:
mysql_insert_id()
returns0
following aCALL
statement for a stored procedure that generates anAUTO_INCREMENT
value because in this casemysql_insert_id()
applies toCALL
and not the statement within the procedure. Within the procedure, you can useLAST_INSERT_ID()
at the SQL level to obtain theAUTO_INCREMENT
value.The reason for the differences between
LAST_INSERT_ID()
andmysql_insert_id()
is thatLAST_INSERT_ID()
is made easy to use in scripts whilemysql_insert_id()
tries to provide more exact information about what happens to theAUTO_INCREMENT
column.
Get the new record primary key ID from MySQL insert query?
You need to use the LAST_INSERT_ID()
function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
Eg:
INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();
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
Typeerror: Can Only Concatenate Str (Not "Float") to Str
What Determines Which Strings Are Interned and When
How to Evaluate the Sum of Values Within Array Blocks
What Is the Fastest Way to Open Urls in New Tabs via Selenium - Python
When Are Objects Garbage Collected in Python
Tkinter Vanishing Photoimage Issue
Why Does This Not Work as an Array Membership Test
Differencebetween .Quit and .Quit in Pygame
How to Get the Row Count of a Pandas Dataframe
Configuring So That Pip Install Can Work from Github
Super() Raises "Typeerror: Must Be Type, Not Classobj" for New-Style Class
How to Compute Derivative Using Numpy
How to Implement a Binary Tree
What Are Good Uses for Python3's "Function Annotations"
How to Turn Off Info Logging in Spark
How to Add a Custom Ca Root Certificate to the Ca Store Used by Pip in Windows