psycopg2 is the de-facto Python module to interface with PostgreSQL database but it has a quirk you ought to beware of.
Parameterized query is the recommended way to modify your SQL statement programmatically where you could sprinkle your SQL statement with variable placeholders like %s
or %(myvar)s
. But, if the statement happens to use any ILIKE operator e.g. '%pattern%'
, you must escape every occurrence of %
in your operand with %%
, i.e. '%%pattern%%'
. Otherwise psycopg2 will throw the exception error message “argument formats can’t be mixed”.
This feature is actually mentioned in the documentation, http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries
When parameters are used, in order to include a literal
%
in the query you can use the%%
string