CODEWITHSUNDEEP
pythonmysqlmysql-python
user1558746
user1558746

Reputation: 633

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

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.

Upvotes: 60

Views: 88101

Answers (6)

PaleNeutron
PaleNeutron

Reputation: 3215

I think the best solution in 2024 is sqlalchemy.text, this method keep sql string the same as it should be.

import sqlalchemy as sa
sqlstr = "SELECT fileid FROM files WHERE description LIKE '%" + search + "%' OR filename LIKE '%" + search + "%' OR uploader LIKE '%" + search + "%' ORDER BY fileid DESC"

conn = sa.create_engine("xxxxx")
conn.execute(sa.text(sqlstr))

Upvotes: 0

adejones
adejones

Reputation: 1050

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

Upvotes: 1

vlad.rad
vlad.rad

Reputation: 1083

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.

Upvotes: 4

CK.Nguyen
CK.Nguyen

Reputation: 1368

My solution:

query = """SELECT id, name FROM provice WHERE name LIKE %s"""
cursor.execute(query, '%%%s%%' % name)

I think it's easy way to fix this issue!

Upvotes: 3

Alice Yuan
Alice Yuan

Reputation: 1

you can try like this: 

SELECT fileid 
FROM files 
WHERE description LIKE '%%%%%s%%%%' 
OR filename LIKE '%%%%%s%%%%' 
OR uploader LIKE '%%%%%s%%%%' 
ORDER BY fileid DESC" % (search, search, search)

Upvotes: 0

Pochi
Pochi

Reputation: 2196

It looks like python is interpreting the % as a printf-like format character. Try using %%?

"SELECT fileid 
FROM files 
WHERE description LIKE '%%%s%%' 
    OR filename LIKE '%%%s%%' 
    OR uploader LIKE '%%%s%%' 
    ORDER BY fileid DESC" % (search, search, search)

Upvotes: 125

Related Questions