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
WinXP and newer comes with a MS Access ODBC driver out of the box. I’ve been using that along with pyodbc to work with *.mdb without actually installing Access. I’ll give adodbapi a try with the same files and queries to see if there’s a speed difference.
SQLAlchemy is pretty sexy, can that be used with these sort of files ?
Thanks for the info. Were you able to determine which was faster?
I did a couple of small tests, one simple select and one select with a couple of inner joins. In these tests adodbapi ran about 10% faster than pyodbc.