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?

Glasgo

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

Vareck

_ is very different from %, they can't be used interchangeably. – Sheley

@VincentSavard - Updated – Vareck

Using %% in replace of % I get back a query that is completely empty even though I can see that it should be bringing back some results. – Glasgo

Well, at least it doesn't throw an error. So you're saying directly querying that table (outside of python) with the mentioned LIKE statement yields correct results? – Vareck

For me it does. – Bowes

Answer 2

With a recent version of pg8000 you shouldn't have any problems with a % in a LIKE. For example:

>>> import pg8000.dbapi
>>>
>>> con = pg8000.dbapi.connect(user="postgres", password="cpsnow")
>>> cur = con.cursor()
>>> cur.execute("CREATE TEMPORARY TABLE book (id SERIAL, title TEXT)")
>>> for title in ("Ender's Game", "The Magus"):
...     cur.execute("INSERT INTO book (title) VALUES (%s)", [title])
>>>
>>> cur.execute("SELECT * from book WHERE title LIKE 'The %'")
>>> cur.fetchall()
([2, 'The Magus'],)

Gastroenteritis