#! /usr/bin/env python
#
# example.py: Using the Python Database API (DB-API)
# with Red Hat Database
#
# This example module utilizes the Python DB-API to
# create and modify data within an RHDB database.
# These examples have been # created to display the
# concepts of the Red Hat Database Python DB-API.
# Allow the Python DBI (pgdb.py) to be accessible
import pgdb
import string
# Dump information about the RHDB Python DB-API
def about ():
print "\n******************************************"
print " About the RHDB Python DB-API"
print " DB-API level: %s" % pgdb.apilevel
print " DB-API Thread Safety level: %d" % pgdb.threadsafety
print " DB-API Parameter Formatting: %s" % pgdb.paramstyle
print "******************************************\n"
# Return a connection to the database
def initialize ():
# Connect to the basketball database.
# Use the dsn as the connection parameter.
# The Python DB-API details the other valid connection
# parameters. Notify the user of any raised exceptions.
try:
db = pgdb.connect (dsn = 'localhost:basketball')
except:
print
print "Exception encountered connecting "
print "to database basketball."
print
# Force execution to stop by raising
# the handled exception again
raise
return db
# Close a database connection
def disconnect (db):
db.close ()
# Create and populate the table which the examples feed off of
def cleantable ():
# Connect to the database and open a cursor
db = initialize ()
cursor = db.cursor ()
print "\nDropping Players table..."
# Drop the table if it exists.
# This is done in a try/except block as we may
# be attempting to drop a table that does not exist.
# Handle exceptions as you see fit.
try:
cursor.execute ("drop table players")
except pgdb.DatabaseError:
print " Exception encountered: Table does not exist.
Continuing."
db.rollback ()
print " Creating and seeding Players table..."
# Create and populate the Players table.
# Any raised exceptions will cause the program to stop
# as no handler is declared.
cursor.execute ("create table players
(name varchar(20), team varchar(50))")
cursor.execute ("insert into players values
('Michael Jordan','Washington Wizards')")
cursor.execute ("insert into players values
('Tim Duncan','San Antonio Spurs')")
cursor.execute ("insert into players values
('Vince Carter','Toronto Raptors')")
db.commit ()
cursor.close ()
print " Players table successfully created.\n"
# Display the contents of the Players table and leave
viewtable (db)
disconnect (db)
# Display the contents of the Players table
def viewtable (db):
# Create understandable index variables
playerName, playerTeam = 0, 1
# Issue a full select from the Players table
cursor = db.cursor ()
cursor.execute ("select * from players order by name asc")
# Fetch the first result from the cursor
row = cursor.fetchone ()
# Display a message on an empty result set
if row == None:
print "Empty result set returned on select * from players"
else:
# Iterate through the result set displaying the player's name
# and team
print "Content of Players table:"
print "*************************"
while row != None:
print " %s -- %s" % (row[playerName], row[playerTeam])
row = cursor.fetchone ()
print "\nNumber of players: %d\n" % cursor.rowcount
cursor.close ()
# Update a member of the Players table
def update ():
# Connect to the database
db = initialize ()
cursor = db.cursor ()
selectStmt = "select * from players where name = 'Kobe Bryant'"
updateStmt = "update players set team='LA Lakers'
where name = 'Kobe Bryant'"
# We'll show a raw before/after image of the result sets sent
# back for Kobe Bryant
cursor.execute (selectStmt)
print "\nBefore:", cursor.fetchall ()
# Update Kobe's real team
print "\nUpdating..."
cursor.execute (updateStmt)
# Display the updated row(s) and then the rest of the table
cursor.execute (selectStmt)
print "\nAfter: ", cursor.fetchall ()
print
viewtable (db)
# Cleanup
db.commit ()
cursor.close ()
db.close ()
# Modify table data and commit or rollback the transaction based
# on user input
def transaction (action):
# Use the older versions of string manipulation so that
# the example works with all versions of Python
command = string.lower (action)
# Check parameters
if (command != "commit") and (command != "rollback"):
print "Usage: transaction (action) where"
print " action=\"commit\" or
action=\"rollback\""
return
# Connect to the database
db = initialize ()
# Display the contents of the Players table before we modify
# the data
print "\nBefore:"
viewtable (db)
# Create SQL statements to be consumed by RHDB
insertStmt1 = "insert into players values
('Tracy McGrady','Orlando Magic')"
insertStmt2 = "insert into players values
('Kobe Bryant','NY Knicks')"
deleteStmt = "delete from players where name = 'Michael Jordan'"
print "About to issue the following commands:
\n %s\n %s\n %s" % (insertStmt1, insertStmt2, deleteStmt)
# Modify the table data. If specific exceptions are returned,
# rollback the transaction and leave... any other exceptions
# will cause execution to halt.
cursor = db.cursor ()
try:
cursor.execute (insertStmt1)
cursor.execute (insertStmt2)
cursor.execute (deleteStmt)
except (pgdb.DatabaseError, pgdb.OperationalError,
pgdb.pgOperationalError):
print " Exception encountered while modifying table data."
db.rollback ()
return
print "\nAbout to", command, "the transaction..."
# Commit or rollback the transaction as requested
if command == "commit":
db.commit ()
else:
db.rollback ()
cursor.close ()
print "\nAfter:"
# Display the contents of the Players table
# after the (potential) data modification
viewtable (db)
disconnect (db) |