Python MySql Batch Insert

We can also insert multiple rows at once using the python script. The multiple rows are mentioned as the list of various tuples.

Each element of the list is treated as one particular row, whereas each element of the tuple is treated as one particular column value (attribute).

MySQL Batch insert example

CopiedCopy Code

import mysql.connector  
myconn = mysql.connector.connect(host = "localhost", user = "root",password = "naveen",database = "PythonDB")  
cur = myconn.cursor()  
sql = "insert into Employee(name, id, salary, dept_id, branch_name) values (--s, --s, --s, --s, --s)"  
val = [("John", 102, 25000.00, 201, "Newyork"),("David",103,25000.00,202,"Port of spain"),("Nick",104,90000.00,201,"Newyork")]  
try:  
    cur.executemany(sql,val)  
    myconn.commit()  
    print(cur.rowcount,"records inserted!")  
except:  
    myconn.rollback()  
myconn.close()