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?
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;"
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.
