Python and psychopg2 - raw sql select from table with integer criteria

Question

It should be simple, bit I've spent the last hour searching for the answer. This is using psychopg2 on python 2.6.

I need something like this:

    special_id = 5
    sql = """
          select count(*) as ct,
            from some_table tbl
           where tbl.id = %(the_id)
      """
cursor = connection.cursor()
cursor.execute(sql, {"the_id" : special_id})

I cannot get this to work. Were special_id a string, I could replace %(the_id) with %(the_id)s and things work well. However, I want it ot use the integer so that it hits my indexes correctly.

There is a surprising lack of specific information on psychopg2 on the internet. I hope someone has an answer to this seemingly simple question.

Answer 1

Per PEP 249, since in psycopg2 paramstyle is pyformat, you need to use %(the_id)s even for non-strings -- trust it to do the right thing.

BTW, internet searches will work better if you use the correct spelling (no h there), but even if you mis-spelled, I'm surprised you didn't get a "did you mean" hint (I did when I deliberately tried!).