Can't get python tuple as input in WHERE IN SQL clause?

Question

Python Code:

query is a UPDATE query, and the error simply states there is some problem with the tuple,which is passed as an argument to the IN Clause

pyList is Python List, DBOBJ is connection object

pyTuple = tuple(pyList)
print(pyTuple)

pyTupleSQLFormat = DBOBJ.string_literal(pyTuple)
print(pyTupleSQLFormat)

query = "UPDATE seats SET isReserved = 1 WHERE screen_name='{}' AND seat_number IN %s".format(screenname)
args = (pyTupleSQLFormat,)
CurOBJ.execute(query,args)

Console Output: Python

('B1', 'B2', 'A6', 'A7')
b"'(\\'B1\\', \\'B2\\', \\'A6\\', \\'A7\\')'"
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''\\'(\\\\\\'B1\\\\\\', \\\\\\'B2\\\\\\', \\\\\\'A6\\\\\\', \\\\\\'A7\\\\\\')\\''' at line 1")

score 1 · Accepted Answer · answered Oct 15 '18 at 07:20

string_literal(...) appears to be preparing the object for insertion into a char/text field, not stringifying it in a way that could be appended to a query.

You can pass the tuple contents in as additional parameters for your query by dynamically building parameter placeholders. This has the additional advantage of using parameterization to avoid SQL injection and related problems.

screen_name = "example"
seat_numbers = [1, 2, 3, 4]

args = tuple([screen_name] + seat_numbers)

query = """
UPDATE 
    seats 
SET 
    isReserved = 1 
WHERE 
    screen_name=%s AND 
    seat_number IN ({placeholders})
""".format(
    placeholders=",".join(["%s"] * len(seat_numbers)),
)
print(query)
print(args)

cursor.execute(query, args)

Works like a charm. Thanks @kungphu – Jalaz Kumar Oct 15 '18 at 10:06 — Jalaz Kumar, Oct 15 '18 at 10:06

Can't get python tuple as input in WHERE IN SQL clause?

1 Answers1