Insert dynamic defined values in mysql via python

I am using the following python code to insert 4 entries in the MySQL table.database

for i,row in empdata.iterrows():
  sql = "INSERT INTO products VALUES (%s,%s,%s,%s)"
  cursor.execute(sql, tuple(row))

If I have 20 columns and I don’t want to write 20 times %s after VALUES. and in another case 30 values.
is there another way to write it out?

>Solution :

You can use the string Python multiplier operator to optimize this task, being n in this case the number of elements you need to include in the string:

n = 20
sql = "INSERT INTO products VALUES (" + ("%s, "*n)[:-2] + ")"

print(sql)

Output:

INSERT INTO products VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)

Leave a Reply