Python MySQL – Insert Multiple Rows into Table
To insert multiple rows or records into MySQL table in Python,
- Create a connection to the MySQL database with user credentials and database name, using connect() function.
- Get cursor object to the database using cursor() function.
- Take INSERT INTO table query, and list of rows to be inserted, where each item in the list is a tuple with column values for each record.
- Call executemany() function on the cursor object, and pass the insert query string, and values list as arguments to executemany() function.
Example
Consider that there is a schema named mydatabase
in MySQL. The credentials to access this database are: user – root
and password – admin1234
, and there is a table named fruits
in mydatabase
.
In the following program, we insert three records into fruits
table in a single statement.
Python Program
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="admin1234",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "INSERT INTO fruits (name, quantity, country) VALUES (%s, %s, %s)"
val = [
("Banana", 40, "Mexico"),
("Mango", 15, "India"),
("Avocado", 37, "Mexico")
]
try:
mycursor.executemany(sql, val)
print(mycursor.rowcount, "records inserted.")
except:
print('Something went wrong.')
mydb.commit()
Output
3 records inserted.
Table before inserting records
![](https://pythonexamples.org/wp-content/uploads/2023/01/python-mysql-insert-multiple-rows-into-table-1.png)
Table after inserting records
![Python MySQL - Insert Multiple Rows into Table](https://pythonexamples.org/wp-content/uploads/2023/01/python-mysql-insert-multiple-rows-into-table-2.png)
Summary
In this tutorial of Python Examples, we learned how to insert multiple rows or records into table in MySQL database from a Python program.