< BACKMake Note | BookmarkCONTINUE >
152015024128143245168232148039199167010047123209178152124239215162147045098084001118189099

The ODBC Module

ODBC (Open Database Connectivity) is a standard interface created by Microsoft; hence, it is fully supported by the Windows platform. It provides access to almost every database. Currently, the ODBC implements the ANSI standard SQL3.

To configure the ODBC settings for a database in your Windows system, you must use the ODBC Data Source Administrator, which is located at the Windows Control Panel.

The two major advantages of choosing to code an application to the ODBC API are as follows:

  • Portable Data Access Code—  The ODBC API is available on all major databases.

  • Dynamic Data Binding—  This allows the user or the system administrator to easily configure an application to use any ODBC compliant data source. This is perhaps the single biggest advantage of coding an application to the ODBC API. Dynamic binding allows the end user to pick a data source—that is, an SQL Server—and use it for all data applications without having to worry about recompiling the application. The ODBC module implements the Python DB API, so you can get this level of abstraction at the DB API level. Also, you don't explicitly recompile Python code.

EShop kindly donated the ODBC module to the public domain. This module is included in the PythonWin distribution. For more details, check out the site at http://www.python.org/windows/win32/odbc.html.

The next example shows how you can open a ODBC connection using Python.

					
import dbi, odbc
try:
        connection = odbc.odbc('DSN=mydatabase;UID=mylogin;
        PASSWORD=mypassword')
        cursor = connection.cursor()
        cursor.execute('select name, email from USERS')
        while 1:
                 record = cursor.fetchone()
                 if not record: break
                 print record
        connection.close()
except NameError,e:
        print 'NameError: ', e

				

Three ways (at least) to access ODBC from Python on the Windows platform are as follows:

  • DB API—  Python Database API

  • calldll—  Sam Rushing's calldll module

  • DAO—  Microsoft Data Access Objects

ODBC Example for Windows Platforms

The first thing you need is to create a DSN for your database in the ODBC Data Source Administrator.

The PythonWin distribution comes with an odbc module, which by the way is very stable. However, it is no longer going to be improved. This odbc module works along with the dbi module. Both files conform to the Version 1.0 of the Python Database API, providing a minimum implementation.

The whole ODBC functionality is made up of two extension files:

  • odbc.pyd—   The odbc module itself

  • dbi.pyd—   The database independence utilities module

The dbi module must be imported before you import the odbc module.

						
import dbi, odbc, pprint
connection = odbc.odbc('DSN=mydatabase;UID=myuser;PWD=mypassword')
cursor = connection.cursor()
cursor.execute('SELECT name, email FROM USERS')
data = mycursor.fetchall()
cursor.close()
connection.close()
pprint.pprint(data)
[('andre','andre@bebemania.com.br'), ('renata', None)]

					

Let's see some of the functions and attributes exposed by the odbc connection and cursor objects.

						
fetchall()             # fetches all the rows
fetchone()           # fetches only one row
fetchmany(n)      # fetches n number of rows
mycursor.arraysize# number of rows fetched.
mycursor.description# structure of the cursor

					

mycursor.execute() supports DML and DDL. However, it doesn't support prepared statements.

The dbi module handles both date and time formats. All date results are returned as dbi date objects.

						
>>> pprint.pprint(data)
 [('col1', <DbiDate object at 12e4b34>)]
>>> dateobj = data[0][1]
>>> dateobj
<DbiDate object at 12e4b34>
>>> int(dateobj)
984046200
>>> str(dateobj)
'Fri Jun 02 00:00:00 2000'
				
					

The next command shows the preferred way to pass date values back to the ODBC driver because this is the standard ODBC syntax for embedding dates in SQL strings.

						
mycursor.execute("UPDATE tablename SET columnname={d '1999-04-15'}")
				
					

mxODBC

mxODBC is an extension package created by Marc-André Lemburg that exposes interfaces to ODBC 2.0 database drivers. This package implements the standard Database API. Among other things, it supports more than one database per process and it has preconfigured scripts for MySQL, Oracle, Informix, and more. This package exposes an odbc module for both Windows and UNIX. One of the most important differences between this module and the one that comes in the PythonWin distribution might be that this one supports prepared statements, hence, you can separate the SQL structure from the actual values. The engine parses a statement once, creates a handle for it. After that, you just need to pass the correct parameters that should be used for each interaction.

This package also possess an enhanced set of date and time types for moving data between both Windows and UNIX systems. You can blame the mxDateTime package for that. The mxDateTime package might become part of the mxODBC package in the near future. Check it out at

http://starship.python.net/crew/lemburg/mxODBC.html

calldll

You can also use the calldll package, developed by Sam Rushing, to call the functions that are part of the Microsoft ODBC DLL. One problem with using this DLL is that it doesn't have any similarity to the Python DB API. Another problem is that if you call the ODBC functions with the wrong arguments, your program might fail. The function calls have a low-level interface that doesn't handle exceptions as nicely as Python does. For more information, check out http://www.nightmare.com/software.html.

Caution

This is one of the most dangerous ways to access databases. calldll removes almost all the safety Python gives you.



unixODBC

unixODBC is a complete, free/open, ODBC solution for UNIX/Linux. The unixODBC Project goals are to develop and promote unixODBC to be the definitive standard for ODBC on the Linux platform. This is to include GUI support for KDE. For more information, check out http://www.unixODBC.org.

Other Interesting ODBC Web Pages

The next few links introduce some interesting material that you can use to understand and use ODBC techniques.

ODBC Hints—by John Dell'Aquila

http://www.python.org/windows/OdbcHints.html

Full ODBC manual

http://www.solidtech.com/developer/documentation.html


Last updated on 1/30/2002
Python Developer's Handbook, © 2002 Sams Publishing

< BACKMake Note | BookmarkCONTINUE >

Index terms contained in this section

accessing
     databases
            calldll package
applications
      calldll 2nd
      mxDateTime
      mxODBC 2nd
calldll
calldll package
connections
     Open Database Connectivity (ODBC)
            opening
databases
     accessing
            calldll package
      Open Database Connectivity (ODBC) module 2nd 3rd
dbi module 2nd
EShop
      Open Database Connectivity (ODBC) module
Lemburg, Marc-Andr[as]e
modules
      dbi 2nd
      odbc
      Open Database Connectivity (ODBC) 2nd 3rd
mxDateTime package
mxODBC package 2nd
odbc module
Open Database Connectivity (ODBC) module 2nd 3rd
opening
     connections
            Open Database Connectivity (ODBC)
packagesÓ
      Ò
programs
      calldll 2nd
      mxDateTime
      mxODBC 2nd
Rushing, Sam
software
      calldll 2nd
      mxDateTime
      mxODBC 2nd
Windows
      odbc module

© 2002, O'Reilly & Associates, Inc.