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

Question

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.

Use the built in string substitution tools for your SQL tool instead of trying to build the query string yourself with standard Python string mechanics. It works better that way. Are you using MySQLdb? — Silas Ray
– Silas Ray, Commented Jul 27, 2012 at 21:23
Yes I'm using MySQLdb, and where can I find these substitution tools? — user1558746
– user1558746, Commented Jul 27, 2012 at 21:45
The docs at mysql-python.sourceforge.net/… say, "Note that any literal percent signs in the query string passed to execute() must be escaped, i.e. %%." Are you using execute()? The substitution tools @Silas mentioned are shown at mysql-python.sourceforge.net/MySQLdb.html#some-examples — LarsH
– LarsH, Commented Jun 30, 2014 at 13:41
Please check, if the answer below still works (for Python 3.5+). Didn't worked for me, it tried it different. — vlad.rad
– vlad.rad, Commented Jun 8, 2018 at 13:03

Fabian N. · Accepted Answer · 2016-05-27 13:08:24Z

126

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)

edited May 27, 2016 at 13:08

Fabian N.

3,8682 gold badges27 silver badges47 bronze badges

answered Jul 27, 2012 at 21:54

Pochi

2,2041 gold badge17 silver badges11 bronze badges

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

2 Comments

Magentron Over a year ago

To be exact, MySQLdb uses the % operator to put parameters into the query, therefor any single % character in the query string is interpreted as the start of a argument specifier. To prevent this use %% for % in queries. Please see also the appropriate documentation: string formatting

Tim Richardson Over a year ago

this fix applies to postgresql too.

vlad.rad · Accepted Answer · 2018-06-08 12:42:44Z

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.

CK.Nguyen · Accepted Answer · 2017-05-19 10:35:05Z

3

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!

answered May 19, 2017 at 10:35

CK.Nguyen

1,38817 silver badges17 bronze badges

1 Comment

AuHau Over a year ago

Ehm, you know that the %%%s%%' % name part is completely unnecessary?... The parameter of the query is the %s which is replaced by the ORM or what ever DB abstraction you are using...

adejones · Accepted Answer · 2019-10-16 08:47:36Z

1

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

answered Oct 16, 2019 at 8:47

adejones

1,08011 silver badges11 bronze badges

Comments

Aurasphere · Accepted Answer · 2017-05-04 07:49:49Z

0

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)

edited May 4, 2017 at 7:49

Aurasphere

4,03912 gold badges53 silver badges80 bronze badges

answered May 4, 2017 at 7:27

Alice Yuan

1

1 Comment

IanS Over a year ago

Can you explain why this answers the question?

PaleNeutron · Accepted Answer · 2024-01-16 13:00:37Z

0

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

answered Jan 16, 2024 at 13:00

PaleNeutron

3,3056 gold badges32 silver badges51 bronze badges

Collectives™ on Stack Overflow

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

6 Answers 6

2 Comments

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related