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")

kungphu · Accepted Answer · 2018-10-15 07:20:37Z

1

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)

answered Oct 15, 2018 at 7:20

kungphu

4,8613 gold badges30 silver badges38 bronze badges

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

1 Comment

Jalaz Kumar Over a year ago

Works like a charm. Thanks @kungphu

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related