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


Accessing PostgreSQL with Python and Psycopg2 : Page 2

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

Support for PostgreSQL's Special Data Types
The Psycopg2 driver also has the built-in ability to deal with some of the special data types that PostgreSQL has available. One such example is arrays. In the following PostgreSQL table definition, the notes column in the bar table is of type text[]. Square brackets ([]) have a special meaning in PostgreSQL, meaning that the column type is not just text but an array of text values. Table 1 shows the structure of a public.bar sample table

Table 1. Public.bar Structure: The table shows the structure and indexes of the public.bar table.
Table public.bar
Column Type Modifiers
id bigint not null default nextval('public.bar_id_seq'::text)
notes text[]  
"bar_pkey" PRIMARY KEY, btree (id)

To insert values into this table you would use a statement like the following.

   foo=# insert into bar(notes) values 
      ('{An array of text, Another array of text}');
After running that statement, selecting all columns from the table would produce the following result.

   foo=# select * from bar;
    id  |                    notes
     2  | {"An array of text","Another array of text"}
   (1 row)
Note that the notes column contains an array of text strings. With many languages and database drivers you would need to create a routine to parse the above array output manually, but Psycopg2 automatically converts the array into a Python list, for example:

   import psycopg2
   # Try to connect
       conn=psycopg2.connect("dbname='foo' user='dbuser' 
       print "I am unable to connect to the database."
   cur = conn.cursor()
       cur.execute("""SELECT * from bar""")
       print "I can't SELECT from bar"
   rows = cur.fetchall()
   print "\nRows: \n"
   for row in rows:
       print "   ", row[1]
If you execute the preceding script you'd see the following output:

   [jd@jd ~]$ python test.py
       ['An array of text', 'Another array of text']
Again, you see the full embedded list; however, because Psycopg2 and Python give you direct access to the array of text strings, if you change the last few lines, you can access individual items in the array with code similar to the following:
   rows = cur.fetchall()
   for row in rows:
       print "   ", row[1][1]
The above loop outputs the first text string in each array of strings, for example.

       Another array of text

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