I have a query similar to below:
def connection():
pcon = pg8000.connect(host='host', port=1234, user='user', password='password', database = 'database')
return pcon, pcon.cursor()
pcon, pcur = connection()
query = """ SELECT * FROM db WHERE (db.foo LIKE 'string-%' OR db.foo LIKE 'bar-%')"""
db = pd.read_sql_query(query, pcon)
However when I try to run the code I get:
DatabaseError: '%'' not supported in a quoted string within the query string
I have tried escaping the symbol with \ and an additional % with no luck. How can I get pg8000 to treat this as a wildcard properly?
"In Python, %
usually refers to a variable that follows the string. If you want a literal percent sign, then you need to double it. %%
"
-- Source
LIKE 'string-%%'
Otherwise, if that doesn't work, PostgreSQL also supports underscores for pattern matching.
'abc' LIKE 'abc' true
'abc' LIKE 'a%' true
'abc' LIKE '_b_' true
But, as mentioned in the comments,
An underscore (_) in
pattern
stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters
According to the source code, though, it would appear the problem is the single quote following the %
in your LIKE
statement.
if next_c == "%":
in_param_escape = True
else:
raise InterfaceError(
"'%" + next_c + "' not supported in a quoted "
"string within the query string")
So if next_c == "'"
instead of next_c == "%"
, then you would get your error
'%'' not supported in a quoted string within the query string