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()
CopyOutput
3 records inserted.
Table before inserting records
Table after inserting records
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.