"unsupported format character at index "-CodePudding

I'm trying to format a PostgreSQL query in python and that query has to have '%' between the name of a survey so I can filter surveys by name.

Here is the code:

sql = """select survey_data
from survey_data.survey_data 
where codigo_do_projeto like '%s%'
ORDER BY data_de_inicio_da_coleta desc 
limit %s
offset %s"""

However it throws this error:

"unsupported format character 'P' (0x50) at index 79"

I don't know how to make python ignore the "%" character.

CodePudding user response：

You have to escape the %.


sql = """select survey_data
from survey_data.survey_data 
where codigo_do_projeto like '%%'||%s||'%%'
ORDER BY data_de_inicio_da_coleta desc 
limit %s
offset %s"""

Or you can do:


search_val = '%search_term%'


sql = """select survey_data
from survey_data.survey_data 
where codigo_do_projeto like %s
ORDER BY data_de_inicio_da_coleta desc 
limit %s
offset %s"""

cur.execute(sql, [search_val, val2, val3])

CodePudding user response：

You need to put the survey_name part inside single quotes:

sql = """SELECT survey_data
FROM survey_data.survey_data 
WHERE project_code like '%{0}%'
ORDER BY starting_date desc 
LIMIT {1} 
OFFSET {2}*{1}""".format(survey_name,items_per_page,page_number)