INSERT INTO a whole list with python and SQL

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.

Answer

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