The Python DB-API

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

To begin, the program must first import the appropriate Python module for connecting to the database product being used. By convention, all database modules compliant with the Python DB-API have names that end in “db”, e.g., soliddb and oracledb.

The next step is to create an object that represents a database connection. The object has the same name as the module. The information required to open a connection, and its format, varies for different databases. Usually, it includes a user name and password, and some indication of how to find the database server, such as a TCP/IP hostname. If you're using the free trial version of SOLID, UNIX pipes are the only method available to connect to the server, so the code is:

>>> import soliddb
>>> db = soliddb.soliddb('UPipe SOLID',
         'amk', 'mypassword')
>>> db
<Solid object at 809bf10>
Cursor Objects

Next, you should create a cursor object. A cursor object acts as a handle for a given SQL query; it allows retrieval of one or more rows of the result, until all the matching rows have been processed. For simple applications that do not need more than one query at a time, it's not necessary to use a cursor object because database objects support all the same methods as cursor objects. We'll deliberately use cursor objects in the following example. (For more on beginning SQL, see At the Forge by Reuven Lerner in LJ, October, 1997.)

Cursor objects provide an execute() statement that accepts a string containing an SQL statement to be performed. This, in turn causes the database server to create a set of rows that match the query.

The results are retrieved by calling a method whose name begins with fetch, which returns one or more matching rows or “None” if there are no more rows to retrieve. The fetchone() method always returns a single row, while fetchmany() returns a small number of rows and fetchall() returns all the rows that match.

For example, to list all the seminars being offered, do the following:

>>> cursor = db.cursor()
>>> # List all the seminars
>>> cursor.execute('select * from Seminars')
>>> cursor.fetchall(
[(4, 'Web Commerce', 300.0, 26),
 (1, 'Python Programming', 200.0, 15),
 (3, 'Socket Programming', 475.0, 7),
 (2, 'Intro to Linux', 100.0, 32),
 ]

A row is represented as a tuple, so the first row returned is:

(4, 'Web Commerce', 300.0, 26)
Notice that the rows aren't returned in sorted order; to do that, the query has to be slightly different (just add order by ID). Because they return multiple rows, the fetchmany() and fetchall() methods return a list of tuples. It's also possible to manually iterate through the results using the fetchone() method and looping until it returns “None”, as in this example which lists all the attendees for seminar 1:
>>> cursor.execute (
        'select * from Attendees where seminar=1')
>>> while (1):
...  attendee = cursor.fetchone()
...  if attendee == None: break
...  print attendee
...
('Albert', 1, 'no')
('Beth', 1, 'yes')
('Elaine', 1, 'yes')
SQL also lets you write queries that operate on multiple tables, as in this query, which lists the seminars that Albert will be attending:
>>> cursor.execute("""select Seminars.title
...                from Seminars, Attendees
...        where Attendees.name = 'Albert'
...            and Seminars.ID = Attendees.seminar""")
>>&Gt; cursor.fetchall()
[('Python Programming',), ('Web Commerce',)]
Now that we can get information out of the database, it's time to start modifying it by adding new information. Changes are made by using the SQL insert and update statements. Just like queries, the SQL statement is passed to the execute() method of a cursor object.

Transactions

Before showing how to add information, there's one subtlety to be noted that occurs when a task requires several different SQL commands to complete. Consider adding an attendee to a given seminar. This requires two steps. In one step, a row must be added to the Attendees table giving the person's name, the ID of the seminar they'll be attending and whether or not they've paid. In the other step, the places_left value for this seminar should be decreased by one, because there's room for one less person. SQL has no way to combine two commands, so this requires two execute() calls. But what if something happens and the second command isn't executed—perhaps, because the computer crashed, the network died or there was a typo in the Python program? The database is now inconsistent: an attendee has been added, but the places_left column for that seminar is now wrong.

Most databases offer transactions as a solution for this problem. A transaction is a group of commands: either all of them are executed, or none of them are. Programs can issue several SQL commands as part of a transaction and then commit them, (i.e., tell the database to apply all these changes simultaneously). Alternatively, the program can decide that something's wrong and roll back the transaction without making the changes.

For databases that support transactions, the Python interface silently starts a transaction when the cursor is created. The commit() method commits the updates made using that cursor, and the rollback() method discards them. Each method then starts a new transaction. Some databases don't have transactions, but simply apply all changes as they're executed. On these databases, commit() does nothing, but you should still call it in order to be compatible with those databases that do support transactions.

Listing 2 is a Python function that tries to get all this right by committing the transaction once both operations have been performed. Calling this function is simple:

addAttendee('George', 4, 'yes')

We can verify that the change was performed by checking the listing for seminar #4, and listing its attendees. This produces the following output:

Seminars:
4       'Web Commerce'  300.0   25
Attendees:
Albert  4       no
Dale    4       yes
Felix   4       no
George  4       yes
Note that this function is still buggy if more than one process or thread tries to execute it at the same time. Database programming can be potentially quite complex.

With this standardized interface, it's not difficult to write all kinds of Python programs that act as easy-to-use front ends to a database.

Resources

Andrew Kuchling works as a web site developer for Magnet Interactive in Washington, D.C. One of his past projects was a sizable commercial site that was implemented using Python on top of an Illustra database. He can be reached via e-mail at akuchling@acm.org.

______________________

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!

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState