#! /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) |