Query works in psql but Syntax error in Python with psycopg2

Question

I have a query which works well in psql, but generates an error in my program:

sql = 'SELECT id, x, y FROM table_name WHERE y NOT LIKE 'pa%' AND x IS NOT NULL;' 
cur.execute(sql)

Any pointers as to why it's not working? Or any suggestions, how I can fix it?

Answer 1

It is not about PostgreSQL vs MySQL query syntax - this is just syntactically wrong in Python:

>>> sql = 'SELECT id, x, y FROM table_name WHERE y NOT LIKE 'pa%' AND x IS NOT NULL;' 
 File "<stdin>", line 1
 sql = 'SELECT id, x, y FROM table_name WHERE y NOT LIKE 'pa%' AND x IS NOT NULL;' 
 ^
SyntaxError: invalid syntax

Put the query into double quotes:

sql = "SELECT id, x, y FROM table_name WHERE y NOT LIKE 'pa%' AND x IS NOT NULL;"

• marengz: I feel silly now, totally never thought of it. thanks.

Answer 2

The good practice for code strings in Python is the triple quoted string which makes it much clearer and can contain both single and double quotes:

sql = '''
 SELECT id, x, y
 FROM table_name
 WHERE y NOT LIKE 'pa%' AND x IS NOT NULL;
'''

From the manual:

Triple quoted strings may span multiple lines - all associated whitespace will be included in the string literal.