ODBC Module

The ODBC module was kindly donated to the public domain by EShop. The EShop guys are still around, although in a new disguise - unfortunately, they can not support these modules, so don't bother looking for them!

If you are having problems, please jump straight to the known bugs section.

[More flesh to be added - any volunteers?? Is the DatabaseAPI.html worth pointing to? Where is it?]

Sample Code

Thanks to Hirendra Hindocha for the following code sample.

import dbi, odbc
try:
        s = odbc.odbc('DSN/UID/PASSWORD')
        cur = s.cursor()
        cur.execute('select * from discounts')
        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'

Sample Code 2

Again, thanks to Hirendra Hindocha for the following code sample.

import dbi, odbc
import time
# %D% %T%
class RTable:
        def __init__(self,dsn,sql):
                try:
                        s = odbc.odbc(dsn)
                        print dsn,sql
                        self.cur = s.cursor()
                        self.cur.execute(sql)
                except NameError,e:
                        print 'error ', e, 'undefined'
        def __len__(self):
                pass
        def __setitem__(self,key,val):
                pass
        def __getitem__(self,index):
                rec = self.cur.fetchone()
                if not rec:
                        raise IndexError, "index too large"
                return rec
class RRec:
        def __init__(self,rec,description):
                self.record = []
                if not rec: return 
                i = 0
                for field in rec:
                        if description[i][1] == 'DATE':
                                local = time.localtime(field.value)
                                s = str(local[1]) + '/' \
                                        + str(local[2]) + '/' +str(local[0])
                                setattr(self,description[i][0],s)
                                self.record.append(s)
                        elif description[i][1] == 'RAW':
                                dummy = 'RAW Not Implemented'
                                setattr(self,description[i][0],dummy)
                                self.record.append( dummy)
                        else:
                                setattr(self,description[i][0],field)
                                self.record.append(field)
                                i = i+1
                                self.data = self.record
        def dump(self):
                for i in self:
                        print i,
                print
        def __len__(self):
                return len(self.record)
        def __setitem__(self,key,val):
                self.record[key] = val
        def __getitem__(self,index):
                return self.record[index]
if __name__ == '__main__':
        print 'script as main'
        #sqlstmt = 'select * from shippers'
        #sqlstmt = 'select * from orders'
        #sqlstmt = 'select * from categories'
        # sqlstmt = '\
        # SELECT Employees.EmployeeID, Employees.LastName,\
        # Employees.FirstName, Employees.Title, Employees.TitleOfCourtesy,\
        # Employees.BirthDate, Employees.HireDate, Employees.Address,\
        # Employees.City, Employees.Region, Employees.PostalCode,\
        # Employees.Country,\
        # Employees.HomePhone, Employees.Extension, Employees.Notes,\
        # Employees.ReportsTo\
        # FROM Employees'
        sqlstmt = '\
SELECT EmployeeID, LastName,\
FirstName, Title, TitleOfCourtesy,\
City, Region, PostalCode,\
Country,\
HireDate, Address,\
HomePhone, Extension, Notes,\
ReportsTo\
FROM Employees'
        dsn='nwin/admin/'
        s = RTable(dsn,sqlstmt)
        for rec in s:
                b = RRec(rec,s.cur.description)
                for field in b:
                        print field,
                print

Known Bugs/Limitations

1. The DBI module must be imported before odbc. Not doing this will cause the import of odbc to fail the first time.
2. Dates before the epoch don't bind through. (Jan 1 1970, 00:00:00 GMT) In fact, using these dates may cause the module to crash.
3. The maximum length on a dbiRaw type column is 64k from the database to Python. There is no limitation from Python to the database.

Hirendra Hindocha also reports: inserting a single row into a table doesn't work properly unless one specifically deallocates the cursor.
for example the following code snippet -

[Note however, Bill Tutt seems to think that this problem implies a problem with the specific ODBC driver, rather than with the ODBC implementation of Python. I havent a clue!]

conn = odbc.odbc(str)
cur = conn.cursor()
sql = 'insert into abc_table values(1,2,'abc')
cur.execute(sql)
conn.commit()
cur.close()
conn.close()

doesn't work, unless you add the following lines

cur = None
conn = None 

at the end of the above code snippet. Tracing with ODBC and a look into odbc.cpp shows that sqlfreestmt is not being called until the explicit deallocation is done.