How insert multiple values (list) to database via mysql python

Question

I use mysql.connector to connect to database in Python. I can create query that insert values into database e.g

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" val = ("John", "Highway 21") mycursor.execute(sql, val) mydb.commit()

but I wonder how can I do the same with list (list is dynamic so sometimes it looks like this: [val1, val2, val3] and other time can looks like this [val1, val2, val3, val4, ... val55]. I have prepared query:

INSERT INTO tablename ("+', '.join(dataset.columns.tolist())+") VALUES (%s, %s)" but the question is how to deal with parameter %s in VALUES because sometimes as I said it can be 4 and other time 55. My only idea so far is to create string in loop and add as many %s parameters as need.

Yes but I still have to pass parameter %s sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" mycursor.executemany(sql, val) — Mr Lukas
executemany do exactly what you need. Can you show your python code? — Alex Yu
I got problem with %s parameter in VALUES. If I am correct this parameter tells how many arguments you will pass into insert statement. The problem is I do not know exactly how many I will pass. Sometimes It can be 4 sometimes 55 so I can not write query with VALUES(%s, %s) because It tells that I will pass only two arguments (I need to be flexible and I do not know how can I achieve that Here is my code sql = "INSERT INTO tableone("+', '.join(data.columns.tolist())+") VALUES (%s, %s, %s)" mycursor.executemany(sql, data.values.tolist()) — Mr Lukas
Take your time and read documentation. If you still have problems - prepare an MVCE illustrating your problem — Alex Yu

Alex Yu Alex Yu · Accepted Answer · 2019-02-17T14:02:18

As your SQL-query needs to be truly dynamic (one step further and you step into area of ORMs and SQL-toolkits) I suppose you need something like

def gen_sql(fields, values):
     return f'INSERT INTO customers  ({",".join(fields)}) VALUES ({",".join(map(str,values))})'

print(gen_sql('name address'.split(), "'Johh' 'New-York'".split()))
print(gen_sql('a b c '.split(), (1,2,3)))

But "Here be dragons!":

Quote-escaping is on responsibility of caller
no prevention of sql-injection

How insert multiple values (list) to database via mysql python

1 Answers