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.exe开发者_Python百科cute( sql1 ) #why exception: IndexError: tuple index out of range ?
cursor.execute( sql2 ) #works ok

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.

Using direct string manipulation will almost certainly lead to improper SQL that is vulnerable to SQL Injection attacks (see psycopg2's comments on the subject).

What I think you're looking to do is try and perform a LIKE '%some value%' in django, right?:

from django.db import connection
q = '%some value%'
cur = connection.cursor()
cur.execute("SELECT * FROM table WHERE field LIKE %(my_like)s", {'my_like': q})

As of psycopg2 2.4.1, the SQL that is executed on the server is:

SELECT * FROM table WHERE field LIKE '%some value%'

You need to QUOTE properly your SQL command:

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

And by quoting properly I mean using single quotes with LIKE expressions.