Using % wildcard with pg8000

Question

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?

Answer 1

"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