The Python DB-API

A Python SIG has put together a DB-API standard; Mr. Kuchling gives us the details.

Many people use Python because, like other scripting languages, it is a portable, platform-independent and general-purpose language that can perform the same tasks as the database-centric, proprietary 4GL tools supplied by database vendors. Like 4GL tools, Python lets you write programs that access, display and update the information in the database with minimal effort. Unlike many 4GLs, Python also gives you a variety of other capabilities, such as parsing HTML, making socket connections and encrypting data.

Possible applications for the Python DB-API include:

  • Many web sites construct pages on the fly to display information requested by the user, such as selections from the products offered in a catalog or from the documents available in a library. Doing this requires CGI scripts that can extract the desired information from a database and render it as HTML.

  • An Intranet application might use the Tkinter module and the DB-API to provide a graphical user interface for browsing through a local database, such as accounts receivable or a customer list.

  • Python programs can be used to analyze data by computing statistical properties of the data.

  • Python programs can form a testing framework for programs that modify the database, in order to verify that a particular integrity constraint is maintained.

There are lots of commercial and freeware databases available, and most of them provide Structured Query Language (SQL) for retrieving and adding information (see Resources). However, while most databases have SQL in common, the details of how to perform an SQL operation vary. The individuals who wrote the Python database modules invented their own interfaces, and the resulting proliferation of different Python modules caused problems: no two of them were exactly alike, so if you decided to switch to a new database product, you had to rewrite all the code that retrieved and inserted data.

To solve the problem, a Special Interest Group (SIG) for databases was formed. People interested in using Python for a given application form a SIG of their own: they meet on an Internet mailing list, where they discuss the topic, exchange ideas, write code (and debug it) and eventually produce a finished product. (Sounds a lot like the development process for the Linux kernel, doesn't it?)

After some discussion, the Database SIG produced a specification for a consistent interface to relational databases—the DB-API. Thanks to this specification, there's only one interface to learn. Porting code to a different database product is much simpler, often requiring the change of only a few lines.

The database modules written before the Database SIG are still around and don't match the specification—the mSQL module is the most commonly used one. These modules will eventually be rewritten to comply with the DB-API; it's just a matter of the maintainers finding the time to do it.

Relational Databases

A relational database is made up of one or more tables. Each table is divided into columns and rows. A column contains items of a similar type, such as customer IDs or prices, and a row contains a single data item, with a value for each column. A single row is also called a tuple or a relation, which is where the term “relational database” originates.

For an example database, we'll use a small table designed to track the attendees for a series of seminars. (See Listing 1.) The Seminars table lists the seminars being offered; an example row is (1, Python Programming, 200, 15). Each row contains a unique identifying ID number (1, in this case), the seminar's title (Python Programming), its cost ($200), and the number of places still open (15). The Attendees table lists the name of each attendee, the seminar that he or she wishes to attend and whether the fee has been paid. If someone wants to attend more than one seminar, there will be more than one row with that person's name, with each row having a different seminar number and payment status.

The examples below use the soliddb module, which supports accessing SOLID databases from Python. SOLID is a product from Solidtech that was reviewed by Bradley Willson in LJ, September, 1997. I'm not trying to cover CGI or Tkinter programming, so only the commands to access the database are presented here, in the same manner as if typed directly into the Python interpreter.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Iteration?

Anonymous's picture

I would rather do something more like this


cursor.execute(...)
for i in cursor:
   ...

clearer code

Anonymous's picture

why
while (1):
instead of
while 1:

and
if attendee == None: break
instead of
if attendee is None: break

if you want to give an example, please give a good one, not just something that works, show how to write pythonic code

I wonder if you thought at all before posting

digginestdogg's picture

Sometimes, things are not as they appear to the casual, shallow minded observer.
AMK is no 'newbie' to Python. Rather like a skilled sensei teaching young would-be samurai, he simplied his technique so as not to distract his students with complexity but rather to let them focus on his lesson one simple point at a time.

Huahweuahwa

Anonymous's picture

The real pythonic way of "saying" [if attendee is None: break]
is [if not attendee: break]
Remember, nothing is so good that can not be better . . .
If you want to correct someone, do this better next time.

Hey Andrew, nice code, your tutorial is the only link at PyGreSQL page, continue helpping people that are realy capable of learnning something.

Huahweuahwa

Anonymous's picture

The real pythonic way of "saying" [if attendee is None: break]
is [if not attendee: break]
Remember, nothing is so good that can not be better . . .
If you want to correct someone, do this better next time.

You certainly don't know who

Anonymous's picture

You certainly don't know who AMK is.

Well, as a python

Anonymous's picture

Well, as a python programmer, it could have been better, but since it's about using the API, and not how to write good python, it's still a good example.

Re: The Python DB-API

Anonymous's picture

This is useful. Thanks.

Re: The Python DB-API

Anonymous's picture

Good writing-hack. Thanx a lot, 'cause it was so useful.

LINUX POWER TO U ;o)

This is really helpful to beg

Anonymous's picture

This is really helpful to beginners to give them idea what is like DB programming with Python. Thanks!

nice :) There's a small ty

Anonymous's picture

nice :)

There's a small typo in one of the code samples where &Gt appears literally (it comes out as >>&Gt instead of >>>)

Thanks.

This is really helpful to beg

Anonymous's picture

This is really helpful to beginners to give them idea what is like DB programming with Python. Thanks!

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix