Database
 

MySQL for Python : Exception Handling - Catching different types of exceptions

11/23/2011 6:04:17 PM
It is typically best practice to process different types of exceptions with different policies. This applies not only to database programming, but to software development in general. Exceptions can be caught with a generic except clause for simpler implementations, but more complex programs should process exceptions by type.


Types of errors

The following are the six different error types supported by MySQL for Python. These are all caught when raising an error of the respective type, but their specification also allows for customized handling.

  • DataError

  • IntegrityError

  • InternalError

  • NotSupportedError

  • OperationalError

  • ProgrammingError

Each of these will be caught by using DatabaseError in conjunction with an except clause. But this leads to ambiguous error-handling and makes debugging difficult both for the programmer(s) who work on the application as well as the network and system administrators who will need to support the program once it is installed on the end user's machine.

DataError

This exception is raised due to problems with the processed data (for example, numeric value out of range, division by zero, and so on).

IntegrityError

If the relational integrity of the database is involved (for example a foreign key check fails, duplicate key, and so on), this exception is raised.

#!/usr/bin/env python
import MySQLdb, sys
mydb = MySQLdb.connect(host ='localhost',
user = 'skipper',
passwd = 'secret',
db = 'fish')
cur = mydb.cursor()
ident = sys.argv[1]
fish = sys.argv[2]
price = sys.argv[3]
statement = """INSERT INTO menu(id, name, price) VALUES("%s", "%s", "%s")""" %(ident, fish, price)
print "Data has been inserted using the following statement: \n", statement
cur.execute(statement)



Change the database login information as necessary. Then call it with an existent value for the identifier. For example:

> python temp.py 2 swordfish 23

The type of error follows a multiple line traceback:

_mysql_exceptions.IntegrityError: (1062, "Duplicate entry '2' for key
'PRIMARY'")

InternalError

This exception is raised when there is an internal error in the MySQL database itself (for example, an invalid cursor, the transaction is out of sync, and so on). This is usually an issue of timing out or otherwise being perceived by MySQL as having lost connectivity with a cursor.

NotSupportedError

MySQL for Python raises this exception when a method or database API that is not supported is used (for example, requesting a transaction-oriented function when transactions are not available. They also can arise in conjunction with setting characters sets, SQL modes, and when using MySQL in conjunction with Secure Socket Layer (SSL).

OperationalError

Exception raised for operational errors that are not necessarily under the control of the programmer (for example, an unexpected disconnect, the data source name is not found, a transaction could not be processed, a memory allocation error occurrs, and so on.). For example, when the following code is run against the fish database, MySQL will throw an OperationalError:

#!/usr/bin/env python
import MySQLdb, sys
mydb = MySQLdb.connect(host = 'localhost',
user = 'skipper',
passwd = 'secret',
db = 'fish')
cur = mydb.cursor()
statement = """SELECT * FROM menu WHERE id=7a""""
cur.execute(statement)
results = cur.fetchall()
print results

The error message reads as follows:

SELECT * FROM menu WHERE id=7a
Traceback (most recent call last):
File "temp.py", line 54, in <module>
cur.execute(statement)
File "/usr/local/lib/python2.6/dist-packages/MySQL_python-1.2.3c1-py2.6-linux-i686.egg/MySQLdb/cursors.py", line 173, in execute
File "/usr/local/lib/python2.6/dist-packages/MySQL_python-1.2.3c1-py2.6-linux-i686.egg/MySQLdb/connections.py", line 36, in defaulterrorhandler



ProgrammingError

Exception raised for actual programming errors (for example, a table is not found or already exists, there is a syntax error in the MySQL statement, a wrong number of parameters is specified, and so on.). For instance, run the following code against the fish database:

#!/usr/bin/env python
import MySQLdb
mydb = MySQLdb.connect(host ='localhost',
user = 'skipper',
passwd = 'secret',
db = 'fish')
cur = mydb.cursor()
fish = sys.argv[1]
price = sys.argv[2]
statement = """INSERTINTO menu(name, price) VALUES("%s", "%s")""" %(fish, price)
print "Data has been inserted using the following statement: \n", statement
cur.execute(statement)


The values you pass as arguments do not matter. The syntactic problem in the MySQL statement will cause a ProgrammingError to be raised:

Customizing for catching

Each of the previous types can be caught with the DatabaseError type. However, catching them separately allows you to customize responses. For example, you may want the application to fail softly for the user when a ProgrammingError is raised but nonetheless want the exception to be reported to the development team. You can do that with customized exception handling.

Catching one type of exception

To catch a particular type of exception, we simply include that type of exception with the except clause. For example, to change the code used for the OperationalError in order to catch that exception, we would use the following:

#!/usr/bin/env python
import MySQLdb, sys
mydb = MySQLdb.connect(host = 'localhost',
user = 'skipper',
passwd = 'r00tp4ss',
db = 'fish')
cur = mydb.cursor()
statement = """SELECT * FROM menu WHERE id=7a""""
try:
cur.execute(statement)
results = cur.fetchall()
print results
except MySQLdb.OperationalError, e:
raise e

After the traceback, we get the following output:

_mysql_exceptions.OperationalError: (1054, "Unknown column '7a' in 'where
clause'")

You can similarly catch any of MySQL for Python's error types or its warning. This allows much greater flexibility in exception-handling.

Catching different exceptions

To customize which error is caught, we need different except clauses. The basic structure of this strategy is as follows:

try:
<do something>
except ErrorType1:
<do something>
except ErrorType2:
<do something else>

To combine the examples for the OperationalError and ProgrammingError that we just saw, we would code as follows:

#!/usr/bin/env python
import MySQLdb, sys
mydb = MySQLdb.connect(host ='localhost',
user = 'skipper',
passwd = 'secret',
db = 'fish')
cur = mydb.cursor()
identifier = sys.argv[1]
statement = """SELECT * FROM menu WHERE id=%s"""" %(identifier)
try:
cur.execute(statement)
results = cur.fetchall()
print results
except MySQLdb.OperationalError, e:
raise e
except MySQLdb.ProgrammingError, e:
raise e

After writing this into a file, execute the program with different arguments. A definite way to trigger the OperationalError is by passing a bad value like 7a. For the ProgrammingError, try an equals sign.

One can do more than simply print and raise exceptions in the except clause. One can also pass system calls as necessary. So, for example, one could pass programming variables to a function to send all errors to a set address by a protocol of your choosing (SMTP, HTTP, FTP, and so on.). This is essentially how programs such as Windows Explorer, Mozilla Firefox and Google's Chrome browsers send feedback to their respective developers.


Combined catching of exceptions

It is not uncommon to want to handle different errors in the same way. To do this, one simply separates the errors by a comma and includes them within parentheses after except. For example, the preceding program could be rewritten as follows:

#!/usr/bin/env python
import MySQLdb, sys
mydb = MySQLdb.connect(host ='localhost',
user = 'skipper',
passwd = 'secret',
db = 'fish'))
cur = mydb.cursor()
identifier = sys.argv[1]
statement = """SELECT * FROM menu WHERE id=%s"""" %(identifier)
try:
cur.execute(statement)
results = cur.fetchall()
print results
except (MySQLdb.OperationalError, MySQLdb.ProgrammingError), e:
raise e

As with most parts of Python, enclosing the two error types in parentheses tells Python to accept either one as the error type. However, only one will be output when e is raised.

Raising different exceptions

Python will raise whatever valid type of error you pass to the raise statement. It is not particularly helpful to raise the wrong exceptions. However, particularly when debugging or auditing code that has been copied from another project, one should be aware that it can be done.

To illustrate this using the code listing previously, change the except clause to read as follows:

except (MySQLdb.OperationalError, MySQLdb.ProgrammingError), e:
raise ValueError

Then pass bad arguments to the program like 7a or ?.

 
Others
 
 
 
Most View
 
- Sharepoint 2013 : Community portals and sites - Reviewing posts submitted for moderation
- Microsoft Word 2010 : Creating an Outline (part 3) - Building an Outline - Expanding and Collapsing Parts of an Outline , Reorganizing an Outline
- Packaging and Deploying Sharepoint 2013 Apps : Deploying an App (part 1) - SharePoint-Hosted App Deployment
- Sharepoint 2010 : Windows PowerShell Functions
- Understanding Core Exchange Server 2013 Design Plans (part 3) - Understanding AD Design Concepts for Exchange Server 2013 - Understanding the AD Domain Structure
- Microsoft Project 2010 : Work Breakdown Structure (part 2) - WBS and Scheduling
- Microsoft Lync Server 2013 : Lync Online and Hybrid Deployments - Configuring Directory Synchronization (part 1)
- Windows Server 2012 : Installing and Configuring FTP Services (part 3) - Configuring FTP 8 Features and Properties - FTP Authentication Feature Page
- Understanding Core Exchange Server 2013 Design Plans (part 2) - Understanding AD Design Concepts for Exchange Server 2013 - Understanding the AD DS Forest
- Windows 8 : Sharing and Securing with User Accounts - Logging In and Out of User Accounts
 
 
Top 10
 
- Microsoft Access 2010 : Sharing Data with Other Applications - Linking to Tables in Another Access Database
- Microsoft Access 2010 : Sharing Data with Other Applications - Importing ASCII Data
- Microsoft Access 2010 : Sharing Data with Other Applications - Importing Spreadsheet Data
- Microsoft Access 2010 : Sharing Data with Other Applications - Importing from Another Access Database
- Microsoft Access 2010 : Sharing Data with Other Applications - Exporting to ASCII
- Microsoft Access 2010 : Sharing Data with Other Applications - Exporting to an Excel Spreadsheet
- Microsoft Access 2010 : Sharing Data with Other Applications - Exporting to Another Access Database
- Microsoft Access 2010 : Sharing Data with Other Applications - Importing, Linking, and Opening Files
- Windows 8 Apps and the Windows Store : Installing Apps from the Windows Store, Sharing Windows 8 App Data
- The Windows 8 Apps (part 7) - Travel, Video