Postgres query with '%%' parameter not returning results via psycopg2

Question

When I execute the below query in a query editor like DBeaver - it returns a result but if I execute the same query via Python & psycopg2 it does not return a result. '%%' should match any title/location so there will always return something. I'm just testing this for a category without keywords but it will also take an array of keywords if they exist depending on the category. So the array could be ['%%'] or ['%boston%', '%cambridge%'] and both should work.

select title, link
from internal.jobs 
where (title ilike any(array['%%'])
or location ilike any(array['%%']))
order by "publishDate" desc
limit 1;

I've tried adding the E flag at the beginning of the string. E.g. E'%%'

Python:

import psycopg2

FILTERS = {
    'AllJobs': [],
    'BostonJobs': ['boston', 'cambridge'],
    'MachineLearningJobs': ['ml', 'machine learning']
}

conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()

sql = """
select title, link
from internal.jobs 
where (title ilike any(array[%s])
or location ilike any(array[%s]))
order by "publishDate" desc
limit 1;
"""

for title, tags in FILTERS.items():
    if not tags:
        formatted_filters = "'%%'" # Will match any record
    else:
        formatted_filters = ','.join([f"'%{keyword}%'" for keyword in tags])

    cur.execute(sql, (formatted_filters))
    results = cur.fetchone()
    print(results)

You've got single quotes, double quotes, and percent signs so I highly suspect your issue is with a character that needs escaping but without seeing the Python code there's no way to know. Here's more info. — Alex W
– Alex W, Commented Jan 30, 2020 at 2:04
Thanks @AlexW I suspect you're correct and someone may point me in the right direction for escaping % signs / building an array this way. — Jonathan Porter
– Jonathan Porter, Commented Jan 30, 2020 at 3:05

piro · Accepted Answer · 2020-02-01 13:28:37Z

1

You can use the cur.mogrify() query to look at the SQL finally generated, check in psql if it works, and how you need to tweak it.

Most likely you have to double every %.

answered Feb 1, 2020 at 13:28

piro

14k6 gold badges39 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonathan Porter · Accepted Answer · 2020-02-01 19:21:09Z

Thanks to Piro for the very useful cur.mogrify() clue. That helped me further debug the query to figure out what was going wrong.

I ended up removing the extra set of quotes, I used a named parameter and now it works as expected.

Updated code:

import psycopg2

FILTERS = {
    'AllJobs': [],
    'BostonJobs': ['boston', 'cambridge'],
    'MachineLearningJobs': ['ml', 'machine learning']
}

conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()

sql = """
select title, link
from internal.jobs 
where (title ilike any(array[%(filter)s])
or location ilike any(array[%(filter)s]))
order by "publishDate" desc
limit 1;
"""

for title, tags in FILTERS.items():
    if not tags:
        formatted_filters = '%%' # Will match any record
    else:
        formatted_filters = [f'%{keyword}%' for keyword in tags]

    print(cur.mogrify(sql, {'filter': formatted_filters}))
    cur.execute(sql, {'filter': formatted_filters})
    results = cur.fetchone()
    print(results)

Collectives™ on Stack Overflow

Postgres query with '%%' parameter not returning results via psycopg2

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related