CODEWITHSUNDEEP
pythonmysqlsqlpython-3.xwhere-clause
Joshua
Joshua

Reputation: 75

MySQL connector/python difficulties using %s

I'm using MySQL connector - a Python driver for MySQL In an attempt to eliminate duplicate code within a function with two parameters: title, author. I'm trying to dynamically modify the query to fit User input. Although the query is legit, apparently the wild card %s doesn't work with it.

Can someone please point to a possible fix I'm missing, or an alternative solution that doesn't require creating 4 different queries for all four possible variations: Both are equal to "ALL", neither do, or XOR.

The simplified piece of code that I've tried implementing:

    if title == 'ALL':
        title = 'ANY (SELECT title FROM book)'
    if author == 'ALL':
        author = 'ANY (SELECT author FROM book)'
    cursor.execute("SELECT * FROM book where title = %s and author = %s",(title, author))

Thanks to anyone willing to help.

Upvotes: 1

Views: 79

Answers (1)

GMB
GMB

Reputation: 222402

You cannot pass a piece of SQL as a parameter. This mechanism is built to pass literal values only. I would recommend boolean logic:

sql = "select * from book where (%s = 'ALL' or title = %s) and (%s = 'ALL' or author = %s)"
cursor.execute(sql, (title, title, author, author))

Upvotes: 1

Related Questions