Insert multiple rows at once with Python and MySQL

John D K -

Multiple SQL inserts at once with Python can be done in several different ways depending on your data input and expected output.

You can check also:

Multiple SQL insert with PyMySQL

The first option which can to mind is to use PyMySQL and cursor.execute reading list of tuples:

import pymysql

con = pymysql.connect(host="localhost", user="myuser", passwd="mypass", db="test")
cursor = con.cursor()

values_to_insert = [(1, 2, 'a'), (3, 4, 'b'), (5, 6, 'c')]
query = "INSERT INTO tab (col1, col2, col3) VALUES " + ",".join("(%s, %s, %s)" for _ in values_to_insert)
flattened_values = [item for sublist in values_to_insert for item in sublist]
cursor.execute(query, flattened_values)

con.commit()

In this example we are connecting to the MySQL with the settings shown in the code above. After that we build insert from list of tuples. The tricky code is:

flattened_values = [item for sublist in values_to_insert for item in sublist]
cursor.execute(query, flattened_values)

which will generate values for the insert. By using list comprehensions we are building insert values:

  • flattened_values - <class 'list'>: [1, 2, 'a', 3, 4, 'b', 5, 6, 'c']
  • query - 'INSERT INTO tab (col1, col2, col3) VALUES (%s, %s, %s),(%s, %s, %s),(%s, %s, %s)'

Multiple SQL insert from map with pandas

Another option for inserting data from dictionary using pandas and python is shown below:

import pandas as pd
import pymysql

con = pymysql.connect(host="localhost", user="myuser", passwd="mypass", db="test")
cursor = con.cursor()

df = pd.DataFrame(mydict)
val_to_insert = df.values.tolist()

cursor.executemany("insert into tab (col1, col2, col3) values (%s, %s, %s)",
                   val_to_insert)

con.commit()
con.close()

in this example we are using pandas dataframe values to list in order to product insert values.

Generating SQL inserts from csv data

If you need to insert multiple rows at once with Python and MySQL you can use pandas in order to solve this problem in few lines. Let say that your input data is in CSV file and you expect output as SQL insert.

INSERT INTO tab (`col1`, `col2`, `col3`)
VALUES (1,2,1), (1,2,2), (1,2,3);

Let say also that you want to insert 5 rows at once reading your csv file. This is a sample csv data:

col1,col2,col3
1,2,1
1,2,2
1,2,3

Below you can find the code which is going to read the csv data, split the data per 5 and build SQL inserts:

import pandas as pd

df = pd.read_csv("/home/user/Downloads/1.csv", sep=',')
cols = str(df.columns.values.tolist()).replace('[', '').replace(']', '').replace('\'', '`')

for i in range(1, df.shape[0], 5):
    values = str(df.iloc[i:i+5].values.tolist()).replace('[', '(').replace(']', ')').replace('((', '(').replace('))', ')')
    print("insert into %s ( %s ) values  %s ;" % ("tab", cols, values))

And now some explanation of the code:

df = pd.read_csv("/home/user/Downloads/1.csv", sep=',')

will read the csv file with path: "/home/user/Downloads/1.csv" and separator: ',' - taking into account the headers of the CSV file. Finally the data will be loaded as pandas dataframe.

cols = str(df.columns.values.tolist()).replace('[', '').replace(']', '').replace('\'', '`')

the last piece of code is going to read the headers from the CSV/dataframe format which will be used for the insert columns. There is also cosmetic formating to create SQL like syntax.

for i in range(1, df.shape[0], 5):
    values = str(df.iloc[i:i+5].values.tolist()).replace('[', '(').replace(']', ')').replace('((', '(').replace('))', ')')

Now it's time to read rows from the dataframe per blocks of 5. We are using iloc to locate the blocks. You can change this value to something which best suits your needs. And again some formating in order to satisfy MySQL syntax is done.

The final step is printing the results on the console. Another option is to save this information in a SQL file. Possible solution for python 3 would be:

with open(filename, 'a') as out:
    out.write(sql_insert + '\n')