Client 4 - An Interactive Command Processor

Client 4 ”An Interactive Command Processor

The next client is an interactive command processor. The basic Python language distribution does not include any tools for building graphical applications. Instead, you can add GUI toolkits to Python based on your needs. If you don't need graphics, you won't have to weigh down your application with extra code. If you do need graphics in your application, you can choose the toolkit best suited to your requirements.

We'll use the Tkinter toolkit for our command processor. If you read the previous chapter, you know that Tk is a portable toolkit originally designed for the Tcl language. Tkinter is a Python wrapper around the Tk graphics toolkit. Using Tkinter, you can create and manipulate Tk widgets ( buttons , windows , scrollbars, and so on) from Python applications.

The application that you will build should look like Figure 17.1 when you are finished. When you run this program, you can enter an arbitrary PostgreSQL command in the text entry widget, press Return, and then view the results in the table widget below. You'll also place a status line in the middle of the window so you can show error messages and row counts.

Figure 17.1. The client4.py application.

This application ( client4.py ) is a bit larger than the other Python clients you have seen so far (see Listing 17.9). Start by importing the pgdb module (as usual) and two Tk- related modules: Tkinter and Tktable . Tkinter is the basic Tk GUI toolkit. Tktable is an extension to Tkinter that adds a table widget. The source code for Tktable is a little hard to find on the Web, but you will find it with the sample code for this chapter at http://www.conjectrix.com/pgbook.

Listing 17.9 client4.py ”PGDialog.init()

 1 #!/usr/bin/python  2 #  3 # File: client4.py  4  5 import pgdb  6 from Tkinter import *  7 from Tktable import Table,ArrayVar  8  9 class PGDialog: 10 ############################################# 11     def __init__( self ): 12         self.widths  = [] 13         self.conn    = None 14         # Widgets 15         self.table   = None 16         self.command = None 17         self.status  = None 

At line 9, you declare the PGDialog class. You use PGDialog as a single container for all the variables that you would otherwise need to pass between member functions.

It may not be obvious because of the formatting requirements of this book, but all the functions defined in client4.py are members of the PGDialog class.

The __init__() function is called whenever you create an instance of a PGDialog object. C++ and Java programmers will recognize __init__() as a constructor. Inside of this constructor, you initialize all member variables to a known state.

You use the self.widths[] member variable to hold the computed width for each column. self.widths[] is filled by the set_column_headers() function, modified by fill_table() , and used by size_columns() .

The self.conn variable holds the connection object that we create in main() .

self.table , self.command , and self.status are widgets that you need to manipulate. All widgets are created in the build_dialog() function.

Listing 17.10 shows PGDialog.main() . This function is called when you want to display the dialog (refer to Figure 17.1) to the user .

Listing 17.10 client4.py ” PGDialog.main()

 19 ############################################# 20     def main( self ): 21 22         self.conn = pgdb.connect( database="movies" ) 23 24         self.build_dialog( ) 25         self.table.mainloop( ) 

At line 22, call pgdb.connect() to establish a connection to the PostgreSQL server. Notice that you won't catch any exceptions thrown by pgdb.connect() ”if this call fails, your application can't do anything useful, so you just let Python print an error message and end. If you want to embed the PGDialog class in a larger application, you'll want to add some error checking here.

Assuming that pgdb.connect() returned successfully, you call the build_dialog() function to create all required widgets. Next, call Tk's mainloop() function. mainloop() displays the dialog and waits for user interaction. mainloop() does not return until the user closes the dialog window.

Listing 17.11 shows PGDialog.build_dialog().

Listing 17.11 client4.py ” PGDialog.build_dialog()

 28 ############################################# 29     def build_dialog( self ): 30 31         root = Tk() 32 33         self.make_table( root ) 34 35         self.command = Text( root, height=3 ) 36         self.status  = Label( root ) 37 38         close = Button( root, 39                         text="Close Window", 40                         command=root.destroy ) 41 42         label = Label( root, 43                        text="Enter an SQL Command and Press Return") 44 45         self.command.focus_force( ) 46 47         self.command.bind( "<Return>", self.execute ) 48 49         sy = Scrollbar( root, 50                         command=self.table.yview ) 51 52         sx = Scrollbar( root, 53                         command=self.table.xview, 54                         orient="horizontal" ) 55 56         self.table.config( xscrollcommand=sx.set, 57                            yscrollcommand=sy.set ) 58 59         label.grid( row=0 ) 60 61         self.command.grid( row=1, sticky='news' ) 62         self.status.grid( row=2 ) 63         self.table.grid( row=3, column=0, sticky='news' ) 64 65         sy.grid( row=3, column=1, sticky='news' ) 66         sx.grid( row=4, sticky='ew' ) 67         close.grid( row=5 ) 68 69         root.columnconfigure( 0, weight=1 ) 70 71         root.rowconfigure( 1, weight=0 ) 72         root.rowconfigure( 3, weight=1 ) 73         root.rowconfigure( 5, weight=0 ) 

The build_dialog() function is responsible for creating and arranging the widgets in your dialog. At line 31, you construct a Tk object named root . You will use root as the parent window for all the widgets that you create.

Next, call the make_table() member function (see Listing 17.12) to create a Tktable widget. You won't know how many rows and columns you will need in the table until you execute a SELECT command, but you can configure everything else now.

Lines 35 through 42 create a few more child widgets that you will display on the dialog. self.command is a text entry widget that holds the command text entered by the user. self.status is a simple Label widget ”you will display error messages and row counts in this widget (if you refer to Figure 17.1, self.status is the part that says " 5(rows) ").

The close widget is a Button that displays the text " Close Window ". When the user clicks on this button, Tk will execute the command root.destroy , closing the application.

You create the label widget to display a prompt (" Enter an SQL Command and Press Return ") to the user.

At line 45, you move the keyboard focus to the command (text entry) widget. That way, the cursor is positioned in the right place when this application starts running.

Next, bind a chunk of Python code to the Return key. When the command widget has the keyboard focus and the user presses Return , you call the self.execute() function (refer to Listing 17.5). The self.execute() function grabs any text that the user typed into the command widget and sends it to the PostgreSQL server.

The next few lines of code (lines 49 through 57) create a vertical scrollbar ( sy ) and horizontal scrollbar ( sx ) and connect them to the self.table widget. The Tktable widget won't automatically display scrollbars, so you have to wire them in manually.

Lines 59 through 67 arrange all the widgets using Tk's grid layout manager. Refer to Figure 17.1. We lay out the child widgets in a grid of unevenly sized cells . The label widget appears at the top of your dialog, so place it in row 0 (because you have only a single widget in row 0, the column is irrelevant). Next, place the command (text entry) widget in the second row ( row=1 ). The third row (row=2) contains the status widget. The fourth row actually contains two widgets: the table widget on the left ( column=0 ) and the sy vertical scrollbar on the right ( column=1 ). The horizontal scrollbar ( sx ) and close button are placed in the last two rows.

The " sticky " stuff is taking care of widget placement within each grid cell . If you don't specify any sticky options, each widget is centered (vertically and horizontally) within its own cell. sticky=news means that you want the grid layout manager to stick a widget to the n orth (top), e ast (right), w est (left), and s outh (bottom) side of its cell.

The final four lines in this function tell the layout manager how to stretch or compress the widgets whenever the user resizes the root window. You want the table widget (which is positioned in column 0) to resize, but the vertical scrollbar to remain the same; so you give column 0 a resize weight of 1. You also want the command widget (row 1) and the close button to stay the same size , so you give those rows a weight of 0.

Give yourself a quick break ”the next few functions are mercifully short.

Listing 17.12 client4.py ” PGDialog.make_table()

 75 ############################################# 76     def make_table( self, parent ): 77 78         var = ArrayVar( parent ) 79 80         self.table = Table( parent, 81                             variable=var, 82                             titlerows=1, 83                             titlecols=1, 84                             roworigin=-1, 85                             colorigin=-1, 86                             colstretchmode='last', 87                             rowstretchmode='last' ) 

The make_table() function creates a Table widget and does some preliminary configuration work. A Table widget requires a variable that it can use to hold the actual data values that you stuff into the table. Fortunately, the Tktable.py module (remember, you imported that module at the beginning of this application) defines a data type custom-made to work with a Tktable . At line 78, you create an instance of Tktable.ArrayVar() .

Next, create the table widget and configure a few options. First, tell the table to use var as its data variable. Next, you arrange to reserve the top row for column headers and the leftmost column for row numbering. Normally, the first row in a table is row 0; likewise, the first column is usually column 0. For convenience, we will change the table origin to “1, “-1 . That way, the title row is row “1 and the first data row is row 0. We pull a similar trick with the column-numbering scheme.

You also set the column stretch mode and row stretch mode. colstretchmode and rowstretchmode determine how the table will behave when you resize it. A value of 'last' resizes the last row (or column) to fill extra space.

The execute() function is called whenever the table widget holds the focus and the user presses the Return key (see Listing 17.13). You arranged for this behavior back at line 47 (refer to Listing 17.11).

Listing 17.13 client4.py ” PGDialog.execute()

 89 ############################################# 90     def execute( self, event ): 91 92         self.process_command( self.command.get( "1.0", "end" )) 

This function is simple ”you first retrieve the contents of the command widget and then call self.process_command() with that text. If you have trouble seeing the flow in this function, you could have written it as follows :

 ... text = self.command.get( "1.0", "end" ) self.process_command( text ) ... 

The process_command() function (see Listing 17.14) is where things start to get interesting again. This function is called whenever the user wants to execute a command. Start by creating a new cursor object (remember, a DB-API cursor is not the same as a PostgreSQL cursor).

Listing 17.14 client4.py ” PGDialog.process_command()

 94 #############################################  95     def process_command ( self, command ):  96  97         cur = self.conn.cursor()  98  99         try: 100             cur.execute( command ) 101             self.load_table( cur ) 102 103         except Exception, e: 104             from mx.TextTools import collapse 105             self.status.configure( text=collapse( str( e ))) 106 

Next, call the cursor.execute() function to execute the command provided by the caller. If the command completes without error, call the load_table() function (refer to Listing 17.15) to display the results. If anything goes wrong, cursor.execute() will throw an exception. You catch any exceptions at line 103. You want to display error messages in the status widget, which is only one line high. So, use the mx.TextTools.collapse() function to remove any new-line characters from the text of the error message before copying the message into the status widget.

A Few More Ways to Execute PostgreSQL Commands

So far, all the examples in this chapter have used a simple form of the cursor.execute() method to execute PostgreSQL commands. When you call cursor.execute() , you call it with a complete command.

You can also call cursor.execute() with a parameterized command and collection of parameter values. A parameterized command contains placeholders (also known as parameter markers) in which you can substitute values. For example, assume that you have a dictionary that holds two values, one named min and one named max :

 ... >>> min_max = { 'min':2, 'max':4 } ... 

You can execute a command such as ^[4]

 ... >>> cmd="SELECT * FROM customers WHERE id >= %(min)d AND id <= %(  max)d" >>> >>> cur.execute( cmd % min_max ) >>> cur.fetchall() [   [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'] ] ... 

In this example, the SELECT command includes two placeholders: %(min)d and %(max)d . Python replaces the first placeholder with the min value from dictionary min_max and the second placeholder with the max value. In effect, you are executing the following command:

 SELECT * FROM customers WHERE id >= 2 AND id <= 4 

You can also refer to other variables by name in a parameterized command:

 ... >>>  min = 2 >>>  max = 4 >>>  cmd="SELECT * FROM customers WHERE id >= %(min)d AND id <= %  (max)d" >>>  cur.execute( cmd % vars()) >>>  cur.fetchall() [   [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'] ] ... 

I don't want to give you the impression that parameterized commands are a feature unique to the Python/PostgreSQL interface. In fact, we are simply using Python's string formatting operator. You still have to be sure that the end result (that is, the result after formatting) is a valid SQL command ”you must quote strings properly, and you can't simply bind None where you really want NULL to appear.

Note that finding documentation for Python's string formatting operator is notoriously difficult. You can find this information in the Python Library Reference Manual : Built-in Functions, Types, and Exceptions; Built-in Types; Sequence Types; String Formatting Operations.

Besides cursor.execute() , you can use the cursor.executemany() function to execute PostgreSQL commands. The executemany() function executes a command repeatedly, substituting new parameter values with each iteration. For example, let's create a list of tuple values that we want to INSERT into the tapes table:

 >>> vals = \ ... [ ...   ( 'TH-X1138', 'This Island Earth' ), ...   ( 'MST-3000', 'Python' ), ...   ( 'B-MOVIE1', 'Frogs' ), ...   ( 'B-MOVIE2', 'Bats' ) ... ] 

Now we can INSERT all four tuples with a single command:

 >>> cmd = "INSERT INTO tapes VALUES( %s, %s )" >>> cur.executemany( cmd, vals ) 

You can use cursor.execute() and cursor.executemany() to simplify your code. Using these functions, you can factor the code that executes a command and the code that produces parameter values into two separate functions.

^[4] The results returned by cur.fetchall() have been reformatted for clarity.

The function in Listing 17.15, load_table() ,loads the result of a command into the status widget and the table widget. Start by setting the status widget: We query cur.rowcount to find the number of rows and format this value into a nice, polite message.

Listing 17.15 client4.py ” PGDialog.load_table()

 108 ############################################# 109     def load_table( self, cur ): 110 111         self.status.configure( text= "%d row(s)" % cur.rowcount ) 112 113         self.size_table( cur ) 114 115         if( cur.description == None ): 116             return 117 118         self.set_column_headers( cur ) 119 120         self.fill_table( cur ) 121 122         self.size_columns( cur ) 

Next, call the size_table() function (see Listing 17.16) to configure the table widget to the proper number of rows and columns.

At line 115, decide whether you are processing a SELECT command or some other type of command. A SELECT command is the only type of command that will return column metadata ( cur.description ). If you don't have metadata, you are finished. Otherwise, copy the column headers into the table (see Listing 17.17), copy the data values into the table (Listing 17.18), and size each column to match the data (Listing 17.19).

Listing 17.16 client4.py ” PGDialog.size_table()

 124 ############################################# 125     def size_table( self, cur ): 126 127         if( cur.description == None ): 128             self.table.configure( rows=0, cols=0 ) 129         else: 130             col_cnt = len( cur.description ) 131             row_cnt = cur.rowcount 132 133             self.table.configure( rows=row_cnt+1, cols=col_cnt+1 ) 

The size_table() function configures the table widget to hold the proper number of rows and columns. If you have no metadata, size the table to hold 0 rows and 0 columns (metadata is returned for only a SELECT command).

If you have metadata, you can look into the cursor object to find the number of rows and (indirectly) the number columns in the result set. Finding the row count is easy ”each cursor object contains a data member named rowcount . Finding the column count is a bit more complex ”you have to count the number of sequences in the metadata list.

After you know how many rows and columns are present in the result set, configure self.table to hold one extra row (for the column headers) and one extra column (for row numbers ).

Listing 17.17 client4.py ” PGDialog.set_column_headers()

 135 ############################################# 136     def set_column_headers( self, cur ): 137 138         col_no = 0 139 140         for col in cur.description: 141             self.table.set( "-1," + str(col_no), col[0] ) 142             self.widths.append(len( col[0] )) 143             col_no = col_no + 1 144 

The set_column_headers() function tackles two different problems. First, it copies the name of each column in the result set into the first row of self.table . Second, it initializes the self.widths[] array to hold the width of each column header.

The cur.description data member is a list of tuples ”each tuple corresponds to one column in the result set. The first member of each tuple contains the column name. Refer to Table 17.2 for more information on the contents of cur.description .

Listing 17.18 client4.py ”PGDialog.fill_table()

 146 ############################################# 147     def fill_table( self, cur ): 148 149         rows = cur.fetchall() 150 151         r = 0 152         for row in rows: 153             c = 0 154 155             for col in row: 156 157                 self.table.set( str(r) + "," + str(c), str( col )) 158 159                 if( col != None ): 160                     if( len( str( col )) > self.widths[c] ): 161                         self.widths[c] = len( str( col )) 162 163                 c = c + 1 164 165             self.table.set( str(r) + ",-1", str(r)) 166 167             r = r + 1 

Listing 17.18 shows the PGDialog.fill_table() function. This function looks complicated, but it's actually very simple. You have a pair of nested loops : The outer loop iterates through each row in the result set and the inner loop iterates through each column in the current row.

In the inner loop, you convert each data value into string form and copy it into the proper cell in the table widget (line 157). You also use the length of each value to update the self.widths[] array. You'll use the widths[] array to set each column in the table to the proper width. You want each column to be wide enough to display the widest value in the column, so you have to measure each value as you encounter it.

After you have finished processing the data values in each row, copy the row number into the leftmost column of self.table .

Listing 17.19 client4.py ” PGDialog.size_columns()

 169 ############################################# 170     def size_columns( self, cur ): 171         col_cnt = len( cur.description ) 172 173         for col in range( 0, col_cnt ): 174             self.table.width( col, self.widths[col] ) 

size_columns() is the last function in client4.py . This function is responsible for configuring each column in self.table to the proper width. You computed the optimal width of each column in the fill_table() and set_column_headers() functions.

Listing 17.20 shows the mainline code for client4.py . These are the first executable commands outside of PGDialog , so execution begins at line 178. Getting this program up and running is pretty easy; you create an instance of the PgDialog class and then invoke that object's main() function (refer to Listingv 17.9).

Listing 17.20 client4.py ” mainline code

 176 ############################################# 177 178 obj = PGDialog() 179 obj.main( )