Python mysql insert multiple rows

In the previous tutorial, we have seen how to create a database and connect it to the python program. In this tutorial, we will see how to insert a single row as well as multiple rows using the python program. Apart from this, we will see how we can get the id of the last inserted row. So, let’s get started!

Also read: Python MySQL Overview and Introduction

Insert Data into Table

We already know that the INSERT statement is used to insert the data into the table. However, let’s revise it one more time-

To insert a single row of data in all columns of the table-

INSERT INTO table VALUES(val1, val2, val3...);



Code language: SQL (Structured Query Language) (sql)

Note that, when you want to insert the data into all columns of the row, there is no need to mention the column names in the insert statement.

To insert a single row of data in particular columns of the table-

INSERT INTO table(col1,col2...) VALUES(val1,val2...);
Code language: SQL (Structured Query Language) (sql)

To insert multiple rows-

INSERT INTO table(col1,col2) VALUES(val1,val2), (val1,val2), (val1,val2);
Code language: SQL (Structured Query Language) (sql)

Also read: Python MySQL – Connect a MySQL Database with Python

In the examples below, we will use a table that we have already created using the MySQL CLI. Following is the table description of the ’emp’ table-

Insert a Single Row

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
sql = "INSERT INTO emp (name, city) VALUES (%s, %s)"
val = ("John", "Boston")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted successfully")
Code language: Python (python)

Here, we have first established the connection with the database. Then we created a cursor and wrote an insert query. Using the cursor, we execute that query.

Note that, even if you execute the query, the changes won’t be made in the table unless you write a statement mydb.commit().

the mycursor.rowcount statement returns how many rows are inserted into the table.

Let’s run the python program and check the emp table to see if the data is inserted into it.

SELECT * FROM EMP;
Code language: SQL (Structured Query Language) (sql)

Insert Multiple Rows

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
sql = "INSERT INTO emp (name, city) VALUES (%s, %s)"
val = [
    ("John", "Boston"),
    ("Anna", "Mumbai"),
    ("Peter", "Boston"),
    ("Sara", "Gurgaon"),
    ("Ester", "Mumbai")
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, " records inserted successfully")
Code language: Python (python)

Here, instead of writing multiple values in the insert statement, we have only specified the type of data that we will insert.

Then, we have created the list of values that we want to insert.

Note that, we must use the executemany() method when there are multiple rows has to be inserted into the table.

Finally, we make changes in the table using the commit() method.

Let’s run the program and check the table to see if all values are inserted correctly.

SELECT * FROM emp;



Code language: SQL (Structured Query Language) (sql)

Get the id of Inserted Record

By using the cursor object, we can get the id of the row that we have just inserted.

If we insert multiple rows, the object will return the id of the last inserted row.

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="1234",
    database="journaldev"
)
mycursor = mydb.cursor()
sql = "INSERT INTO emp (name, city) VALUES (%s, %s)"
val = ("Sean", "San Jose")
mycursor.execute(sql, val)
mydb.commit()
print("id of the last record is ", mycursor.lastrowid)
Code language: Python (python)

Here, have a look at the last line of the code. The lastrowid property returns the id of the last inserted row.

Till now, we have six records present in the table. When we execute the above program, we must get the if 7 in the output.

As you can see, we have got the correct output.

Conclusion

In this tutorial, we have learned how to insert a single row as well as multiple rows into the MySQL table using the python program. We have learned to get the id of the last inserted row which is quite useful when you want to assign a unique id based on the auto_increment id such as student PRN number, employee number or order id, etc. In the next tutorial, we will learn to fetch the data from the MySQL table.