Client 1 - Connecting to the Server

To interact with a PostgreSQL server using Python's DB-API, you must first import the pgdb module. This module defines a few exception classes (we'll talk about exceptions a little later), two classes (pgdbCnx and pgdbCursor), and a single module function.

The pgdb.connect() function returns a connection object (an instance of class pgdbCnx). This function actually comes in two flavors:

pgdb.connect( dsn )

pgdb.connect( dsn      = dsn,

              user     = user,

              password = password,

              host     = host,

              database = dbname )

In the first flavor, the dsn is expected to be a string of the form:

host:database:user:password:opt:tty

The rules for composing a valid dsn are a bit complex. In the simplest case, you can specify all connection properties in the order shown:

"davinci:movies:bruce:cows:-fi:/dev/tty"

You can omit leading properties, but you must include the proper number of delimiters (that is, colons):

"::bruce:cows:-fi:/dev/tty"         # omit host and database

You can omit properties in the middle of the dsn, but again, you must include the proper number of colons:

"davinci:movies:::-fi:/dev/tty"     # omit user and password

You can omit trailing properties, in which case the extra delimiters are optional:

"davinci:movies:bruce::: "          # omit password, opt, and tty

"davinci:movies:bruce"              # ditto

In the second flavor, you should pass each parameter using Python's named parameter mechanism. For example:

pgdb.connect( host='davinci', user='bruce' )

pgdb.connect( host='davinci:5432', user='bruce' )

pgdb.connect( user     = 'bruce',

              password = 'cows',

              host     = 'davinci',

              database = 'movies' )

The order in which the parameters appear is unimportant when you use named parameters. Also notice, in the second example, that you can include a port number in the host parameter?just separate the hostname and port number with a colon.

You can also combine the first and second forms:

pgdb.connect( dsn = "davinci:movies", user='bruce', password='cows' )

In this case, we have used the dsn to specify the hostname and database, and named parameters to specify the username and password. If you have duplicate properties, the named parameters take precedence over the properties specified in the dsn, for example:

pgdb.connect( dsn      = "davinci:movies:sheila",

              user     = "bruce",

              password = "cows" )

In this case, we specified a username (sheila) in the dsn, but we have also supplied a username (bruce) with the user named parameter; we will connect as user bruce.

The PostgreSQL implementation of the DB-API eventually ends up using the libpq library (PostgreSQL's C language API) to do all the low-level communications work. If you've read some of the previous chapters, you might be thinking that you can use environment variables (such as PGDATABASE) to supply default values for connection properties (refer to Table 8.2 for a description of the connection-related environment variables). You may be able to, but for only three of the connection properties: PGHOST, PGPORT, and PGUSER. An apparent bug in Python prevents you from using PGOPTIONS, PGTTY, PGDATABASE, and PGPASSWORD. This problem may be fixed in newer versions of Python, so be sure to test the feature if you need it.

After you have successfully connected, pgdb.connect() returns a connection object. We'll look at some of the things that you can do with a connection object a bit later. For now, let's develop a simple client that establishes a connection to a PostgreSQL server.

Listing 17.1 shows the file client1.py. The first line tells the operating system which interpreter to use to run this script. If your copy of Python is stored in a different location, you should adjust this line to reflect the correct directory. If you are new to Python, you may be surprised to find that there are no block delimiters (curly braces or BEGIN/END pairs) to mark the boundaries of complex statements. Python uses indentation to indicate block boundaries.

Listing 17.1 client1.py

 1 #!/usr/bin/python

 2 #

 3 # Filename: client1.py

 4

 5 import pgdb

 6

 7 connection = pgdb.connect( database = "movies",

 8                            user     = "bruce",

 9                            password = "cows" )

10

11 print connection

At line 5, you import the pgdb module. When you import a module, all the classes and functions in that module become available for you to use. Next, at lines 7, 8, and 9 you use the pgdb.connect() function to establish a connection to the movies database. Finally, you print the connection object returned by pgdb.connect().

Let's run this client application to see what a connection object looks like:

$ chmod a+x client1.py

$ ./client1.py

<pgdb.pgdbCnx instance at 810dd98>

$

The single line of output really doesn't tell you anything useful other than your program did something. Now, shut down the postmaster and run client1.py again so you can see how an error is reported:

$ pg_ctl stop

waiting for postmaster to shut down......done

$ ./client1.py

Traceback (innermost last):

  File "./client1.py", line 9, in ?

    password = "cows" )

  File "/usr/lib/python1.5/site-packages/pgdb.py", line 376, in connect

    user = dbuser, passwd = dbpasswd)

    pg.error: could not connect to server: No such file or directory

        Is the server running locally and accepting

        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Don't you just love being assaulted by error messages like this? If you're a programmer, you probably appreciate the level of detail and a complete context, but our users tend to get upset when they see smoke and flames. Let's clean this up.