Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Accessing PostgreSQL with Python and Psycopg2 : Page 3

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


advertisement
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.



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.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap