The Tutorial:
 
 


    Lesson 8: The Python DB-API, Version 1.0

    Most Web sites require large-scale data warehousing and data mining.  This feat is nearly impossible without the use of a relational database backend.  Additionally, many of the commercially available interfaces to leading database engines are extremely costly.  This cost can be avoided by employing the Python Database Application Program Interface (DB-API) modules to handle Structured Query Language (SQL) requests to various database engines.  The DB-API provides a standard Python interface to most commercial, freeware, and shareware databases.  Therefore, there exists a single, uniform interface format to learn, and modifying the front-end code to work with a different database product requires only minor changes [5].

    The first step in using a DB-API module is to import the correct database module and create an object to represent the database connection.  By convention, all DB-API compliant database modules have names ending with the letters 'db' (i.e., oracledb, informixdb, mysqldb, and soliddb). The correct syntax for this process is described below.

    Code Example 8.1: 

    # Import the module.
    import oracledb

    # Create a connection.
    db = oracledb.oracledb("db_username/password@db_host")

    # Close connection when finished.
    db.close()

    In the example above, the words in pink should be substituted with your username, password, and host. Note that this connection string format is specific to the oracledb module. Each DB-API compliant module has a different connection string format, and you should check the documentation to your specific module.

    Simple SQL Queries

    After you have established connection with the database, you should create a cursor object to act as a handle for SQL queries.  This cursor handle facilitates the retrieval of table rows from the database.  The most basic cursor member functions are execute("some_SQL_statement"), fetchone(), and fetchall().  The execute() function takes the SQL statement (most commonly a "select", "insert", or "update" statement) to be performed.  The fetchone() function returns a single row that matches the execute() query.  The return value is a Python tuple data type.  The fetchall() function returns all rows that match the execute() query.  This function returns a list data type of tuple data types. Code Example 8.2 demonstrates the proper syntax for the cursor functions. Again, the words in pink should be substituted with items specific to your database.

    Code Example 8.2: 

    # Import the module.
    import oracledb

    # Create a connection.
    db = oracledb.oracledb("db_username/password@db_host")

    # Create a cursor.
    cursor = db.cursor()

    # Query the database.
    cursor.execute('select * from a_table')

    # Return a single row.
    single_row = cursor.fetchone()

    # Return every matching row.
    list_of_rows = cursor.fetchall()

    # Close connection when finished.
    db.close()

    Transactions

    Some situations require updates to several dependent database tables.  For example, imagine a CGI program that transfers money from one bank account to another.  The tuples, account balances in this case, must be updated "simultaneously."  If one account tuple is subtracted by the transfer amount, and a program error or network failure causes the second account not to be updated, money is "lost."

    A transaction is a group of commands, where either all or none of the commands are executed.  A partial execution would leave the database in an inconsistent state [5].

    Python provides the commit() function for the DB-API compliant modules.  When an "insert" or "update" SQL command is issued, the db.commit() function commits all the statements made by the cursor.  The rollback() function discards the cursor statements.  Some database engines do not support this feature; check your documentation.



 © 2000 Department of Computer Science, University of Virginia