In this section we examine various aspects of the Python Database API using a sample program. The topics that will be covered are:
Database schema
Loading the Python Database API module and connecting to a database
Performing queries and updates
Retrieving results
Transactions
Closing a cursor and connection.
The examples will focus on using the Python Database API interactively with the Python interpreter.
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.
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.
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'") |
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) |
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 () |
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.