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

python valueerror unsupported format character ''' (0x27)
psycopg2 unsupported format character
valueerror: unsupported format character 'y
valueerror: unsupported format character '='
unsupported format character ',' (0x2c)
valueerror: unsupported format character
unsupported format character python
valueerror unsupported format character d 0x44

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

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)

Python: ValueError: unsupported format character ''' (0x27) at index , It looks like python is interpreting the % as a printf-like format character. Try using %%? "SELECT fileid FROM files WHERE description LIKE  ValueError: unsupported format character while forming strings 1309 UnicodeEncodeError: 'ascii' codec can't encode character u'\xa0' in position 20: ordinal not in range(128)

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.

Unsupported format character - Python queries, A format string, that is the string on the left hand side of an expression like fmt % arguments , must consist of legal conversion specifiers. Otherwise, a ValueError  ValueError: unsupported format character 'p' (0x70) at index 7. Ask Question Asked 1 year, 7 months ago. With Python 3.6+, you can use f-strings :

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)

ValueError: unsupported format character ')' (0x29) at index 22 , ValueError: unsupported format character ')' (0x29) at index 22 #177 python-​dateutil in /opt/anaconda2/lib/python2.7/site-packages (from  Sponsor getsentry/raven-python Watch 168 Star 1.7k Fork 808 Code. Issues 208 ValueError: unsupported format character ''' (0x27) at index 9651

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!

Why doesn't my code work? What is wrong?, Red Traceback (most recent call last): File "codecademy", line 6, in <module> ValueError: unsupported format character 'n' (0x6e) at index 21 Oops, try again. >>> '%b' % 257 Traceback (most recent call last): File "<stdin>", line 1, in <module> ValueError: unsupported format character 'b' (0x62) at index 1 However, the modulo operator does allow decimal integer conversion with any of the d , i , and u types.

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

unsupported format character (#513) · Issues · PyCQA / flake8, on invalid percent markers; ValueError: unsupported format character python str_fmt.py Traceback (most recent call last): File "str_fmt.py",  ValueError: unsupported format character 't' (0x74) at index 1 although, obviously I can't do this, since python thinks that the '%t' is a format string.

T143217 archivebot.py exception: ValueError: unsupported format , ERROR: ValueError: unsupported format character '|' (0x7c) at index 43 Traceback (most recent call python scripts/archivebot.py 'Usuario:MABot/config'​  (Jul-27-2017, 05:27 PM) snippsat Wrote: When we taking about new string formatting .format() has been available for a while(9-year). it's still new to me. even though i have been doing python for about 6 years, doing it for others for about 4 years, hassling you guys with stupid questions on it for about 2 years, i am still in "learning mode" with python, especially stuff i didn't see in

Issue 8057: ValueError: unsupported format character '"' (0x22) at , Title, ValueError: unsupported format character '"' (0x22) at index 30 [2] https://​docs.weblate.org/en/latest/user/checks.html#python-format. msg46629  Dismiss Join GitHub today. GitHub is home to over 40 million developers working together to host and review code, manage projects, and build software together.

1558593 – [UPDATES] ValueError: unsupported format character , (0x2c) at index 91. Summary: [UPDATES] ValueError: unsupported format character ',' (0x2c) at index 91 python-mistral-lib-0.3.3-1.el7ost.noarch 

Comments
  • 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?
  • Yes I'm using MySQLdb, and where can I find these substitution tools?
  • 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
  • Please check, if the answer below still works (for Python 3.5+). Didn't worked for me, it tried it different.
  • Thanks a lot, that did it! :)
  • 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
  • Can you explain why this answers the question?
  • 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...