Sample Application

Database Schema

For all of the sample code, we assume that a database called basketball has been created. The basketball database contains one table called players, which contains a player's name and team.

The table definition is as follows:

CREATE TABLE players (
   name varchar(25), 
   team varchar(20)
)

and is populated with the following data:

INSERT INTO players VALUES ('Michael Jordan', 'Washington Wizards')
INSERT INTO players VALUES ('Tim Duncan', 'San Antonio Spurs')
INSERT INTO players VALUES ('Vince Carter', 'Toronto Raptors')

A function in the example.py module, cleantable(), creates the players table as defined above. See the section the Section called Code: example.py.

Loading the Python Database API Module and Connecting to a Database

In order to connect to an RHDB database using the Python Database API, you must load the Python Database API module, pgdb.py.

Start the Python interpreter:

$ python

and then load the Python Database API module:

>>> import pgdb

This allows access to the objects and methods defined in the API.

To connect to a database, call the connect() method of the Connection Object.

To connect to a database named basketball on my local host, using user patrickm and password fakepswd:

>>> dbConnect = pgdb.connect (dsn='localhost:basketball', 
               user='patrickm', password='fakepswd')

The above statement creates a local Connection Object, dbConnect.

Performing Queries and Updates

To perform queries and to update data (that is, to execute INSERT, UPDATE, or DELETE commands), you need to allocate a Cursor Object by invoking the cursor() method of your newly allocated Connection Object dbConnect.

>>> cursor = dbConnect.cursor ()

The execute() method of the Cursor Object prepares and executes a database operation. A reference to the operation performed will be retained by the cursor to allow result set iteration.

To perform a query on the players table:

>>> cursor.execute ("select * from players")

To perform a delete on the players table:

>>> cursor.execute("delete from players where name = 'Michael Jordan'")

Retrieving Results

The Python Database API supports the concept of retrieving one or more rows from a result set using the Cursor Object's fetchone(), fetchmany(), and fetchall() methods. Given the following query:

>>> cursor.execute ("select * from players")

To retrieve one row from the result set, call the fetchone() method of the Cursor Object:

>>> row = cursor.fetchone ()

If there are no more rows in the result set, fetchone() will return None; otherwise it will return the row/tuple.

Let's look at an example of iterating through the result set one row at a time and displaying the result:

>>> cursor.execute ("select * from players")
>>> while (1):
...   row = cursor.fetchone()
...   if row == None:
...     break
...   print row

As mentioned, there are other methods of the Cursor Object that return rows from result sets. Call the fetchall() method to display all remaining rows of the result set:

>>> cursor.fetchall ()

The fetchmany() method returns the next set of rows from the result set, based on the method call parameter, as a list of rows. To return the next two rows of a result set, call the fetchmany() method with a size of 2:

>>> cursor.fetchmany (2)

Transactions

A transaction is a grouping of SQL statements that are perceived by the database as one atomic action. They are guaranteed to either all succeed or all fail. Transactions are implicitly started when a Cursor Object is created and are explicitly ended using the transaction-ending method calls, commit() or rollback(), of the Connection Object. These transaction ending method calls implicitly start a new transaction after ending the current one. Note that since Red Hat Database supports an auto-commit feature, it is initially turned off as per the Python Database API Specification.

To commit a transaction and make the results visible to the rest of the system, call the commit() method of the Connection Object:

>>> dbConnect.commit ()

To rollback a transaction, call the rollback() method of the Connection Object:

>>> dbConnect.rollback () 

Closing a Cursor and Connection

To close a cursor, call the close() method of the Cursor Object:

>>> cursor.close ()

The cursor will be unusable after the close() method call. Any operation attempted with the cursor after it is closed will raise an exception.

As with closing a cursor, closing a connection is very simple. All you have to do is call the close() method of the Connection Object.

>>> dbConnect.close ()

The connection will be unusable after the close() method call. Any operation attempted with the connection after it is closed will raise an exception.