Search code examples
djangopostgresqlpsycopg

psycopg2 strange behavior

from django.db import connection

q = 'some value'

sql1 = 'SELECT * FROM table WHERE field LIKE %%%s%%' % q
sql2 = 'SELECT * FROM table WHERE field LIKE %%'+ q +'%%'

cursor = connection.cursor()
cursor.execute( sql1 ) #why exception: IndexError: tuple index out of range ?
cursor.execute( sql2 ) #works ok
Solution

  • You need to QUOTE properly your SQL arguments.

    And by quoting properly I mean using the quote facility provided by DBAPI, not adding a ' around your string, which is useless.

    Correct code :

    q = "%"+q+"%"
cursor.execute( 'SELECT * FROM table WHERE field LIKE %s', (q,) )

    Really correct code :

    q = "%"+q.replace("%","%%")+"%"
cursor.execute( 'SELECT * FROM table WHERE field LIKE %s', (q,) )

    Suppose q = "a'bc" First, rewrite this as "%a'bc%" Then use it as a normal string argument. psycopg will rewrite it as '%a\'bc%' as it should.

    If q may contain "%" and you want to search for it, then use the second one.