Python: ValueError: unsupported format character ''' (0x27) at index 1

user_1558746 0
Viewed 71020

I'm trying to execute a query to search 3 tables in a database using MySQL through Python. Every time I try and execute the following string as a query, it gives me an error about concatenation in the string.

"SELECT fileid FROM files WHERE description LIKE '%" + search + "%' OR filename LIKE '%" + search + "%' OR uploader LIKE '%" + search + "%' ORDER BY fileid DESC"

This is the error it gives me:

ValueError: unsupported format character ''' (0x27) at index 1

If I remove the character it asks for then I have to also remove the %, which stops the query from actually working properly. What can I do to fix this, since I'm rather new to Python.

Thanks, Kris

mysql-python mysql python
5 Answers

Just for you info: I tried the solution of @Pochi today, in Python 3.6, and for some reason it provoked not expected behaviour. I had two, and three arguments for format string, so at the end was:

% (Search, Search)

My string ("search") began with an upper "S". I got the error message: 

ValueError: unsupported format character 'S' (0x53) at index 113

I changed uppercase to lowercase, and the error was: 

TypeError: not enough arguments for format string

Then I just put my arguments inside of double %% at the beginning and the end, and it worked. So my code looked like: 

"SELECT fileid 
FROM files 
WHERE description LIKE '%%search%%' 
    OR filename LIKE '%%search%%'
    ORDER BY fileid DESC"

Another solution would be the one provided by @Alice Yuan. She just doubled the percentage sings, and it works.

user_66997350

The simplest answer is to add the LIKE wildcard character % to the value. This correctly quotes and escapes the LIKE pattern.

In Python 3.6+ you can use an f-string to include the LIKE wildcard character % in the value which correctly inserts the escaped string value into the SQL:

# string to find, e.g.,
search = 'find-me'

# Parameterised SQL template
sql = """SELECT fileid FROM files
WHERE description LIKE %s OR filename LIKE %s OR uploader LIKE %s
ORDER BY fileid DESC"""

# Combine LIKE wildcard with search value
like_val = f'%{search}%'

# Run query with correctly quoted and escaped LIKE pattern
cursor.execute(sql, (like_val, like_val, like_val))

python3 mysql-python

user_5081850
Related