Psycopg2 using wildcard causes TypeError

问题:

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 ;)

回答1:

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 

I think %% is the correct escaping, but it could be \%

回答2:

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