Here are a few a my Python programs. I have recently started studying this language. I am currently using the ActiveState distribution on Windows, the fedora 5 release on Linux and the Standard Mac Edition from Python.org. There might be a few Macintosh features in some of the programs. Enjoy!

   PyPgExplorer-0.6 Released June 15, 2006 is my initial attempt at a non trivial program. It is modeled after my Tcl/Tk program PgBrowse.

   PyPgExplorer-0.7 Released July 1, 2006

   PyPgExplorer-0.8 Released July 17, 2006

   PyPgExplorer-0.9 Released Sep 9, 2006

   PyPgExplorer-0.95 Released Sep 20, 2006

   Current Editor Documentation.

PyPgExplorer is a postgresql browser. The user can interact with a postgresql database ie query/modify the database by sending SQL commands to the server and viewing the results.

Documentation for the latest version PyPgExplorer-0.95

Requirements:

• psycopg2 release 2 or better.
• PIL if you wish to view images stored in the database.
• a recent version of Python (only tested with 2.4.2 or better).
• Tkinter and Tktable modules.

PyPgExplorer runs on my WinXP, Linux, and MacOS X systems.

Fill in the appropriate parameters and click the Connect button. If all goes well you will get the main window of the application or a dialog that lists the reason for the login failure.

As one can see the main window is divided into three parts:

1. The SQL window, this is where the user enters SQL commands.
2. The Status window, error messages and status messages appear here.
3. The Results table, output from selections is displayed here.

• Enter some semicolon delimited SQL into the SQL pane. Note single SQL statements do not require a semicolon
• Cause the SQL to be sent to the backend.
• Observe the status and results.

• The entire window will be sent if the usr types <ctl-r> ( command-r for macs).
• If there is a selection highlighted, a <shift-return> will send the selection to the backend.
• If there is no selection, then a <shift-return> will send the line containing the insertion point.

• The File Menu
  

  

  • New Browser will bring up another login dialog box and allow the user to work with another (or the same) database in a new main window. Additional windows will have a '*' prefixed to the window title. If such a window is closed the initial window will remain open.
  • Load SQL File... will clear the sql window and bring up an "open file" dialog. Whatever file the user chooses will be loaded into the SQL window.
  • Table Options... will bring up the following list box.

    The list box will contain all of the tables visable to the user.

    Clicking on the Edit Button will bring up a window that can be used to modify the contents of the table. Documentation about using the editor can be found here. Selecting a Table and then clicking the Describe button will generate a description of the table in the Results area of the main window. Note that if a table name is in two schemas that the results will be the descriptions of all of the columns of both of the tables. Clicking the Close button will close the window.

  • Send Single Statements The default action is to send all of the selected statements "at once" to the backend. If this item is checked I make a simple effort to split the text on semicolons and send each statement separately.
  • Show Long Fields The default action is to check the length of a field before it is displayed in the Results pane. If the length is greater than 200 characters the displayed value will look like "LoNgField<nnn> Later we will see than we can still "see" the entire field as it truely exists.
  • Process Window Send the entire window to the backend.
  • Process Selection Send the selection to the backend, if there is no selection send the line containing the insertion point to the backend.
  Send Window To Psql If running under Linux/MacOS X and psql is located in a "standard location" then the contents of the SQL window is sent to the backend via psql using a one shot subprocess. The results will be displayed in the SQL window. Note that due to the way libpq works, only the last statement output will be captured. (This is true for ordinary SQL execution also).
  • Send Selection To Psql Just as above except only the selection will be sent to the backend.
  • Save SQL Window... Will bring up a dialog box that lets the user select a save file name. The contents of the SQL window will be written to the file.
  • Save SQL Results Window... Will bring up a dialog window that lets the user select a save file name. The contents of the SQL Results panel will be written to the file. The first row will be the column names. The remainder of the rows will be the data in the results window using a "|" as the item separator one record per line. None will be represented as an empty string.
  • Quit Will quit the application.

---

• The Edit Menu The items in this menu are currently disabled. However one can use the native window manager cut/paste/copy keystrokes depending on ones platform.

---

• The Scripts Menu

  

  Part of the startup code is to look for the folder "~/SQLScripts". If the folder is not found then it will be created.

  The purpose of this folder is to serve as a library of frequently used sql fragments. During startup the directory is scanned and every file ending in ".sql" (independent of case) is added to the menu. If a subdirectory is encountered the directory name becomes an entry in the script menu as a submenu. The submenu is recursively filled with files ending in ".sql" and subdirectories. Choosing a menu item from this list will clear and load the SQL window with the contents of the selected file.

  • Open Scripts Folder trys to open the folder "~/SQLScripts" using various methods depending on the platform.
  • Refresh Scripts Menu If the user adds a script while the program is running, this item will refresh the menu so the new item is visable in the menu.

---

• The Help Menu There currently is only one item under the help menu.
  • Help For SQL Choosing this item from the help menu will bring up a box with two columns.

    

    Clicking on an item in the left hand listbox will cause the right hand list box to scroll to the (minimal) help for the command.

  ---

• The popup menu The result grid supports a popup menu. <Control-Button1> in the "grid" will bring up the menu:

  

  • Show As Text Will show the contents of the field where the contextual menu is opened as text in a scrolling text window. This can be used to see the complete contents of "long fields".
  • Show Large Object As Text If large object support is available, choosing this item over a field will display the the large object in a text window. This item will be disabled if large object support is not present in psycopg2.
  • Export Field.... Will bring up a "FileSave Dialog" and allow the user to select a file to receive the contents of the selected field. for example if a picture was stored in a bytea field then the picture could easily be extracted with this menu item.
  • Export Large Object... If the cursor is over a large object field and large object support is available then a file dialog will be displayed and the user can select a file name. The contents of the large object will be written to the specified file.
  • Show As Bytea Image If the PIL package is available then choosing this item for a (bytea) field that contains an image will display the image in a scrolling canvas.
  • Show As Large Object Image If the cursor is over a large object field and large object support is available then the PIL package will be used to display (if possible) the image stored in the large object.

• Pyscopg2 will not officially support Postgresql large objects until version 2.1 ( current released version on Sep 9, 2006 is 2.0.5.1). However support is in the Trunk, I am using version 285. You can obtain a version of psycopg that supports large objects by installing svn and the using the command :

  svn co http://initd.org/svn/psycopg/psycopg2/trunk psycopg-2.1

  A working version for linux and macos x can then easily be built using the usual python module install technique.

• If psql has been detected on the client system and a password is required on the target system then the user will need a ".pgpass" file in their home directory on the client system. This is because there is no way to pass a password to psql on the command line (and would be a security risk).
• If psql has been detected and access is allowed then if the first character of the string send to the back end starts with a "\" then string will automatically be sent via psql. Thus if I type "\l" (no quotes) the effect would be the same as if I had type "\l" in psql and a listing of the databases in the cluster will be displayed in the SQL window. Don't forget that each psql transaction is a standalone operation, ie no state is saved between invocations.
• PyPgExplorer operates in "autocommit" mode. This means that each successful statement is commited to the database. If the user is doing something tricky then code like the following might be advised...

        begin;
        do some tricky sql;
        commit or rollback 
   

Send mail to me (Jerry LeVan) if you have any problems/questions....
Back to my HomePage.

Counter courtesy of http://www.digits.com

Last update Oct 24,2006