See also http://www.python.org/windows/win32/odbc.html for sample
code and notes by Hirendra Hindocha.
Notice that result values are converted to Python objects. Dates in
particular are returned as dbiDate objects. This can be a serious
limitation, because dbiDate can not represent dates prior to the UNIX
epoch (1 Jan 1970 00:00:00 GMT). If you try to retrieve earlier dates,
you'll get garbage and may even provoke a crash.
You'll want to download the win32 source and examine odbc.cpp to see
which features are currently provided. For example, transaction
facilities (setautocommit, commit and rollback) are available, but
SQLDriverConnect (needed to supply extra parameters to the driver) is
not.
If you don't need any of these facilities, you're set. Otherwise, read
on.
II. ODBC using calldll
Sam Rushing's calldll module lets Python call any function in any
DLL. In particular, you can get at ODBC by directly calling functions
in odbc32.dll, and Sam has provided a wrapper module, odbc.py, to do
just that. There is also code for managing data sources, installing
ODBC itself, and creating and maintaining Jet (Microsoft Access)
databases. Poke through the demos and sample code. There are some
amazing things, like cbdemo.py, which has a message loop and window
procedure implemented as Python functions!
[You can find links to the calldll package on Sam's Python Software
page.]
To set up calldll as a package:
1. Install python 1.5 (not 1.4!) as above
2. Get calldll.zip:
ftp://squirl.nightmare.com/pub/python/python-ext/calldll.zip
3. Create a new directory, calldll, under the lib directory, e.g.
c:\Program Files\Python\lib\caldll\
4. Unpack calldll.zip into this directory using WinZip, pkunzip, etc.
5. Move all the files in the calldll\lib\ subdirectory (created by
the unzip) up to the parent (calldll) directory and delete the lib
subdirectory
6. Create the file __init__.py in the calldll directory and put
a suitable comment in it:
# File to allow this directory to be treated as a python 1.5
package.
7. Edit calldll\odbc.py to fix a bug in the get_info facility:
In the functions "get_info_word" and "get_info_long", change
"calldll.membuf" to "windll.membuf"
Here is an example using calldll:
from calldll import odbc
dbc = odbc.environment().connection() # create connection
dbc.connect('sample', 'monty', 'spam') # connect to db
# alternatively, use full connect string:
# dbc.driver_connect('DSN=sample;UID=monty;PWD=spam')
print 'DBMS: %s %s\n' % ( # show DB information
dbc.get_info(odbc.SQL_DBMS_NAME),
dbc.get_info(odbc.SQL_DBMS_VER)
)
result = dbc.query( # execute query & return results
"""
SELECT country_id, name, insert_change_date
FROM country
ORDER BY name
"""
)
print 'Column descriptions:' # show column descriptions
for col in result[0]:
print ' ', col
print '\nFirst result row:\n ', result[1] # show first result row
-------------------------------output--------------------------------
First result row:
['24', 'ARGENTINA', '1997-12-19 01:51:53']
Notice that result values are returned as strings, so dates aren't a
problem, and you have access to SQLDriverConnect in case you need to
supply a connect string. You'll want to examine odbc.py to discover
all the available features. And if you need some facility that isn't
provided, you can easily add it *without* firing up a C compiler.
Suppose, for example, that you need to handle transactions. This
requires the ability to turn off autocommit mode and execute a commit
or a rollback. First, figure out which ODBC functions you need to
call, then add the wrappers to odbc.py, using the existing functions
as guides. When in doubt, experiment! You'll obviously need ODBC
documentation for this. You can download the ODBC 3.0 Programmer's
Reference (in Help file format) or the whole SDK, for free, from
Microsoft: http://www.microsoft.com/data/odbc/download.htm
To handle transactions, we can add the following methods to the
connection class:
def autocommit(self, value=1):
if value:
value = 1 # SQL_AUTOCOMMIT_ON
else:
value = 0 # SQL_AUTOCOMMIT_OFF
return odbc.SQLSetConnectAttr(
self, # connection
102, # attribute = SQL_ATTR_AUTOCOMMMIT
value, # ptr or value
-5 # len = SQL_IS_UINTEGER
)
Notes: 1. SQLSetConnectAttr and SQLEndTran are ODBC 3.0 functions, so
you'll need an ODBC 3.0 Driver Manager (2.x drivers are OK though);
2. The literals are meant to keep the example self-contained, in
practice you should define the appropriate constants in odbc.py
With this code you can now handle transactions:
dbc.autocommit(0) # turn off autocommit
dbc.commit() # commit or rollback as required
dbc.rolback()
Good luck, and if you make any significant extensions to odbc.py, be
sure to share them!
--
John Dell'Aquila <jbd@alum.mit.edu>
import dbi, odbc
try:
s = odbc.odbc('DSNName/Username/Password')
cur = s.cursor()
cur.execute('select * from testTable')
print cur.description
for tup in cur.description:
print tup[0],
print
while 1:
rec = cur.fetchmany(10)
if not rec: break
print rec
except NameError,e:
print 'error ', e, 'undefined'