Databases, GUIs, and Python

Date: Jan 24, 2003

Article is provided courtesy of InformIT.

Now that Python has grown up, it has to hold its own in two- and three-tier database applications against corporate heavyweights such as Java and Visual Basic. Boudewijn Rempt analyzes Python's possibilities and weighs its strengths and weaknesses.

Any application that exhibits any complexity contains something that could be called a database: structured data that is persisted between invocations of the application. Uses range from the dedicated object databases that constitute a word-processor document, to the vanilla tables and files of a payroll application. However, when people talk about database-based applications, they mostly think of a basic two- or three-tier architecture: a relational database, a GUI front end, and perhaps a middle layers.

Now that Python has grown up, it has to hold its own in this area against corporate heavyweights such as Java, Visual Basic, or any of the proprietary solutions such as Oracle Forms. This article examines the possibilities, strengths, and weaknesses of Python in the area of two- and three-tier database applications.

Files

The simplest way to store your data is to use key-value pairs. Python's native dictionary type makes this possible. Using one of the standard modules, such as pickle, shelve, or anydbm, you can store your data in a platform-independent file format.

It is quite easy to create a simple file-based database with multiple indexes using only Python dictionaries and pickle. If the needs of your GUI or Web application are simple—that is, if you don't need many dynamically built queries over many tables—you can use such a back end instead of a real database. An example is the textdb back end for the dbobj database wrapper model I have developed.

DB-API II

The most usual way of connecting to a database in Python is to use one of the databases supported by DB-API II modules. DB-API II is the current standard for low-level access to any relational database. If you don't use any extensions, and if you write database-independent SQL, your DB-API II–compatible code should work on any of the supported databases. Writing database-independent SQL code might well be impossible if you consider that, for instance, MySQL doesn't support subqueries or that the Oracle syntax for outer joins differs from the ANSI standard. And no database offers the same mechanism to access the automatically incremented primary key values after inserting a row. Some databases don't offer any mechanism for that at all.

Supported databases include DB/2, Informix, Interbase, Ingres, MySQL, Oracle, PostgreSQL, SAP, and Sybase. However, you need to check in advance whether the particular database that you want to use is supported on your particular operating system. For instance, psycopg, is reported to work only on Solaris, Linux, and OS/X—that is to say, it has been tested on UNIX.

If your database isn't supported directly but supports ODBC, you can always try to use one of the DB-API II–compliant ODBC modules. The most famous of these is mxODBC. And if you're using Jython, you can use JDBC—either directly, because Jython enables you to access all Java class libraries, or via a DB-API II–compliant wrapper, zxJDBC.

If you are securely wedded to the Microsoft world, you can also try the DB-API II bindings to ADO, an alternative to ODBC (see http://adodbapi.sourceforge.net/).

Qt SQL and Data-Aware Widgets

If you want to trade the flexibility DB-API II offers for ease of prototyping, you can use PyQt, which wraps Qt's data-aware controls. Qt, a cross-platform GUI library (Windows, UNIX/X11, OS X), comes with a complete set of classes to access databases. Currently supported are MySQL, Oracle (via the OCI interface, which is really fast), ODBC, PostgreSQL, and Sybase/Microsoft SQL Server.

Some experimental evidence suggests that it can be quite hard to compile database drivers for Qt—but it can be equally hard for DB-API II.

From a developer's point of view, there are basically two approaches to a standalone GUI database application: using data-aware controls or widgets and letting the library do the heavy lifting of records and fields from the database, or using vanilla controls or widgets and doing the retrieving, inserting, deleting, and updating of records yourself.

You might assume that the first option would be the most popular because it evidently takes least time to develop. Unfortunately, that is seldom true. The problem with most data-aware GUI controls or widgets is that the database connection code behind that GUI control is simple—often even simple-minded. Perhaps it's suitable for small one-off applications, but not for real-world projects. Another point against using data-aware controls is that they lead to a simple, two-tiered architecture in which it might be difficult to change databases.

I have about a decade of experience with building database applications with Oracle, Visual Basic, and Java. Only Oracle Forms have stood up to the challenge, mainly because once you decide to use Oracle Forms, you've also decided to use an Oracle database. Visual Basic and Java both forced our development team to separate the GUI application from the data-handling code—which might be a very good thing, too.

With the advent of Web applications based on databases, the wisdom of tying up the database access code into the GUI client is even more questionable. However, to provide a full palette of choices, Python should offer data-aware controls, too.

Gadfly

Where Java has a plethora of small memory-based relational databases such as McKoi or hSQldb, there is really only one contender for Python: Gadfly. Gadfly has some limitations: MySQL subqueries are not supported, for instance, and neither are inner or outer joins. Worse, it lacks the LIKE predicate. Still, it is a stable and useful tool in the toolbox, one that offers a good performance.

Object Databases

Python is an object-oriented language. Relational databases notoriously aren't. So how do the twain meet? Ideally, you'd want to write code that goes like this:

class Person
 def __init__(self, name, age):
  name = name
  age = age

person = Person("Boudewijn Rempt", 33)

instead of this:

myStatement = """insert into person 
(name, age) values (%s, %i)"""

dbCursor.execute(myStatement % ("Boudewijn Rempt", 32))

There are two possible approaches to achieving an object-oriented view of your data: wrapping the underlying relational database in an object-oriented middleware component, or directly using an object-based database.

Matisse

Matisse is one object database that offers Python bindings and that also offers SQL support.

ZODB

A far more well-known object database for Python is ZODB, the Zope Object Database. ZODB is far more closely coupled with Python than Matisse, and it can easily be used outside a ZOPE application.

ZODB is really easy to use. Basically, once you've created the database, you have a root dictionary that you can add your Python objects to. You can add, delete, and update objects to your heart's content, if only you remember that your objects must be pickleable. That means that you cannot store code objects (methods, classes, function) or file objects (files, sockets).

On the other hand, because you are basically working with Python dictionaries, you are limited to the kind of manipulations dictionaries support. That means that retrieving all the objects where, say, the Name field contains "Rempt" is harder than with a relational database, where you can write a simple query. With ZODB, you have to iterate over the contents of your dictionary and check every item.

On top of ZODB sits ZEO, the Zope Enterprise Objects. This makes ZODB a standalone database server that can be accessed from many clients over the network. The distribution of ZODB comes with an extensive programming guide.

Object-Relational Mappers

One wheel that has been invented time and time again is the object-relational mapping. This is code that either takes Python objects and prepares them for storage in a relational database, or code that takes the data model of a relational database and constructs Python objects from it.

An example of the first approach is discussed in Joel Shprentz's article "Persistent Storage of Python Objects in Relational Databases." I developed the second idea in dbObj (see http://www.valdyas.org/python/dbobj.html), together with a library that takes the repository and automatically creates PyQt-based data-aware forms and table views.

A Simple GUI Using PyQt's Data-Aware Objects

If are looking for the easiest way to create a database application, you might want to take a look at PyQt. PyQt, the Python bindings to the cross-platform GUI toolkit Qt, supports the designer files created by Qt Designer. Using Qt Designer, you can easily create forms that connect to any database.

From these forms, you can generate either Python or C++ code. I have written a book on working with PyQt and Designer (GUI Programming with Python: Qt Edition) that deals with this toolkit. Here, I just want to show you the steps you need to create data-aware forms.

When would it be appropriate to use PyQt's database widgets? The following points need consideration:

You are prototyping and might want to migrate your design to C++ at a certain point in time.
A two-tier architecture suffices. You don't think you will need to provide alternative interfaces, such as a Web interface or a report interface, to your database (since those would require coding the application logic again.)
You do not use complex queries that link tables.
You want to deploy on UNIX/X11, Windows, or OS X.

You are using PyQt anyway. Using PyQt's SQL classes enables you to eliminate the need for extra database-related modules, making distribution of your application easier.

When you have decided to use Qt's data-aware controls, your life suddenly becomes very simple. In Qt Designer, you first create a new database connection. See Figure 1.

Figure 1 Creating a database connection.

When that is done, you can start designing a form. In this case, we have a simple window, of the type QDialog, and one of three data-aware controls will be placed in it. You have a choice between QDataTable, which presents the data in a tabular form; QDataBrowser, which presents one record in a simple form; and QDataView, a read-only form.

Creating the form is made easy by the Data Table Wizard (see Figure 2). First you select the database connection and the table you want to select from.

Figure 2 Selecting the database connection and the table.

Then you select the fields that you want to include in the table (see Figure 3).

Figure 3 Selecting the fields that should be displayed.

In the next step, you select the way the form will interact with the user when confirmations are needed and whether the user can sort on columns (see Figure 4).

Figure 4 User interaction options.

It is possible to create a specific SQL WHERE clause, and to select the columns to use in the ORDER BY clause (see Figure 5).

Figure 5 Adding SQL code.

Finally, you can set autoediting on or off. Autoediting kicks in when the changes are made to the contents of the QDataTable; if it's true, changes are automatically committed when the user navigates to the next record. (See Figure 6.)

Figure 6 Setting autoediting.

The result is a database-aware table that runs even inside Qt Designer (see Figure 7).

Figure 7 Running the form inside Qt Designer.

You can save your design to a .ui designer file and then create Python source code using a simple command-line command:

pyuic -x form1.ui > form1.py

However, the generated form isn't immediately useful. You have to embed it in a script where you create a database connection using QSqlDatabase:

import form1, sys
if __name__ == "__main__":
  a = QApplication(sys.argv)
  QObject.connect(a,SIGNAL("lastWindowClosed()"),
          a,SLOT("quit()"))
  db = QSqlDatabase.addDatabase("QMYSQL3")
  if db:
    db.setHostName("hostname")
    db.setDatabaseName("database")
    db.setUserName("username")
    db.setPassword("xxx")
    if not db.open():
      print "Could not open testdb database"
      print db.lastError().driverText()
      print db.lastError().databaseText()
      sys.exit(1)
  w = form1.Form1()
  a.setMainWidget(w)
  w.show()

  a.exec_loop()

When you run this script and the database exists and is running, and when your Qt is compiled with the right drivers, you will be greeted once again by your form filled with data.

Of course, Qt wouldn't be what it is—as complete as Java, yet simple and compact—if it didn't also provide classes that you can use for more complex database handling. These are the QSQl module classes, such as QSqlDatabase, QSqlQuery, and QSqlCursor. Using those, however, means that you are leaving the balmy country of data-aware widgets and that you have to start rolling your own solution again.

Nowadays Python can hold its own in the wonderful world of databases and GUI applications. For simple tasks, you can select PyQt and its data-aware widgets, and be done quickly with good, efficient results. More complex tasks demand a more complex approach, but there is no dearth of powerful tools libraries to choose from.