Python Batch Insert in Database

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).

Python Batch Insert in Database Example

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()