Accessing PostgreSQL with Python and Psycopg2 : Page 3

Using Named Columns
Some programmers would prefer to not use numeric column representations such as row[1][1]; instead, it can be easier to use a dictionary. Here's the same example with a slight modification that uses the column name rather than the column index number:

   #/usr/bin/python2.4
   #
   #
   
   # load the adapter
   import psycopg2
   
   # load the psycopg extras module
   import psycopg2.extras
   
   # Try to connect
   
   try:
       conn=psycopg2.connect("dbname='foo' user='dbuser' password='mypass'")
   except:
       print "I am unable to connect to the database."
   
   # If we are accessing the rows via column name instead of position we 
   # need to add the arguments to conn.cursor.
       
   cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
   try:
       cur.execute("""SELECT * from bar""")
   except:
       print "I can't SELECT from bar"
   
   #
   # Note that below we are accessing the row via the column name.
   
   rows = cur.fetchall()
   for row in rows:
       print "   ", row['notes'][1]

Notice that the preceding code did not use row[1] but instead used row['notes'], which signifies the notes column within the bar table.

The output from that is identical to the example that used the column number:

   
   Rows:
   
       Another array of text

Finally, I'd like to show you how simple it is to insert multiple rows using a dictionary. Suppose you had a dictionary containing the following:

   namedict = ({"first_name":"Joshua", "last_name":"Drake"},
               {"first_name":"Steven", "last_name":"Foo"},
               {"first_name":"David", "last_name":"Bar"})

You could easily insert all three rows within the dictionary by using:

   cur = conn.cursor()
   cur.executemany("""INSERT INTO bar(first_name,last_name) VALUES 
      (%(first_name)s, %(last_name)s)""", namedict)

The cur.executemany statement automatically iterates through the dictionary and executes the INSERT query for each row.

The only downside to using Psycopg2 and PostgreSQL is that the driver is a little behind in terms of server-side support functions such as prepared queries, but the author of Psycopg2 expects to implement those features in the near future.

Previous Page