INSERT INTO a whole list with python and SQL

Question

I'm inserting data in a database with INSERT INTO

The main problem is that I'm inserting around 170k points of data and I'm looping a code like that:

for row in data:
   SQL='''INSERT INTO Table
       VALUES ({},{},{})
   '''.format(row[0],row[1],row[2])
   cur.execute(SQL)
cur.commit()
cur.close()
con.close()

This code is extremely slow, is there a faster way to do it?

I was thinking if there is a way to insert a whole column of my matrix data at once.

Check this: https://stackoverflow.com/questions/48006551/speeding-up-pandas-dataframe-to-sql-with-fast-executemany-of-pyodbc — Srinika Pinnaduwage, Jun 07 '20 at 15:30

apoorva kamath · Accepted Answer · 2020-06-07T15:49:47.053

2

Try this. Basically you can achieve it using executemany() method.

import mysql.connector

mydb = mysql.connector.connect(
  .....
)
mycursor = mydb.cursor()

val = []
for row in data:
   val.append((row[0],row[1],row[2]))

sql = "INSERT INTO table (x,y,z) VALUES (%s, %s, %s)"

mycursor.executemany(sql, val)

mydb.commit()

edited Jun 07 '20 at 15:49

answered Jun 07 '20 at 15:30

apoorva kamath

816
1
7
19

score 1 · Answer 2 · answered Jun 07 '20 at 15:31

Support may vary by DBMS (which you do not specify), but you can use a prepared statement, using your DBMS's paramstyle string in the VALUES clause, and pass a list of rows to the executemany() method. See the docs at https://www.python.org/dev/peps/pep-0249/#cursor-methods

INSERT INTO a whole list with python and SQL

2 Answers2