Google News
logo
Python Program to Insert multiple rows
To insert multiple rows into a MySQL table using Python, you can use the `executemany()` method of the cursor object. Here's an example Python program that inserts multiple rows into a `customers` table :

Example :
import mysql.connector

# Create a connection to the MySQL server
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# Create a cursor object to execute SQL statements
mycursor = mydb.cursor()

# Define the data to be inserted
data = [
  ("John", "Highway 21"),
  ("Peter", "Lowstreet 4"),
  ("Amy", "Apple st 652"),
  ("Hannah", "Mountain 21"),
  ("Michael", "Valley 345")
]

# Insert multiple rows into the table
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
mycursor.executemany(sql, data)

# Commit the transaction
mydb.commit()

# Print a message to indicate success
print(mycursor.rowcount, "records inserted.")

# Close the connection
mydb.close()​
In the above example, the program defines the data to be inserted in a list (`data`) of tuples. Each tuple represents a row to be inserted into the `customers` table.

To insert the rows into the table, the program uses the `executemany()` method of the cursor object to execute an `INSERT INTO` statement multiple times, once for each row.

The statement is the same as in the previous example, but the second parameter to the `executemany()` method is the list of tuples (`data`) instead of a single tuple.

After the rows have been inserted, the program calls the `commit()` method of the connection object to commit the transaction. Finally, the program prints a message to indicate the number of records inserted and closes the connection.