Insert multiple rows in MySQL table in Python
By Saruque Ahamed MollickIn 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,
- install MySQL Connector in Python
- Connect MySQL with Python
- create a database in MySQL using Python
- create MySQL table in Python
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
Also, learn,
if we want to store the values dynamically by using python in mysql…???
Instead of inserting data directly (as shown in this tutorial), just take the values as user inputs then use those values.
You can learn more about how to take user input from here – How to take user defined input in Python
I want to fetch data row wise after some delay of 2 seconds.
like after the first row is displayed, 2 seconds of delay must be there and then the next row should appear. How to perform this task?