Exception Handling in MySQL for Python

MySQL for Python

September 2010


Integrating MySQL and Python can bring a whole new level of productivity to your applications. This practical tutorial shows you how with examples and explanations that clarify even the most difficult concepts.


MySQL for Python

MySQL for Python

Integrate the flexibility of Python and the power of MySQL to boost the productivity of your Python applications

  • Implement the outstanding features of Python's MySQL library to their full potential
  • See how to make MySQL take the processing burden from your programs
  • Learn how to employ Python with MySQL to power your websites and desktop applications
  • Apply your knowledge of MySQL and Python to real-world problems instead of hypothetical scenarios
  • A manual packed with step-by-step exercises to integrate your Python applications with the MySQL database server

Read more about this book

(For more resources on Phython see here.)

Why errors and warnings are good for you

The value of rigorous error checking is exemplified in any of the several catastrophes arising from poor software engineering. Examples abound, but a few are particularly illustrative of what happens when bad data and design go unchallenged.

On 4 June 1996, the first test flight of the Ariane 5 rocket self-destructed 37 seconds after its launch. The navigation code from Ariane 4 was reused in Ariane 5. The faster processing speed on the newer rocket caused an operand error. The conversion of a 64-bit floating-point value resulted in a larger-than-expected and unsupported 16-bit signed integer. The result was an overflow that scrambled the flight's computer, causing too much thrust to be passed by the rocket itself, resulting in the crash of US$370 million worth of technology. Widely considered to be one of the most expensive computer bugs in history, the crash arose due to mistakes in design and in subsequent error checking.

On 15 January 1990, the American telecommunications company AT&T installed a new system on the switches that controlled their long-distance service. A bug in the software caused the computers to crash every time they received a message from one of their neighboring switches. The message in question just happened to be the same one that the switches send out when they recover from a system crash. The result: Within a short time, 114 switches across New York City were rebooting every six seconds, leaving around 60,000 people without long distance service for nine hours. The system ultimately had to be fixed by reinstalling the old software.

On the Internet, a lack of proper error-checking still makes it possible for a malformed ping request to crash a server anywhere in the world. The Computer Emergency Response Team (CERT) Advisory on this bug, CA-1996-26, was released in 1996, but the bug persists. The original denial-of-service attack has thus evolved into the distributed denial-of-service attack employing botnets of zombie machines worldwide.

More than any other part of a computing system, errors cost significantly more to fix later than if they were resolved earlier in the development process. It is specifically for this reason that Python outputs error messages to the screen, unless such errors are explicitly handled otherwise.

A basic dynamic of computing is that the computer does not let anyone know what is happening inside itself. A simple illustration of this dynamic is as follows:

x = 2
if x == 2:
x = x + x

Knowing Python and reading the code, we understand that the value of x is now 4. But the computer has provided us no indication of the value of x. What's more, it will not tell us anything unless we explicitly tell it to do so. Generally speaking, there are two ways you can ask Python to tell you what it's thinking:

  • By outputting values to the screen
  • By writing them to a file

Here, a simple print statement would tell us the value of x.

Output displayed on the screen or saved to a file are the most common ways for programs to report their status to users. However, the similar effect is done by indicator lights and other non-verbal forms of communication. The type of output is necessarily dependent on the hardware being used.

By default, Python outputs all errors and warnings to the screen. As MySQL for Python is interpreted by Python, errors passed by MySQLdb are no different. This naturally gives the debugging programmer information for ironing out the performance of the program—whether determining why a program is not executing as planned or how to make it execute faster or more reliably. However, it also means that any information needed for tracing the error, along with parts of the code, is passed to the user, whoever they may be.

This is great for debugging, but makes for terrible security. That is why the Zen of Python reads:

Errors should never pass silently

Unless explicitly silenced

One needs the error messages to know why the program fails, but it is a security hazard to pass raw error messages to the user. If one wants the user to handle an error message, it should be sanitized of information that may compromise the security of the system.

Handling exceptions correctly takes a lot of code. At the risk of sounding like a hypocrite, it should be noted that the exigencies of a printed book do not allow for the reproduction of constant, rigorous error-handling in the code examples such as this article espouses. Therefore, while I state this principle, the programming examples do not always illustrate it as they should. If they did, the book would be significantly thicker and heavier (and probably cost more too!).

Further, the more complicated an application, the more robust the error-handling should be. Ultimately, every kind of error is covered by one of the several types that can be thrown by MySQL for Python. Each one of them allows for customized error messages to be passed to the user.

With a bit of further coding, one can check the authentication level of the user and pass error messages according to their level of authorization. This can be done through a flag system or by using modules from the Python library. If the former is used, one must ensure that knowledge of the flag(s) used is guarded from unauthorized users. Alternatively, one can employ both systems by checking the authentication level of users or programs that pass a particular flag to the program.

Errors versus warnings: There's a big difference

As with Python in general, the main difference between errors and warnings is that warnings do not cause a program to terminate. Errors do. Warnings provide notice of something we should note; errors indicate the reason the program cannot continue. If not handled appropriately, warnings therefore pass process information to the user without interrupting the execution of the program. This lack of detectability makes warnings more dangerous to the security of an application, and the system in general, than errors. Consequently, the error-handling process of an application must account for both errors and warnings.

While Python handles warnings and exceptions differently by default, especially with regard to program execution, both are written to stderr. Therefore, one handles them the same way that one handles standard errors.

Additionally, one can set warnings to be silenced altogether or to carry the same gravity as an error. This level of functionality was introduced in Python 2.1.

The two main errors in MySQLdb

Python generally supports several kinds of errors, and MySQL for Python is no different. The obvious difference between the two is that MySQLdb's errors deal exclusively with the database connection. Where MySQLdb passes warnings that are not MySQL-specific, all exceptions are related to MySQL.

The MySQL-specific exceptions are then classified as either warnings or errors. There is only one kind of warning, but MySQLdb allows two categories of errors—DatabaseError and InterfaceError. Of the former, there are six types that we will discuss here.


When there is a problem with the MySQL database itself, a DatabaseError is thrown. This is an intermediate catch-all category of exceptions that deal with everything from how the data is processed (for example, errors arising from division by zero), to problems in the SQL syntax, to internal problems within MySQL itself. Essentially, if a connection is made and a problem arises, the DatabaseError will catch it.

Several types of exceptions are contained by the DatabaseError type. We look at each of these in the section Handling exceptions passed from MySQL.


When the database connection fails for some reason, MySQLdb will raise an InterfaceError. This may be caused from problems in the interface anywhere in the connection process.

Read more about this book

(For more resources on Phython see here.)

Warnings in MySQL for Python

In addition to errors, MySQL for Python also supports a Warning class. This exception is raised for warnings like data truncation when executing an INSERT statement. It may be caught just like an error, but otherwise will not interrupt the flow of a program.

Handling exceptions passed from MySQL

MySQL for Python takes care of the nitty-gritty of communication between your program and MySQL. As a result, handling exceptions passed from MySQL is as straightforward as handling exceptions passed from any other Python module.

Python exception-handling

Python error-handling uses a try...except...else code structure to handle exceptions. It then uses raise to generate the error.

while True:
x = int(raw_input("Please enter a number: "))
print "That is not a valid number. Please try again..."

While this is the example for raising an error, there are a few points to keep in mind.

while True:

This sets up a loop with a condition that applies as long as there are no exceptions raised.


Python then tries to execute whatever follows. If successful, the program terminates with break. If not, an exception is registered, but not raised.


The use of except tells Python what to do in the event of an exception. In this case it prints a message to the screen, but it does not raise an exception. Instead, the while loop remains unbroken and another number is requested.

Catching an exception from MySQLdb

All exceptions in MySQL for Python are accessed as part of MySQLdb. Therefore, one cannot reference them directly. Using the fish database, execute the following code:

#!/usr/bin/env python

import MySQLdb

mydb = MySQLdb.connect(host ='localhost',
user = 'skipper',
passwd = 'secret',
db = 'fish'
cur = mydb.cursor()

# Note the use of '7a' instead of '7'
statement = """SELECT * FROM menu WHERE id=7a"""

results = cur.fetchall()
print results
except Error:
print "An error has been passed."

The preceding code will return a NameError from Python itself. For Python to recognize the exception from MySQLdb, change each instance of Error to read MySQLdb.Error. The except clause then reads as follows:

except MySQLdb.Error:
print "An error has been passed."

The resulting output will be from the print statement.

An error has been passed.

Raising an error or a warning

An exception is only explicitly registered when raise is used. Instead of the print statement used in the except clause previously, we can raise an error and update the print statement with the following line of code:

raise MySQLdb.Error

Instead of the friendly statement about an error passing, we get a stack trace that ends as follows:


Remember that MySQLdb is a macro system for interfacing with _mysql_ and, subsequently, with the C API for MySQL. Any errors that pass from MySQL come through each of those before reaching the Python interpreter and your program.

Instead of raising an actual error, we can raise our own error message. After the MySQLdb.Error in the raise line, simply place your error message in parentheses and quotes.

raise MySQLdb.Error("An error has been passed. Please contact your
system administrator.")

As shown here, the exact error message is customizable. If raise is simply appended to the preceding code as part of the except clause, the usual stack trace will be printed to stdout whenever the except clause is run. Note also that the flow of the program is interrupted whenever raise is executed.

The same process applies when raising a warning. Simply use MySQLdb.Warning and, if necessary, also use a suitable warning message.

Read more about this book

(For more resources on Phython see here.)

Making exceptions less intimidating

For many users, program exceptions are a sign on par with Armageddon and tend to elicit the anxiety and mystery that accompany the usual view of that occasion. In order to be more helpful to users and to help users be more helpful to their IT support staff, it is good practice to give error messages that are explanatory rather than merely cryptic. Consider the following two error messages:

  • Exception: NameError in line 256 of someprogram.py.
  • The value you passed is not of the correct format. The program needs an integer value and you passed a character of the alphabet. Please contact a member of IT staff if you need further clarifi cation on this error and tell them the error message is: "Unknown column '7a' in 'where clause' on line 256 of someprogram.py".

Admittedly, the fi rst takes up less space and takes less time to type. But it also is guaranteed to compromise the usefulness of your program for the user and to increase the number of phone calls to the IT helpdesk. While the second may be a bit longer than necessary, the user and helpdesk will benefi t from a helpful message, regardless of its verbosity, more than an overly technical and terse one.

To accomplish a user-friendly error message that nonetheless provides the technical information necessary, catch the exception that Python passes. Using the previous if...except loop, we can catch the error without the traceback as follows:

#!/usr/bin/env python

import MySQLdb

mydb = MySQLdb.connect(host ='localhost',
user = 'skipper',
passwd = 'secret',
db = 'fish')

cur = mydb.cursor()

statement = """SELECT * FROM menu WHERE id=7a"""

results = cur.fetchall()
print results
except MySQLdb.Error, e:
print "An error has been passed. %s" %e

Now when the program is executed, we receive the following output:

An error has been passed. (1054, "Unknown column '7a' in 'where clause'")

This could easily be revised to similar wording as the second of the two error examples seen just now.

Catching different types of exceptions

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.

In Python, there are 36 built-in exceptions and 9 built-in warnings. It is beyond the scope of this article to go into them in detail, but further discussion on them can be found online.
For exceptions see:

For warnings visit:
The Python documentation also covers them at:


In the above article we have covered:

  • Why errors and warnings are good for you
  • Errors versus warnings: there's a big difference
  • The two main errors in MySQLdb
  • Warnings in MySQL for Python
  • Handling exceptions passed from MySQL
  • Catching different types of exceptions

Further resources on this subject:

Books to Consider

comments powered by Disqus