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.

• Shapi: this is a solution to a similar problem already asked, hope it helps stackoverflow.com/questions/21612933/…
• MyGGaN: 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'.

Answer 1

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.

• ahmed: You are welcome, take a look at the note, i edited my answer.
• six7zero9: i meant to update yesterday: cursor.executemany('''INSERT IGNORE INTO %s (col1, col2, col3) VALUES (%%s, %%s, %%s)''' % table_name, (array))

Answer 2

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.")