Inserting an array into mysql with Python

Question

I am building this array with 40k entries.

array = [(value1, value2, value3),(value1, value2, value3),(value1, value2, value3) .... ]

Is it possible to insert this into mysql in python something like:

cursor.execute('''INSERT IGNORE into %s VALUES *array here*''' % (table_name, array))

I am having trouble passing the array variable into mysql correctly. Any help appreciated.

this is a solution to a similar problem already asked, hope it helps stackoverflow.com/questions/21612933/… — Shapi
– Shapi, Commented Oct 1, 2015 at 19:23
It could be good to check the max size for your server as well. You could do that with SHOW VARIABLES LIKE 'max_allowed_packet'. — MyGGaN
– MyGGaN, Commented Oct 1, 2015 at 19:54

ahmed · Accepted Answer · 2015-10-01 19:50:46Z

7

Yes you can do it with executemany:

cursor.executemany('INSERT IGNORE into %s VALUES(%s, %s, %s)'%table_name, sql_data)

Note: you shouldn't use % to pass values to the database, instead you need to pass them in the second parameter of execute/executemany. i used % for the table name because the first parameter is the prepared query string.

edited Oct 1, 2015 at 19:50

answered Oct 1, 2015 at 19:44

ahmed

5,6001 gold badge23 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ahmed Over a year ago

You are welcome, take a look at the note, i edited my answer.

six7zero9 Over a year ago

i meant to update yesterday: cursor.executemany('''INSERT IGNORE INTO %s (col1, col2, col3) VALUES (%%s, %%s, %%s)''' % table_name, (array))

Thai Mozhi Kalvi · Accepted Answer · 2019-04-09 13:42:19Z

This is Use for me try it.

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="test"
)
mycursor = mydb.cursor()
print("Insert Process... Please Wait...")
for r in range(1,tdrows+1):
    a = []
    for c in range(1,tdcols+1):
        a.append(driver.find_element_by_xpath("//*[@id='DataTables_Table_0']/tbody/tr["+str(r)+"]/td["+str(c)+"]").text)
    sql = "INSERT IGNORE into test_table(id,fname,lname) VALUES (%s, %s, %s)"
    val = (a)
    mycursor.execute(sql,val)
    mydb.commit()
print(mycursor.rowcount, "Record Inserted.")

Collectives™ on Stack Overflow

Inserting an array into mysql with Python

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related