How to Insert Multiple Records in Python MySQL

In this example we will show how to insert multiple records in Python MySQL.

Source Code

import mysql.connector

# connect to server
mydb = mysql.connector.connect(
    host="localhost",
    port=3306,
    user="yourusername",
    password="yourpassword",
    db="mydatabase"
)

# get a cursor
mycursor = mydb.cursor()

sql = "INSERT INTO US_STATE (State_name, Abbreviation, Capital, Total_area) VALUES (%s, %s, %s, %s)"
add_vals = [
    ('Alaska', 'AK', 'Juneau', 665384),
    ('Arizona', 'AZ', 'Phoenix', 113990),
    ('Arkansas', 'AR', 'Little Rock', 53179),
    ('California', 'CA', 'Sacramento', 163695),
    ('Colorado', 'CO', 'Denver', 104094),
    ('Connecticut', 'CT', 'Hartford', 5543)
]

# execute a query
mycursor.executemany(sql, add_vals)

# put the change into effect
mydb.commit()

print(mycursor.rowcount, "record inserted.")

# close the connection
mydb.close()

Output:

6 record inserted.