How to pass dynamic value in SQL LIKE?

Question

In the below code, I want to pass a dynamic value to SQL-like but receiving.

CODE

#Database

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="deikho_app"
)

cursor = mydb.cursor(buffered=True)

`videoCatId`=1024

    if videoCatId:
        query = """select * from cb_video where active = 'yes' and status = 'Successful' and category LIKE '%' + %s + '%'"""
        cursor.execute(query, (videoCatId,))

CODE EXPLANATION

First of all connection with the database is created.
Then a value is assigned to videoCatId for running if condition.
After this query is written and stored in the query variable.
And then the query is executed.
Finally, results are fetched from the database and stored in seriesResult variable.

ERROR

ERROR:dramas-api:Exception on /papis/get-video [GET]
Traceback (most recent call last):
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 2070, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 1515, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 1513, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 1499, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "E:\dramas-api.py", line 61, in getVideo
    seriesResult = cursor.fetchall()
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 985, in fetchall
    raise errors.InterfaceError("No result set to fetch from.")
mysql.connector.errors.InterfaceError: No result set to fetch from.

user_1422451 · Answer

To parameterize values in LIKE expressions, incorporate the wildcards in the binded value and not in SQL prepared statement which you can do with Python's F-strings:

query = """select * from cb_video 
           where active = 'yes' 
             and status = 'Successful' 
             and category LIKE %s
        """ 

cursor.execute(query, (f"%{videoCatId}%",))

How to pass dynamic value in SQL LIKE?

1 Answers