Reading data from a Microsoft Access 2003 database

[Jimoid]

Hi All,

I use Ubuntu 64 bit and need to develop a programme (ideally in
Python) to work on data that is contained in a Microsoft Access 2003
database. I do not need to modify the database, simply read a few
columns of data from some tables.

Google hasn't been able to offer me a suitable solution to this
problem, and although there are some references to this problem in the
archives of this group, they are mainly very old and I wonder if there
is anything more recent that members could suggest to me?

Python would be my language of choice, however if someone has any
other suggestions then I'm open to ideas.

TIA,

Jimmy

 

[Simon Brunning]

I use Ubuntu 64 bit and need to develop a programme (ideally in
Python) to work on data that is contained in a Microsoft Access 2003
database. I do not need to modify the database, simply read a few
columns of data from some tables.

mxODBC might well be what you are looking for,

 

[Ahmed, Shakir]

-----Original Message-----
From: [email protected]
[mailto[email protected]] On Behalf Of
Simon Brunning
Sent: Wednesday, May 19, 2010 6:13 AM
To: python-list
Subject: Re: Reading data from a Microsoft Access 2003 database

I use Ubuntu 64 bit and need to develop a programme (ideally in
Python) to work on data that is contained in a Microsoft Access 2003
database. I do not need to modify the database, simply read a few
columns of data from some tables.

mxODBC might well be what you are looking for,

--
Cheers,
Simon B.
--

Or you can use pyodbc
DBfile = '/path/*.mdb
conn = pyodbc.connect('DRIVER={Microsoft Access Driver
(*.mdb)};DBQ='+DBfile, autocommit=True)
cursor = conn.cursor()

Thanks
Shakir

 

[Les Schaffer]

I use Ubuntu 64 bit and need to develop a programme (ideally in
Python) to work on data that is contained in a Microsoft Access 2003
database. I do not need to modify the database, simply read a few
columns of data from some tables.

this worked like a charm for me:

http://code.activestate.com/recipes/528868-extraction-and-manipulation-class-for-microsoft-ac/

Les

 

[Adam Tauno Williams]

this worked like a charm for me:
http://code.activestate.com/recipes/528868-extraction-and-manipulation-class-for-microsoft-ac/

The OP: "I use Ubuntu 64 bit"

Does something that contains "import win32com.client" work on anything
other than Windows?

 

[Adam Tauno Williams]

Hi All,
I use Ubuntu 64 bit and need to develop a programme (ideally in
Python) to work on data that is contained in a Microsoft Access 2003
database. I do not need to modify the database, simply read a few
columns of data from some tables.
Google hasn't been able to offer me a suitable solution to this

Google is a poor way to look for such things. It is better to go to
Freshmeat / SourceForge or PyPI directly.

Try <http://freshmeat.net/search?q=microsoft+access&submit=Search>

mdbtools is your best bet unless you are willing to pay for the EasySoft
drivers. I've had mixed success with mdbtools; if all you need to do
is extract data their ODBC driver will probably work.

If you are willing to use Jython (Python on the JVM) you have a *lot*
more options for this kind of thing.

 

[Les Schaffer]

The OP: "I use Ubuntu 64 bit"

woops, my bad ...

 

[Jimoid]

Thanks to all for your helpful suggestions.

So far I have installed and played around with mdbtools, and it
appears that I can use this from the shell to extract the information
I need, e.g. mdb-export for dumping an entire table or mdb-sql to run
a query. I am also interested by mxODBC and pyodbc as these seem to be
python only solutions, so could be useful for platform independent
python solutions (it may be that my programme will be used by some
Windows users, if it is successful).

To give it a bit of context, I am extracting data from the on-board
flight planning software from an aerial survey plane (the Access
database). I will extract the GPS data for each image taken so that I
can then georeference the images.

Cheers,

Jimmy

 

[Jimoid]

I've now had a closer look at both pyODBC and mxODBC and it seems to
me that they both require the database to be running to be able to
query it. Is this correct? If so I think I will have to use mdb-* as
the database I want to query is not running.

Cheers,

Jimmy

 

[Dennis Lee Bieber]

I've now had a closer look at both pyODBC and mxODBC and it seems to
me that they both require the database to be running to be able to
query it. Is this correct? If so I think I will have to use mdb-* as
the database I want to query is not running.

To my knowledge, all "ODBC" modules (in any language) rely upon a
DBMS specific driver. Where the DB-API definition defines the common
Python side of a database interface, ODBC defines a common OS-level
interface to the DBMS, using a named driver (driver specified in the
connection string or, for Windows, externally with the connection string
specifying the connection definition) to translate ODBC calls into
actual DBMS calls.

 

[Adam Tauno Williams]

To my knowledge, all "ODBC" modules (in any language) rely upon a
DBMS specific driver.

Correct (of course, so does DB-API if you are using a 'native' Python
connection).

Where the DB-API definition defines the common
Python side of a database interface, ODBC defines a common OS-level
interface to the DBMS, using a named driver (driver specified in the
connection string or, for Windows, externally with the connection string
specifying the connection definition)

There is no distinction between how ODBC operates on Windows or LINUX.
Both use the same connection strings [provided the drivers have the same
names].

to translate ODBC calls into actual DBMS calls.

Yes, just like a DB-API provider, except that the 'translation' happens
in the ODBC driver.

 

[Gregory Ewing]

I've now had a closer look at both pyODBC and mxODBC and it seems to
me that they both require the database to be running to be able to
query it. Is this correct?

[/QUOTE][/QUOTE]

If you mean that an instance of MS Access has to be running,
no, I don't think so. The ODBC driver for Access databases
knows how to open .mdb files itself. I've done this myself
recently, and it seems to work fine without having Access
running anywhere.

 

[Dennis Lee Bieber]

If you mean that an instance of MS Access has to be running,
no, I don't think so. The ODBC driver for Access databases
knows how to open .mdb files itself. I've done this myself
recently, and it seems to work fine without having Access
running anywhere.

Just a minor clarification -- Access is really just a GUI
report/form creation tool... The .mdb files are JET databases; JET being
the engine... Heck, a M$ Access Project may not even use JET, it just
uses Access to act as a front-end for M$ SQL Server. (And via ODBC,
Access can, well, access Visual FoxPro table files -- a feature I've
used to browse the database used by The Master Genealogist)

 

[Jimoid]

This is perfect if the python ODBC driver can read the .mdb without
the need for it to be running elsewhere.

Thanks all for your help.

 

[Dennis Lee Bieber]

This is perfect if the python ODBC driver can read the .mdb without
the need for it to be running elsewhere.

The "Python ODBC" driver just "talks" ODBC... you need a
system/database ODBC back-end that talks to the database/file.

SQL (in Python) => ODBC DB-API (Python) => system ODBC (named in
connection string) => database/file

On Windows, an ODBC connection specifies the back-end driver... An Excel
connection to TMG (genealogy) database reads:
-=-=-=-=-
XLODBC
1
Driver={Microsoft Visual FoxPro Driver};
UID=;
SourceDB=e:\UserData\Dennis Lee Bieber\My
Documents\Genealogy\TMGv7\Bieber;
SourceType=DBF;
Exclusive=No;
BackgroundFetch=Yes;
Collate=Machine;
Null=Yes;
Deleted=Yes;
SELECT `bieber family_g`.etype, `bieber family_g`.dsid as 'DataSet',
`bieber family_t`.etypename as 'Event' FROM `bieber family_g`, `bieber
family_t` WHERE `bieber family_g`.dsid = `bieber family_t`.dsid AND
`bieber family_g`.etype = `bieber family_t`.etypenum


etype DataSet Event
-=-=-=-=-
{Note: I put in the new-lines after each ; above, the actual mess is a
single line string}

Not how the first main item specifies the back-end driver that has
to be loaded to handle the database.

To my knowledge, all language-side ODBC adapters require separate
database-side ODBC drivers.