How to use ilike and wildcard pattern in python psycopg2?

I already referred this post and this post but it doesn’t help

I am trying to write the below code

import psycopg2
param_dic = {
    "host"      : "localhost",
    "database"  : "test",
    "user"      : "test",
    "password"  : "****"
}
conn = psycopg2.connect(**param_dic)
cursor = conn.cursor()
cursor.execute('select * from test where code in ("0091","0092","0FY00Z0","0FY00Z1","0FY00Z2","5051","5059")')

In the above cursor execute command, whether I use single quote or double quotes I get the error as shown below

ndefinedColumn Traceback (most recent call last) in —-> 1 cursor.execute(‘select * from test where code in (“0091″,”0092″,”0FY00Z0″,”0FY00Z1″,”0FY00Z2″,”5051″,”5059”)’)

UndefinedColumn: column “0091” does not exist LINE 1: …from test where code in (“0091”,”00…

When I change the quotes to single quote, I get the below error

cursor.execute(‘select * from test where code in (‘0091′,’0092′,’0FY00Z0′,’0FY00Z1′,’0FY00Z2′,’5051′,’5059′)’) ^ SyntaxError: invalid token

When I try to use ilike operator, I get the below error

cursor.execute ('select code from test where long_title ilike '%live%' and long_title ilike '%ransplan%'')

NameError: name ‘live’ is not defined

But all the above query works perfectly fine in my pgadmin postgresql editor. So there is no issue with the SQL in its native form but when I try to use them in python using cursor.execute, I am encountering issues.

Can you help me with this please?

Answer

Postgresql requires that string values are quoted with single quotes.

Python strings can be bounded with either single quotes ('...'), double-quotes ("...") or tripled single or double quotes ('''...''').

To keep Postgresql happy, use single quotes to quote the values in the query, and bound the query string with one of the other types.

cursor.execute("""select * from test where code in ('0091','0092','0FY00Z0','0FY00Z1','0FY00Z2','5051','5059')""")

cursor.execute ("""select code from test where long_title ilike '%live%' and long_title ilike '%ransplan%'""")

It’s common, but not obligatory, to use triple-quotes for SQL queries, as they will work when the query contains single-quoted values or double-quoted identifiers.