PyWin32: adodbapi and MS Access

Last week, there was an interesting thread on the PyWin32 mailing list about how to read Microsoft Access databases with Python without having Access actually installed. Vernon Cole had the solution, but I noticed that Google doesn't seem to index the PyWin32 list very well, so I decided to write about it here.

I took his code and modified it slightly to make it even more explicit and I put together a lame database file with Microsoft Access XP (downloadable below). The adodbapi module's (not to be confused with the adodb module) source distribution also includes a test database in its "test" folder that you can use as well. Anyway, here's the code:

import adodbapi

database = "db1.mdb"
constr = 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s'  % database
tablename = "address"

# connect to the database
conn = adodbapi.connect(constr)

# create a cursor
cur = conn.cursor()

# extract all the data
sql = "select * from %s" % tablename
cur.execute(sql)

# show the result
result = cur.fetchall()
for item in result:
    print item
    
# close the cursor and connection
cur.close()
conn.close()

This code was tested on the following:

• Windows XP Professional with Python 2.5.4 and adodbapi 2.4.0 with Microsoft Access installed
• Windows 7 Home Premium (32-bit) with Python 2.6.4, adodbapi 2.2.6, without Microsoft Access

Downloads

• adodbapi_access.zip