See All Titles |
![]() ![]() The ODBC ModuleODBC (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:
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:
ODBC Example for Windows PlatformsThe 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:
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'}") mxODBCmxODBC 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 calldllYou 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. unixODBCunixODBC 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 PagesThe 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
|
Index terms contained in this sectionaccessingdatabases 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. |