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
– Mr Lukas, Commented Feb 17, 2019 at 13:14
executemany do exactly what you need. Can you show your python code? — Alex Yu
– Alex Yu, Commented Feb 17, 2019 at 13:16
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
– Mr Lukas, Commented Feb 17, 2019 at 13:19
Take your time and read documentation. If you still have problems - prepare an MVCE illustrating your problem — Alex Yu
– Alex Yu, Commented Feb 17, 2019 at 13:23

Alex Yu · Accepted Answer · 2019-02-17 14:02:18Z

1

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

answered Feb 17, 2019 at 14:02

Alex Yu

3,5771 gold badge27 silver badges42 bronze badges

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

7 Comments

Mr Lukas Over a year ago

Ok works fine for strings but is it possible to pass to VALUES list?

Alex Yu Over a year ago

Look at prints in my code - the first example is passing list ["'Johh'", "'New-York'"]

Alex Yu Over a year ago

And second example is tuple of ints

Mr Lukas Over a year ago

Oh I am sorry I have used split on list (which ofc does not work). Man you are great! Thank you for your help

Alex Yu Over a year ago

Code generation is one task that I do frequently in python. But again: Beware! Here be dragons!

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related