Psycopg2 using wildcard causes TypeError

7
votes

Currently I am attempting to search a database to grab certain events. My query is as such

SELECT * FROM events WHERE summary ILIKE E'%test%' AND start_time > '2010-10-01'

Simply put I need the query to look through a database of calendar events and return anything with a summary with 'test' in it and after the beginning of this month.

This returns the expected results when queried from the database command line. However when I attempt to use it in my Python script with psycopg2 as such:

cursor.execute("SELECT * FROM events WHERE summary ILIKE E'%test%' AND start_time > %(begin)s ", {'begin' : datetime.datetime(2010,10,1) })

I get a type error

*** TypeError: 'dict' object does not support indexing

Doing some initial Googling it sounds like something with the way I'm using my wildcards. I could be wrong though and I am probably missing something simple that I don't see. Hopefully a fresh pair of eyes from the community can correct my noobishness ;)

2
pythonpsycopg2

2 Answers

23
votes

Not sure if this is the full root of your problem, but I think you need to escape your wildcards or the parameterization logic will get confused.

SELECT * FROM events WHERE summary ILIKE E'%%test%%' AND start_time > %(begin)s

EDIT: 10+ years later, I'm looking at the psycopg2 issues for an unrelated bug and found that the developers recognize this as an issue. My comments below were correct. If you're passing arguments in your query, then you must escape the percent signs in your LIKE clause. If you're not passing arguments, then you don't escape your LIKE clause.

3
votes

my guess is something about your "%"'s is confusing python. in psycopg2 i do my wildcard "like" queries like this:


#!/usr/bin/python

import sys,os.path,psycopg2
db=psycopg2.connect("dbname=music")

for line in sys.argv[1::]:
    cursor=db.cursor()
    key="%"+line+"%"
    cursor.execute("select count(*) from pool where path like %s",(key,))
    if cursor.fetchone()[0] != 1:
        sys.stderr.write("ambiguous stem or no such song")
        sys.exit(-1)
    cursor.execute("insert into spool select path from pool where path like %s",(key,))
    cursor.close()
    db.commit()
db.close()

with user-supplied search strings like in this script you would probably want to escape out any "%"'s in them, which i suspect would otherwise be legit wildcards in the query, but i haven't gotten that far yet