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

WEBINAR:

On-Demand

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[]

Indexes

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

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:

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.