Client 3 - Query Processing

Client 3 ”Query Processing

To execute a SQL command with Python's DB-API, you must first create a cursor . Don't confuse this cursor with a cursor created by PostgreSQL's DECLARE CURSOR command; they have some similarities, but they are certainly not the same thing, as you will see in this section.

You create a cursor object by calling a connection's cursor() function ^[2] . For example, if you have a connection named connect , you would create a cursor like this:

^[2] It is possible, but extremely unlikely , that a call to connect.cursor() can throw a pgOperationalError exception. In fact, the only way that can happen is if somebody is messing around with the internals of a connection object; and we would never do that, would we?

 cur = connect.cursor() 

Notice that the cursor() function expects no arguments. You can create multiple cursor objects from the same connection; they operate independently, except that a commit() or rollback() executed on the connection will affect all cursor s open on that connection.

The next client application ( client3.py ) shows the steps required to create a cursor , execute a command, and print the results (see Listing 17.4).

Listing 17.4 client3.py ”main()

 1 #!/usr/bin/python  2 #  3 # File: client3.py  4  5 import pgdb  6 import string  7  8 ############################################################  9 def main( ): 10     try: 11         connection = pgdb.connect( database = "movies", 12                                    user     = "bruce", 13                                    password = "cows" ) 14 15     except Exception, e: 16         print str( e ) 17         exit 18 19     cur = connection.cursor() 20 21     try: 22         cur.execute( "SELECT * FROM customers" ) 23         process_results( cur ) 24 25     except StandardError, e: 26       print str( e ) 27 28     cur.close() 29     connection.close() 30     exit 

Listing 17.4 shows the main() procedure from client3.py . You start by calling pgdb.connect() to establish a connection to the movies database. Lines 15 through 17 take care of any exceptions thrown by pgdb.connect() . You take a shortcut here by defining a single exception handler that can catch proper DB-API exceptions as well as the (apparently) erroneous exception thrown by the PostgreSQL interface.

At line 19, you create a new cursor object by calling connection.cursor() . It is very unlikely that this call to cursor() will fail, so we won't bother catching any exceptions. If cursor() does fail, Python will print a stack trace and an error message and abort your application.

Next, use the cursor.execute() function to execute a simple SELECT command. If something goes wrong with this command, execute() will throw an exception. The text of the error message will be encapsulated in the exception parameter ( specifically , e.args ). If the command completes without error, call the process_result() function (see Listing 17.5) to display the result set.

After you have finished with the cursor object, close it by calling cur.close() . This is not strictly required because Python closes this object for you during garbage collection, but it's usually a good idea.

You also close the connection object when you are done with it. Even though you can ignore the cursor.close() function, you should get into the habit of closing connection objects. In fact, before you call connection.close() , you should call connection.commit() . Why? Because the PostgreSQL DB-API interface does not run in " auto-commit " mode. When you first call pgdb.connect() to establish a connection, the connect() function silently executes a BEGIN command for you. That means that all commands that you execute belong to a single multistatement transaction until you either connection.commit() or connection.rollback() . If you fail to commit before you close a connection , any changes made in the most recent transaction are rolled back. Watch out for this ”it will bite you if you aren't careful.

Now, let's look at the process_results() function (see Listing 17.5). This function is responsible for formatting and displaying the result of the SELECT command. You don't actually do any of the grunt work in process_results() ; instead, you have factored the details into three helper functions.

Listing 17.5 client3.py ”process_results()

 32 ############################################################ 33 def process_results( cur ): 34 35     widths = [] 36     rows   = cur.fetchall() 37     cols   = cur.description 38 39     compute_widths( cur, widths, rows, cols ) 40     print_headers( cur, widths, cols ) 41     print_values( cur, widths, rows ) 

Start by defining an (empty) array that holds the display width for each column in the result set. You pass this array to your helper functions, so you define it here.

Next, use the cursor.fetchall() function to retrieve all rows from the result set. The cursor.fetchall() function returns a sequence of sequences ^[3] . Each member of this sequence represents a single row. So, to get to the second column in the third row, you would use the following:

^[3] If you're not familiar with Python, think of a "sequence of sequences" as "an array of arrays" or maybe as a "list of lists." They are not completely analogous, but close enough to understand that fetchall() returns a collection of collections.

 print rows[2][1] # sequence indexes start at 0, not 1 

Besides cursor.fetchall() , there are two other functions that return all or part of a result set. The cursor.fetchone() function fetches the next row in a result set. fetchone() returns a sequence or returns None if you have exhausted the result set. The cursor.fetchmany( [ size = n ] ) function returns the next n rows in the result set. If you omit the size parameter, fetchmany() will assume that n =5 . If there are fewer than n rows remaining in the result set, fetchmany() will return all remaining rows. If the result set has been exhausted, fetchmany() will return None . Like fetchall() , fetchmany() returns a sequence of one or more sequences.

Notice that there is no way to go backward in the result set. You can't refetch a row after you have gone past it, nor can you "rewind" the result set to the beginning. If you need to move around in the result set, use fetchall() or declare a PostgreSQL cursor (not a DB-API cursor) and execute the FETCH commands yourself.

After you have retrieved all the rows in the result set, nab the column metadata from cursor.description . Notice that cursor.description is a public data member, not a function. cursor.description is a list of seven-element lists. Table 17.2 shows the meaning of each sublist.

Table 17.2. cursor.description Metadata Values

Element	Meaning
	Column name
1	Data type
2	Maximum display size
3	Server size (in bytes)
4	Precision (not used)
5	Scale (not used)
6	Null allowed? (not used)

Currently, the PyGreSQL DB-API implementation does not use the last three elements in the table (precision, scale, and null allowed?); they are always set to None . The data type member does not conform to the DB-API specification, but it's probably more useful that way. Data types are reported by their PostgreSQL names ( char , oid , float4 , and so on). The display size and server size elements are set to “1 for any variable- sized columns .

We will be using the column names a little later, so we store them in the local variable cols .

Now that you have access to the data (rows) and the metadata (cols), call each of your helper functions in the right order. compute_widths() computes the width of each column name, storing the result in the widths array (see Listing 17.6). Next, print_headers() prints column headings. Finally, print_values() prints the entire result set.

Listing 17.6 client3.py ”compute_widths()

 43 ############################################################ 44 def compute_widths( cur, widths, rows, cols ): 45 46     c = 0 47 48     for col in cols: 49         widths.append( len( col[0] )) 50         c = c + 1 51 52     r = 0 53 54     for row in rows: 55         c = 0 56 57         for col in row: 58             if( len( str( col )) > widths[c] ): 59                 widths[c] = len( str( col )) 60             c = c + 1 61         r = r + 1 

The compute_widths() function computes the width of each column in the result set.

Start by walking through the list of column names and appending the length of each name to the widths[] array. Remember, the caller ( process_results() ) gave you a complete metadata array in the cols parameter. Element 0 of each metadata list is the column name.

Next, you have to find the widest value in each column of the result set. The caller gave you a list of all the rows in the result set in the rows parameter. As you process each column in each row of the result set, you increase the corresponding element in the widths[] array to its maximum required width.

Notice (in lines 58 and 59) that you convert each data value into string form before you call the len() function. The result set can contain integer values, string values, float values, and so on. You can't invoke the len() function on a numeric value so convert them into string form first.

You can view the actual Python data types using the type() function:

 >>> cur.execute( "SELECT * FROM pg_class" ) >>> c = 0 >>> for col in cur.fetchone(): ...   print cur.description[c][0], '\t', col, '\t', type(col) ...   c = c+1 ... relname         pg_type <type 'string'> reltype         71L     <type 'long int'> relowner        1       <type 'int'> relam           0L      <type 'long int'> relfilenode     1247L   <type 'long int'> relpages        2       <type 'int'> reltuples       143.0   <type 'float'> reltoastrelid   0L      <type 'long int'> reltoastidxid   0L      <type 'long int'> relhasindex     1       <type 'int'> relisshared     0       <type 'int'> relkind         r       <type 'string'> relnatts        17      <type 'int'> relchecks       0       <type 'int'> reltriggers     0       <type 'int'> relukeys        0       <type 'int'> relfkeys        0       <type 'int'> relrefs         0       <type 'int'> relhasoids      1       <type 'int'> relhaspkey      0       <type 'int'> relhasrules     0       <type 'int'> relhassubclass  0       <type 'int'> relacl          None    <type 'None'> 

Listing 17.7 shows the print_headers() function.

Listing 17.7 client3.py ”print_headers()

 63 ############################################################ 64 def print_headers( cur, widths, cols ): 65 66     c = 0; 67 68     for col in cols: 69         print string.center( col[0], widths[c] ), 70         c = c + 1 71     print 72 73     c = 0; 74 75     for col in cur.description: 76         print '-' * widths[c], 77         c = c + 1 78     print 

print_headers() centers each column name within the width calculated by compute_widths() . You may have noticed that you have a dangling comma at the end of line 69 (and again at the end of line 76). Those aren't typos ”a dangling comma suppresses the new-line character that print would otherwise emit. You want all the column names to appear on the same line, so suppress all new-lines until you get to line 71 (or 78 in the case of the second loop).

Following the column names, print a line of separator characters (hyphens). When you apply the multiply operator ( * ) to a string, as in line 76, the result is a string of repeated characters. You create the separator strings my "multiplying" a dash by the width of each column.

Listing 17.8 shows the remaining code in client3.py . The print_values() function loops through each row and column in the result set ( rows ). At line 89, convert each value to string form, left-justify it within the proper column, and print it.

Listing 17.8 client3.py ”print_values() and mainline

 80 ############################################################ 81 def print_values( cur, widths, rows ): 82 83     r = 0 84 85     for row in rows: 86         c = 0 87 88         for col in row: 89             print string.ljust( str(col), widths[c] ), 90             c = c + 1 91         r = r + 1 92         print 93 94 95 ############################################################ 96 97 main() 

The mainline code (that is, the entry point for your client application) is at line 97 ”just call the main() function and exit when main() returns.

Now, run this application:

 $ chmod a+x client3.py $ ./client3.py id    customer_name      phone   birth_date -- -------------------- -------- ---------- 1  Jones, Henry         555-1212 1970-10-10 2  Rubin, William       555-2211 1972-07-10 3  Panky, Henry         555-1221 1968-01-21 4  Wonderland, Alice N. 555-1122 1969-03-05 7  Grumby, Jonas        None     1984-02-21 

At this point, you know how to connect to a PostgreSQL server from Python, how to intercept errors, and how to process SELECT commands. In the next section, we'll develop an interactive command processor using Python and the Tkinter GUI module.