-6

According to this answer, the correct way to build a Parameterized SQL Statement that will be safe is by using a tuple to string format the query like this:

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))

However, in my case the values are stored in an array where the length can vary. I'm trying to create the Parameterized SQL Statement like below, but I have a syntax error that I don't understand:

values = ["a", "b", "c", "d"]
cursor.execute("INSERT INTO table VALUES ({})".format(",".join(u"%s", (v,) for v in values)))

>>>SyntaxError: Generator expression must be parenthesized if not sole argument

Expected result:

"INSERT INTO table VALUES (%s, %s, %s, %s)"

What would be the correct way to achieve this?

Below the Radar
  • 7,321
  • 11
  • 63
  • 142
  • Are these values each going to become their own row or getting filled into an unknown number of columns? – scnerd May 15 '18 at 13:33

3 Answers3

1

The syntax error is because of how you call ",".join(...). It should look like the following:

cursor.execute("INSERT INTO table VALUES ({})".format(",".join("%s" for v in values)), values)
scnerd
  • 5,836
  • 2
  • 21
  • 36
1

I'm not sure what you expect this to do:

",".join(u"%s", (v,) for v in values)

but the error message gives you some clue on what you're doing wrong:

SyntaxError: Generator expression must be parenthesized if not sole argument

the "Generator expression" part here being (v,) for v in values.

What you want is obviously something like:

values = ["a", "b", "c", "d"]
placeholders = ", ".join("%s" for _ in values) 
sql = "INSERT INTO table VALUES ({})".format(placeholders)
cursor.execute(sql, values)
bruno desthuilliers
  • 75,974
  • 6
  • 88
  • 118
0

You can try using a temp placeholders

Ex:

values = ["a", "b", "c", "d"]
placeholders = ', '.join('?' * len(values))
cursor.execute("INSERT INTO table VALUES ({0})".format(placeholders), tuple(values))
Rakesh
  • 81,458
  • 17
  • 76
  • 113