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