Python Database Programming: Part Nine

Python Database ProgrammingPython Database Programming: Committing and Rolling Back Transactions

Each connection, while it is engaged in action, manages a transaction. With SQL, data is not modified unless you commit a transaction. The database then guarantees that it will perform all of the modifications in the transaction or none. As a result, you will not leave your database in a potentially erroneous condition.

To commit a transaction, we call the commit method of a connection:

conn.commit()

Note the the transaction methods are part of the connection class, not the cursor class.

If something goes wrong, like an exception is thrown that you can handle, you should call the rollback method to undo the effects of the incomplete transaction; this will restore the database to the state it was in before you started the transaction, guaranteed:

conn.rollback()

The capability to roll back a transaction is very important, because you can handle errors by ensuring that the database does not get changed. In addition, rollbacks are very useful for testing. You can insert, modify and delete a number of rows as part of a unit test and then roll back the transaction to undo the effects of all the changes. This enables your unit tests to run without making any permanent changes to the database. It also enables your unit tests to be run repeatedly, because each run resets the data.

The DB API defines several globals that need to be defined at the module level. You can use these globals to determine information about the database module and the features it supports. The following table lists these globals.

Global What It Holds
Apilevel Should hold ‘2.0’ for the DB API 2.0, or ‘1.0’ for the 1.0 API.
Paramstyle Defines how you can indicate the placeholders for dynamic data in your SQL statements. The values include:

  • ‘qmark’: Use question marks
  • ‘numeric’: Use a positional number style; e.g. ‘:1’, ‘:2’, etc.
  • ‘named’: Use a colon and a name for each parameter.
  • ‘format’: Use the ANSI C sprintf format codes.
  • ‘pyformat’: Use the Python extended format codes.

With a cursor object, you can check the definition attribute to see information about the data returned. This information should be a set of seven-element sequences, one for each column of result data. These sequences include the following items:

(name, type_code, display_size, internal_size, precision, scale, null_ok)

None can be used for all but the first two items, as shown in this example:

((‘FIRSTNAME’, None, None, None, None, None, None),
(‘LASTNAME’, None, None, None, None, None, None),
(‘NAME’, None, None, None, None, None, None))

With databases, errors happen a lot. The DB API defines a number of errors that must exist in each database module. The following table lists those errors:

Exception Usage
Warning Used for non-fatal issues. Must subclass StandardError.
Error Base class for errors. Must subclass StandardError.
InterfaceError Used for errors in the database module, not the database itself. Must subclass Error.
DatabaseError Used for errors in the database. Must subclass Error.
DataError Subclass of Database error that refers to errors in the data.
OperationalError Subclass of DatabaseError that refers to errors such as the loss of the connection to the database. These errors are generally outside of the control of a Python scripter.
IntegrityError Subclass of DatabaseError for situations that would damage the relational integrity, such as uniqueness constraints or foreign keys.
InternalError Subclass of DatabaseError that refers to errors internal to the database module, such as a cursor no longer being active.
ProgrammingError Subclass of DatabaseError that refers to errors such as bad table name and other things that can safely be blamed on the scripter.
NotSupportedError Subclass of DatabaseError that refers to trying to call unsupported functionality.

Your Python scripts should handle these errors. You can get more information about them by reading the DB API specification.

External Links:

Database Programming at wiki.python.org

Database Programming at python.about.com

Databases at docs.python-guide.org

Be Sociable, Share!

Speak Your Mind

*