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

How to Get the "id" After INSERT into MySQL Database with Python?

So you just executed an INSERT INTO statement in Python to add data to your MySQL database. Nice work! 👏 But now you're wondering how to get the "id" value of the newly inserted record. Don't worry, I've got you covered! In this blog post, I'll explain how to retrieve the primary key ("id") after an INSERT, using Python and MySQL. Let's dive in! 💪

The Setup 🛠️

Before we jump into the solution, let's quickly set up the context. Assume we have a table named "mytable" with two columns: "id" (primary key with auto-increment) and "height". We want to insert a new record into this table and retrieve the "id" value afterwards. Here's the code snippet for the INSERT statement you provided:

cursor.execute("INSERT INTO mytable(height) VALUES(%s)",(height))

Now, let's see how we can obtain the "id" column value using Python.

Solution 1: Using LAST_INSERT_ID() 🆔

One way to get the "id" value after an INSERT is by using the LAST_INSERT_ID() function provided by MySQL. Here's how you can modify your code to achieve this:

cursor.execute("INSERT INTO mytable(height) VALUES(%s)",(height))
cursor.execute("SELECT LAST_INSERT_ID()")
last_id = cursor.fetchone()[0]

In the above code snippet, we execute the INSERT INTO statement as you did before. After that, we execute a separate SELECT statement to retrieve the last inserted ID using LAST_INSERT_ID(). Finally, we fetch the value using fetchone()[0] and store it in the last_id variable.

Solution 2: Using cursor.lastrowid 🆔

If you're using the mysql-connector-python library, you can retrieve the last inserted ID directly from the cursor object without an additional SELECT statement. Here's how you can modify your code to achieve this:

cursor.execute("INSERT INTO mytable(height) VALUES(%s)",(height))
last_id = cursor.lastrowid

In this solution, we simply assign the value of cursor.lastrowid to the last_id variable after executing the INSERT statement. This attribute holds the last inserted ID without the need for additional queries.

Easy peasy, right? 🎉

By following one of these two solutions, you can easily retrieve the "id" value after an INSERT into a MySQL database using Python. Give it a try, and you'll be grabbing those primary keys in no time! 😉

Do you have any other MySQL or Python-related questions? Need help with any other tech issues? Let me know in the comments below! I'm here to help you out. 👇

Happy coding! 💻