Main Page -> My Writings -> HOW TO Use ODBC with the Python DBI
by Mark Nenadov (Written ?. Last revised January ?)
Warning: This article was published quite some time ago on Coffee Code, and it may very well be out of date.
View it as if it is an older document, because it is. The document is shown here as it was originally published
Before I begin, I wish to suggest that you purchase Python Cookbook, Second Edition by O'Reilly. It is a fantastic collection of varied Python code snippets. The explanations provided are also very enlightening.
Part One: Python DBI in a Nutshell
Anyone who has ever programmed in Perl is probably familiar, with DBI. DBI simply means Database Interface.
For our exaple we want to connect to an ODBC database. There are three ways to connect to an ODBC database
in Python. One way is to use Sam Rushing's 'calldll' module, another way is to access Microsoft DAO, however
we will discover how to do it through the Python DBI. For the purpose of the example, I will focus only
on doing so on a Win32 machine.
The Python Database API is defined to encourage similarily for Python database access modules. Go to the end
of this article to find the URL for the actual specification of Python Database API 2.0. The Python DBI aims to
provide a uniform interface for tabular data access in Python. As you may notice later, not every database
module follows the API properly. The API pays particular attention to the module interface, the connection
object, and the cursor object. We will be using an ODBC implementation of the Python Database API that is
found in Mark Hammond's Win32 Extensions for Python.
Part Two: Up and Running with ODBC under Win32 (Connecting to the Database)
To get started you will need:
- A Python interpreter (I am using BeOpen Python 2.0 for this demo, and I recommend you use it as well)
- Mark Hammonds Win32 Extensions installed into Python (Avaliable from http://www.activestate.com/ASPN/Downloads/ActivePython/Extensions/Win32all)
- Read/Write access to your Brain (I assume you got this one covered)
The first step that you must take, is to import the 'dbi' module and the 'odbc' module. I assume you know how
to import a module in Python, but if you don't here's how it looks:
>> import dbi
>> import odbc
Be careful to import the 'dbi' module before the 'odbc' module. Not importing the 'dbi' module before the
'odbc' module is known to cause the import of the 'odbc' module to fail. Now we have all the modules we need.
To connect to a database, you must first have an ODBC data source. Typically, you would need to add the data
source to the ODBC Data Source Administrator. This tool can be found in the Control Panel, or in Windows 2000
under Administrative tools. That is another one of those things that I assume you already know. Since we are
using ODBC, you can connect to a host of different database such as Oracle, Microsoft Access, dBase, Progress,
MySQL, and others. Assuming you have added a data source, and know its name, you are now ready to access it with
Python. For this example, I will have an ODBC data source named 'Contacts' which has the following schema:
TABLE -> People
id - integer (auto increment)
name - character(80)
age - integer
email - character(80)
To connect to our database - which has a username called "Mark" and a password called "secret", we can do
the following:
>> Con = odbc.odbc("Contacts/Mark/secret")
In case your database isn't protected by a username/password, you can just use:
>> Con = odbc.odbc("Contacts")
If you are an unfortunate fellow, there is a chance that the previous line of code will give you an error that
says "dbi.operation-error[Microsoft][ODBC Driver Manager] Data source name not found........". Never fear,
9 times out of 10 the error means that you just mispelled the data source name. Go back into the ODBC Data
Source Administrator and make sure you have the correct spelling. To the best of my knowledge, ODBC Data
Source names are case insensitive. If this works, you will have a Connection object called 'Con'.
Part Three: Up and Running with ODBC under Win32 (Lets do something usefull now)
If you investigate this new 'Con' object you will notice that it contains a few items: "close", "commit",
"cursor", "rollback", and "setautocommit". For our purposes, right now we will only use "cursor". We may
also use "close", but it is self explanitory. In order to be able to do anything useful with the database,
we must create a Cursor object. To create a Cursor object, use the following code:
>> Cursor = Con.cursor()
Now we have a Cursor object called 'Cursor'. If we examine the object 'Cursor' we find a bunch of items:
"close", "execute", "fetchall", "fetchmany", "fetchone", "setinputsizes", and "setoutputsize". For now,
none of this is important to us except for methods "fetchall" and "execute".
Ok, time for some imagination. Lets say our Contacts database contains 3,000 contacts. Let say we wanted to
select every Contact that is over 19. Here's how we would do it, now that we have a Cursor we can do the
following to execute an SQL statement on it:
>> Cursor.execute = "SELECT * FROM People WHERE age > 19"
Right then and there, we have executed an SQL statement on the database. I am not here to teach you SQL. If
you don't know SQL, please learn it and come back later. Because we have used a 'SELECT' statement, we obviously
should be expecting some sort of result. Here is one way that we can 'fetch' the results:
>> Results = Cursor.fetchall()
The previous statement basically puts all the results returned by the SQL statement into one variable.
"Fetchall" is defined by the Python Database API as fetching "all (remaining) rows of a query result".
The cursor object contains a couple of other methods for fetching data. These methods are defined in the
Python Database API and include "fetchone" and "fetchmany". "Fetchone" could be described as fetching "the
next row of a query result set", while "Fetchmany" could be described as fetching "the next set of rows of
a query result". For "Fetchmany", the number of rows to return is specified by parameter (ie Cursor.fetchmany(10),
would fetch 10 rows).
In simple terms, the Results variable is a list of records that matched the 'SELECT' statement. Each of those
records contains a list of fields. In more technical terms, it returns a list of tuples. To print the first field
of the second record (remember, we count from 0, not 1) we would do the following:
>> print Results[1][0]
While that simple example of accessing the Results is fine and dandy, we usually can't deal with data results
in such a hard coded way. If we wanted to go through each Contact (that was returned from the SQL statement
because they are over 19), and print their e-mail, we could use a for loop in the following way:
>> fldEmail = 3
>> for Contact in Results:
>> print Contact[fldEmail]
Of course, we only covered the 'SELECT' statement, statements such as 'INSERT' and 'UPDATE' are normally easier
(and therefore won't be explained) because we don't even have to fetch any results. Again, I am not here to
teach you database theory, just the Python DBI.
Part Four: Ensuring Smooth Operation
Nothing is more important to mission critical systems than a solid and stable connection to a database. I would
venture to say that the layer between an application and the database is what fails most often. That being said,
it is vital that we at least have some confidence that we will have a minimum amount of difficulties and problems.
However, as I write about this, I recall again that this article is not ment to teach database theory nor database
administration. I will try to keep this section as specific to ODBC, Python, and its DBI as I possibly can.
The code examples that I gave earlier in this article are pretty simple. Thats a good thing when you are trying
to demonstrate something simple. Typically real live examples would be more complex. They would also have error
handling. A simple way to add error handling would be to handle exceptions. One way to do this could be applied
to our connection statement as follows:
>> try:
>> Con = odbc.odbc("Contacts/Mark/secret")
>> except:
>> DoSomethingToFixTheProblemorNotifyUser()
Assuming you are a software developer, I don't think I need to describe the benefit of previous snippet of code.
Part Four: Ensuring Smooth Operation
f course, any experienced developer will roll their eyes and say 'thats it?'. Well, there is more. But I
will let you experiment on your own. There are many things to experiment with. There is a popular library
called mxODBC that may be worth checkign out. If you
really want to soak in all you can about database access in Python you could stuff the Python Database API
Specification version 2.0 (http://www.python.org/topics/database/DatabaseAPI-2.0.html) under your pillow.
Want to be involved in the Python DB community? Take a look at the Python DB Sig.
Perhaps ODBC is not the best option? Your best bet may be to use database software-specific modules based
on the Python DBI such as the MySQLdb module.
|
