Blog / 2009 / December / 19

Bind Variables with Python and Postgres

December 19, 2009

When executing SQL against Postgres with python, the bind variable format is different from that used for Oracle.

lCursor = connection.cursor()
lCursor.execute("""SELECT event.date_of_event, 
                         event.name, 
                         contest.slug, 
                         contest.name,  
                         event.id, 
                         contest.id 
                   FROM contests_contest contest, contests_contestevent event 
                   WHERE event.owner_id = %(userid)s 
                   AND event.contest_id = contest.id 
                   AND event.date_of_event > %(startyear)s 
                   AND event.date_of_event < %(endyear)s 
                   ORDER BY event.date_of_event desc""",
     {'userid' : lUser.id, 'startyear' : lStartYear, 'endyear' : lEndYear})
lCursor.close()

Note the s after the bind variable name in the body of the sql - this is a normal format specifier like %s.

Processing Results

Results can be processed using fetchall(), and then iterating around the rows:

lRows = lCursor.fetchall()
lClasses = []
for row in lRows:
    lClasses.append(row[0])
lCursor.close()

row will be an array of the columns for the row being processed, here we're just referencing the first column.

Django

If you're working within django, then you can get the connection from 

from django.db import connection

Tags: python postgres bind variables sql