RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Accessing PostgreSQL with Python and Psycopg2 : Page 3

The combination of Python and PostgreSQL is potent, particularly when you use the Psycopg2 driver.

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:

   # load the adapter
   import psycopg2
   # load the psycopg extras module
   import psycopg2.extras
   # Try to connect
       conn=psycopg2.connect("dbname='foo' user='dbuser' password='mypass'")
       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)
       cur.execute("""SELECT * from bar""")
       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:

       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.

Joshua D. Drake is President of Command Prompt, Inc. a dedicated PostgreSQL support and custom programming company. He is also the co-author of "Practical PostgreSQL" from O'Reilly and Associates.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date