Insert multiple rows in MySQL table in Python

In this Python tutorial, we will learn how to insert multiple rows in MySQL table in Python. In my previous tutorial, I have shown you How to insert data into MySQL Table in Python Programming. But then I felt that inserting multiple rows into a MySQL table with that execute() method will be annoying for us. So here I am going to show you how to insert multiple rows at once into MySQL table in a single Python program.

Insert Multiple Rows at Once in MySQL Table in Python

Before jumping to the discussion, please take a look at these requirements,

If the above things are already done and you already have a table in your MySQL database then you may proceed.

executemany() method to insert multiple rows in MySQL Table – Python

Here we are going to use executemany instead of execute method to insert multiple rows at a single time in a single Python program.

Syntax of executemany()

.executemany(parameter1, parameter2)

The first parameter can take a MySQL statement and the second parameter can take data set.



data = [ ("Saruque","Male","Developer"),

           ("Mistry","Female","Blogger")

          ]
sql_statement = "INSERT INTO table_name (name,sex,role) values(%s,%s,%s)"
cursor.executemany(sql_statement,data)

This is the general code snippet to insert multiple rows into MySQL Database table in a single Python program.

Don’t forget to use commit() method to make changes to your database otherwise your inserted data will not be saved.

Python Program to insert more than one row into MySQL table

Let’s say we have a MySQL table named codespeedy having three columns.

category duration level

Now we want to insert multiple numbers of rows in MySQL table in a single Python program.

import mysql.connector
db_connection = mysql.connector.connect(
  host="localhost",
  user="username_of_mysql_server",
  passwd="password_here",
  database="your_database_name"
)
my_database = db_connection.cursor()
sql_statement = "INSERT INTO codespeedy (category,duration,level) values(%s,%s,%s)"
values = [ ("Java","80 Hours","Advanced"),
           ("Php","70 Hours","Basic")
          ]
my_database.executemany(sql_statement,values)
db_connection.commit()

Run this program. Output with no errors indicates that everything is alright.

You can check your table data. New rows are inserted successfully.

Here is my data for:

select * from codespeedy

insert multiple rows into mysql table using Python

Also, learn,

Leave a Reply

Your email address will not be published. Required fields are marked *