Insert Multiple Rows into Table in Python MySQL

Python MySQL - Insert Multiple Rows into Table

To insert multiple rows or records into MySQL table in Python,

1. Create a connection to the MySQL database with user credentials and database name, using connect() function.
2. Get cursor object to the database using cursor() function.
3. 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.
4. 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

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.