Escape SQL "LIKE" value for Postgres with psycopg2

Question

Does psycopg2 have a function to speed up the value of the LIKE operand for Postgres?

For example, I might need to match the lines starting with the line “20% of all,” so I want to write something like this:

sql = '... WHERE ... LIKE %(myvalue)s' cursor.fetchall(sql, { 'myvalue': escape_sql_like('20% of all') + '%' } 

Is there an existing escape_sql_like function that I could hook here?

(A similar question is explicitly quoting a string value (Python DB API / Psycopg2) , but I could not find the answer is.)

Answer 1

Yes, this is a real mess. Both MySQL and PostgreSQL use a backslash by default for this. This is a terrible pain if you again avoid the string using backslashes instead of using parameterization, and are also incorrect according to ANSI SQL: 1992, which says that by default there are no extra escape characters on top of the normal string escaping and therefore there is no way to enable literal % or _ .

I would suggest that a simple backslash method is also wrong if you disable backslash screens (which themselves do not match ANSI SQL) using NO_BACKSLASH_ESCAPE sql_mode in MySQL or standard_conforming_strings conf in PostgreSQL (which PostgreSQL developers are threatening to do for several versions now) .

The only real solution is to use the little-known LIKE...ESCAPE syntax to specify an explicit escape character for the LIKE -pattern. This is used instead of the backslash in MySQL and PostgreSQL, which makes them compatible with what everyone else does, and provides a guaranteed way to include out-of-band characters. For example, with the = sign as escape:

 # look for term anywhere within title term= term.replace('=', '==').replace('%', '=%').replace('_', '=_') sql= "SELECT * FROM things WHERE description LIKE %(like)s ESCAPE '='" cursor.execute(sql, dict(like= '%'+term+'%')) 

This works with PostgreSQL, MySQL, and ANSI SQL databases (modulo, which, of course, changes on different db modules).

There may still be a problem with MS SQL Server / Sybase, which apparently also allows [az] -character groups of characters in LIKE expressions. In this case, you also want to avoid the symbol [ c .replace('[', '=[') . However, according to ANSI SQL, escaping characters that do not require escaping is not valid! (Argh!) Therefore, although it will probably still work through real DBMSs, you still will not be ANSI-compatible. Sigh...

Answer 2

You can also look at this problem from a different angle. What do you want? You need a query that performs LIKE for any string argument, adding "%" to the argument. A good way to express this without resorting to psycopg2 functions and extensions is to:

 sql = "... WHERE ... LIKE %(myvalue)s||'%'" cursor.execute(sql, { 'myvalue': '20% of all'}) 

Answer 3

Instead of avoiding the percent sign, you can use the PostgreSQL regex implementation instead.

For example, the following query to system directories will provide a list of active queries that are not related to the auto-issue subsystem:

 SELECT procpid, current_query FROM pg_stat_activity WHERE (CURRENT_TIMESTAMP - query_start) >= '%s minute'::interval AND current_query !~ '^autovacuum' ORDER BY (CURRENT_TIMESTAMP - query_start) DESC; 

Since this query syntax does not use the "LIKE" keyword, you can do what you want ... and not get any trouble with python and psycopg2.

Answer 4

I wonder if all this is really necessary. I am using psycopg2 and just can use:

 data_dict['like'] = psycopg2.Binary('%'+ match_string +'%') cursor.execute("SELECT * FROM some_table WHERE description ILIKE %(like)s;", data_dict) 

Answer 5

Unable to find the built-in function written by me is pretty simple:

 def escape_sql_like(s): return s.replace('\\', '\\\\').replace('%', '\\%').replace('_', '\\_') 

Answer 6

You can subclass Like str and register an adapter for it to convert it to the correct syntax (for example, using the escape_sql_like() you wrote).

Answer 7

I made some changes in the above code to do the following:

 def escape_sql_like(SQL): return SQL.replace("'%", 'PERCENTLEFT').replace("%'", 'PERCENTRIGHT') def reescape_sql_like(SQL): return SQL.replace('PERCENTLEFT', "'%").replace('PERCENTRIGHT', "%'") SQL = "SELECT blah LIKE '%OUCH%' FROM blah_tbl ... " SQL = escape_sql_like(SQL) tmpData = (LastDate,) SQL = cur.mogrify(SQL, tmpData) SQL = reescape_sql_like(SQL) cur.execute(SQL)