Using an Access database (.mdb) with Python on Ubuntu [duplicate]

7
votes

I'm trying to use pyodbc to access a .mdb on Ubuntu. I've based my progress so far on this link

Query crashes MS Access

I have installed pyodbc, unixodbc, and unixodbc-dev

My code looks like this:

import csv
import pyodbc

MDB = 'URY.mdb'
DRV ='Microsoft Access Driver (*.mdb)'
PWD = 'pass'

conn = pyodbc.connect('DRIVER=%s;DBQ=%s;PWD=%s' % (DRV,MDB,PWD))
curs = conn.cursor()

When I run it, I receive this error message:

Traceback (most recent call last):
  File "mdbscraper.py", line 8, in <module>
    conn = pyodbc.connect('DRIVER=%s;DBQ=%s;PWD=%s' % (DRV,MDB,PWD))
pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found,         and no default driver specified (0) (SQLDriverConnect)')

Does anyone have any ideas? Any help would be very much appreciated

Thank you!

2
pythonms-accessubuntupyodbc
I'm not familiar with Linux, but on Windows, this error would mean that you did not set up an ODBC connection in Control Panel -> Administrative Tools -> ODBC Connections. And you'd pass the Data Source Name (DNS) in the connection, not the name of file (that's in the DNS). – HardCode
I think because I'm using a .mdb it's all local, so I don't need a DNS. Does that sound right? I'm basing it on the example at the top of this page code.google.com/p/pyodbc/wiki/ConnectionStrings – mythander889
I think you still need to install an ODBC driver for MS Access. Easysoft sells one. easysoft.com/products/data_access/odbc-access-driver/index.html Supposedly mdbtools includes one but it's described as limited. mdbtools.sourceforge.net/install/x90.htm – HansUp
Thank you. I found the necessary driver on their website as a tar. Do you know where I need to put it? – mythander889
I never tried it. See their getting started guide. easysoft.com/products/data_access/odbc-access-driver/… – HansUp

2 Answers

5
votes

From what I know this driver "Microsoft Access Driver (*.mdb)" is only available on a Microsoft host, since you are on ubuntu, it won't work.

-3
votes
import pyodbc 
DBfile = '/data/MSAccess/Music_Library.mdb'
conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+DBfile)
cursor = conn.cursor()
SQL = 'SELECT Artist, AlbumName FROM RecordCollection ORDER BY Year;'

for row in cursor.execute(SQL): # cursors are iterable
print row.Artist, row.AlbumName

cursor.close()
conn.close()

There is The Official Example .. of use ...