how to insert mutiple rows using mysql connector python code example
Example 1: python mysql insert multiple rows
import mysql.connector
from mysql.connector import Error
try:
connection = mysql.connector.connect(host='localhost',
database='Electronics',
user='pynative',
password='pynative@#29')
mySql_insert_query = """INSERT INTO Laptop (Id, Name, Price, Purchase_date)
VALUES (%s, %s, %s, %s) """
records_to_insert = [(4, 'HP Pavilion Power', 1999, '2019-01-11'),
(5, 'MSI WS75 9TL-496', 5799, '2019-02-27'),
(6, 'Microsoft Surface', 2330, '2019-07-23')]
cursor = connection.cursor()
cursor.executemany(mySql_insert_query, records_to_insert)
connection.commit()
print(cursor.rowcount, "Record inserted successfully into Laptop table")
except mysql.connector.Error as error:
print("Failed to insert record into MySQL table {}".format(error))
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
print("MySQL connection is closed")
Example 2: bulk insert mysql from list of tuples
def stringify(v):
return "('%s', '%s', %s, %s)" % (v[0], v[1], v[2], v[3])
v = map(stringify, row)
batchData = ", ".join(e for e in v)
sql = "INSERT INTO `table_name`(`column`, `column_1`, `column_2`, `column_3`) \
VALUES %s" % batchData
cursor.execute(sql)
db.commit()