How To Insert Multiple Rows In MySQL Using Loop

In this tutorial, you will learn how to insert multiple rows in a MySQL database using a loop in the programming language of your choice.

This can be handy when you need to insert several rows of data at once and avoid calling multiple individual insert statements, thereby reducing server load and improving the overall efficiency of your code.

We will be using Python and the MySQL Connector package for this tutorial. However, feel free to use any other programming languages or libraries you prefer, such as PHP or JavaScript, as long as they provide MySQL support.

Step 1: Create MySQL Table

1
2
3
4
5
6
CREATE TABLE employees (
    id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);

Step 2: Create a connection with the MySQL database

After installing the MySQL Connector package, you need to create a connection with your MySQL database. Replace the placeholders with your actual database credentials.

1
2
3
4
5
6
7
8
import mysql.connector
 
cnx = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database_name"
)

Step 3: Create the data you want to insert

Now we need to create a list of tuples representing the rows to be inserted into the database. Let’s say we have a table named employees with columns id, first_name, last_name, and age:

1
2
3
4
5
6
data = [
    (1, "John", "Doe", 30),
    (2, "Jane", "Doe", 28),
    (3, "Alice", "Johnson", 24),
    (4, "Bob", "Smith", 35)
]

Step 4: Insert multiple rows using a loop

To insert multiple rows using a loop, we will use the executemany() method of the cursor object. This method executes the given database operation multiple times with a provided set of parameters.

1
2
3
4
5
6
7
cursor = cnx.cursor()
 
sql = "INSERT INTO employees (id, first_name, last_name, age) VALUES (%s, %s, %s, %s)"
 
cursor.executemany(sql, data)
 
cnx.commit()

In this example, the sql string contains our SQL query with placeholders (%s) for the values to be inserted. The executemany() method then loops through the data list and executes the SQL query for each value set.

Finally, remember to commit the transaction using cnx.commit().

Step 5: Close the connection

Once you have finished inserting the rows, you should close the cursor and the connection to the database:

1
2
cursor.close()
cnx.close()

Output

Full Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import mysql.connector
 
# Create a connection with MySQL database
cnx = mysql.connector.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database_name"
)
 
# Data to insert
data = [
    (1, "John", "Doe", 30),
    (2, "Jane", "Doe", 28),
    (3, "Alice", "Johnson", 24),
    (4, "Bob", "Smith", 35)
]
 
# Insert multiple rows using a loop
cursor = cnx.cursor()
sql = "INSERT INTO employees (id, first_name, last_name, age) VALUES (%s, %s, %s, %s)"
cursor.executemany(sql, data)
cnx.commit()
 
# Close connection
cursor.close()
cnx.close()

Conclusion

In this tutorial, you have learned how to insert multiple rows into a MySQL database using loops. This is an efficient way to process many records at once and improve the performance of your code.

Remember to close your connection after completing your task to prevent potential database issues. Happy coding!