Psycopg2 Using Wildcards Raises a TypeError

Question

I am currently trying to find a database to capture specific events. My request as such

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

Simply put, I need a request to view the calendar events database and return something with a resume with a “test” in it after the start of this month.

This returns the expected results when prompted from the database command line. However, when I try 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 an error like

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

Doing some kind of initial Googling is similar to how I use my wildcards. I could be wrong, although I probably missed something simple that I do not see. I hope a fresh pair of eyes from the community can fix my noobility;)

Answer 1

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

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

I think %% is proper escaping, but it could be \%

Answer 2

my hunch is that your "%" is confusing python. in psycopg2 I make my group “similar” requests 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 custom search strings, as in this script, you probably want to avoid any "%" in them, which, I suspect, would otherwise be legal patterns in the request, but I haven't received it yet