CODEWITHSUNDEEP
pythonpostgresql
Chris Hall
Chris Hall

Reputation: 931

Correct Postgresql syntax

I'm a postgres newbie and am having some issues querying a text field in postgresql using Python. What is the correct syntax that will allow me to search the content of column "body" from table "jivemessage" out of database "postgres"?

try:
    conn = psycopg2.connect("dbname='postgres' user='postgres' host='localhost'  password='<password>'")

except:
    print "cannot connect"

i = 'test'
cur = conn.cursor()

cur.execute('SELECT * from jivemessage WHERE body LIKE "%'+i+'%"')

Keep getting the following error:

ProgrammingError: column "%test%" does not exist

Thanks for any help.

Upvotes: 0

Views: 92

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121864

You are not quoting the query properly. Don't use string concatenation here, use SQL parameters instead:

cur.execute('SELECT * from jivemessage WHERE body LIKE %s', ("%{}%".format(i),))

Here, the %s placeholder signals to the database driver that the first value of the second argument should be placed there when querying.

This leaves the interpolation up to the database driver, giving the database the opportunity to optimize for the query once, even if you were to reuse the same query.

It also prevents SQL injection attacks better than you could yourself, and most of all, guarantees that the correct quoting rules are followed.

Upvotes: 2

Related Questions