Login | Register   
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.




Application Security Testing: An Integral Part of DevOps

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.



Thanks for your registration, follow us on our social networks to keep up-to-date