Microsoft Access read from linux python

[David ROBERT]

Hello,

I need to read data from a MS Access database.
The program (reader) is installed on a linux box and
is written in python langage.
The database is MS Access 2002 installed on a Win XP box
networked with the linux box.

Is it possible for python/linux to read data from MS Access
Database ?

I've read a lot of things about mxODBC, iODBC and so on,
but I'm still very confused, It looks like it is _not_
possible.

Ideas ? experiences ?

David ROBERT.

 

[Mark Carter]

Hello,

I need to read data from a MS Access database.
The program (reader) is installed on a linux box and
is written in python langage.
The database is MS Access 2002 installed on a Win XP box
networked with the linux box.

Is it possible for python/linux to read data from MS Access
Database ?

I've read a lot of things about mxODBC, iODBC and so on,
but I'm still very confused, It looks like it is _not_
possible.

Ideas ? experiences ?

AFAIK, Python IS able to access MS Access 2002 from Windows. You will
need to install Python on Win XP, and install Mark Hammond's win32all.
It can interface with any Microsoft COM component, which includes MS
Office programs.

You will need to write a Python script on Win XP that interrogates an
Access database. Then you'll need to write some Python networking
stuff to connect Win XP to Linux (maybe using XML-RPC).

This assumes that you can't just export Access to a more open
database, and ditch Access.

 

[Cameron Laird]

AFAIK, Python IS able to access MS Access 2002 from Windows. You will
need to install Python on Win XP, and install Mark Hammond's win32all.
It can interface with any Microsoft COM component, which includes MS
Office programs.

You will need to write a Python script on Win XP that interrogates an
Access database. Then you'll need to write some Python networking
stuff to connect Win XP to Linux (maybe using XML-RPC).

This assumes that you can't just export Access to a more open
database, and ditch Access.

.
.
.
You can also get an ODBC driver, and run a Linux-hosted ODBC
client. I haven't done it with Python, but it ought to be
straightforward. Alex has probably seen at least the parts
of such an arrangement working.

 

[Alan Kennedy]

[David ROBERT]

I need to read data from a MS Access database.
The program (reader) is installed on a linux box and
is written in python langage.

Hmmm. What is "the program"? Is it a python script you have written?
Or that someone else has written and you must update? What is the
"reading" that it does? How does it do it currently? DOes it run on a
non-linux operating system?

A python client running on linux can access any database on any server
operating system if one can obtain an ODBC driver for that database.
Both server and client need to understand ODBC, which is a kind of
universal database protocol/API.

The database is MS Access 2002 installed on a Win XP box
networked with the linux box.

If you can get an ODBC "data source" set up for that database, then
you're half way there. On Win 2K (dunno about XP) Go to "Control
Panel"->"Administrative Tools"->"Data Sources (ODBC)", and add a "DSN"
for your MS Access database. There are security/privacy concerns
related to informed choice of "System DSN" vs. "User DSN", which I'll
leave for you to read up on.

Is it possible for python/linux to read data from MS Access
Database ?

Yes, if the linux client supports ODBC. It's also possible to make
modifications back to the database.

Python has a standardised database API called "DB-API", which has very
wide native support for a lot of different databases. A number of the
currently available DB-API modules also support ODBC, which you can
see on this page

http://www.python.org/topics/database/modules.html

As far as I'm aware, most or all of the modules that support ODBC will
run on linux.

I've read a lot of things about mxODBC, iODBC and so on,
but I'm still very confused, It looks like it is _not_
possible.

Anything is possible. It's just a matter of effort ;-)

 

[Ben Finney]

I've read a lot of things about mxODBC, iODBC and so on,
but I'm still very confused, It looks like it is _not_
possible.

One thing to be wary of is that there is no ODBC module in Python, so
you will need to read the copyright license of any third-party module
you choose to use.

The mxODBC module (as made by eGenix) is certainly not licensed as free
software:

<http://www.egenix.com/files/python/mxODBC.html#Copyright>

 

[francois lepoutre]

Hi David

I've read a lot of things about mxODBC, iODBC and so on,
but I'm still very confused, It looks like it is _not_
possible.

Ideas ? experiences ?

It is of course possible... There are typically two ways into it
as explained by Mark and Cameron.

The solution by mark is more unixian in kind. The one by cameron
using straight odbc is more win32-ish flavour in style.

With only limited background in system admin, I have been
quite happy with odbc and mxodbc for a couple of months now.
On both win and linux boxes.

So I'd recommend that you use odbc as an insulation layer between
the MS connectivity stuff and your application. odbc is pretty stable now.

Installation is not as straightforward on linux as on win32 where it's
plain standard stuff but once set up, the stuff is clearly solid and
workable.

In the end you need a db middleware layer. The choice is yours.
Go straight into win32 com and build then your own insulation thing
using xml-rpc or rely on odbc all along the line.

The choice will depend on your background and is, in the end, only
a matter of choice.

Francois

 

[M.-A. Lemburg]

Hello,

I need to read data from a MS Access database.
The program (reader) is installed on a linux box and
is written in python langage.
The database is MS Access 2002 installed on a Win XP box
networked with the linux box.

Is it possible for python/linux to read data from MS Access
Database ?

I've read a lot of things about mxODBC, iODBC and so on,
but I'm still very confused, It looks like it is _not_
possible.

Ideas ? experiences ?

It is certainly possible. Here are some pointers to get you started:

EasySoft's ODBC-ODBC bridge:

http://www.easysoft.com/

MDBTools ODBC driver:

http://forums.devshed.com/archive/46/2002/06/4/37357
http://mdbtools.sourceforge.net/

ODBC Socket Server:

http://odbcsock.sourceforge.net/

For a version with transaction support see UniverSQL:

http://www.sidespace.com/products/universql/

The setup is as follows:

Python -> mxODBC -> ODBC driver/bridge/gateway -> data source

The bridge and socket server basically interface the ODBC API
from Unix to Windows, so you are talking to the Windows
ODBC driver for MS Access. The MDB tools work directly
on the access files; not sure whether Access 2000 is supported
though.

BTW, if you can, I'd suggest to switch from Access to the
MS Desktop Engine (MSDE) 2000 which is basically MS SQL Server
2000 without the GUI tools and available as free download
from MS. You can use the FreeTDS ODBC driver to talk to it
from Linux or one of the many available commercial ODBC
drivers to access MS SQL Server from Linux.

HTH,
--
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source (#1, Oct 30 2003)________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::

 

[Mark Roach]

I need to read data from a MS Access database.
The program (reader) is installed on a linux box and
is written in python langage.
The database is MS Access 2002 installed on a Win XP box
networked with the linux box.

Is it possible for python/linux to read data from MS Access
Database ?

mdbtools: http://mdbtools.sourceforge.net/ provides a unixODBC
driver for MS access databases. Install it and mxODBC, then you
should be able to create a DSN for the access db, and connect to
it using mxODBC

I've read a lot of things about mxODBC, iODBC and so on,
but I'm still very confused, It looks like it is _not_

What exactly are you confused about? Perhaps someone can help
answer some specific questions...

-Mark

 

[Alan Kennedy]

[David ROBERT]
[Ben Finney]

One thing to be wary of is that there is no ODBC module in Python, so
you will need to read the copyright license of any third-party module
you choose to use.

The mxODBC module (as made by eGenix) is certainly not licensed as free
software:

<http://www.egenix.com/files/python/mxODBC.html#Copyright>

I meant to reply to this last week, but forgot.

There is a completely free (in all senses of the word) and open-source
ODBC module for python: It is Brian Zimmer's/Ziclix zxJDBC.

Of course, you're not going to like that either Ben, because it's in
Java and runs under jython.

But it is a free python ODBC library.

kind regards,

 

[Paul Rudin]

> [David ROBERT]

> [Ben Finney]

>> One thing to be wary of is that there is no ODBC module in
>> Python, so you will need to read the copyright license of any
>> third-party module you choose to use.
>>
>> The mxODBC module (as made by eGenix) is certainly not licensed
>> as free software:
>>
>> <http://www.egenix.com/files/python/mxODBC.html#Copyright>

> I meant to reply to this last week, but forgot.

> There is a completely free (in all senses of the word) and
> open-source ODBC module for python: It is Brian Zimmer's/Ziclix
> zxJDBC.

> Of course, you're not going to like that either Ben, because
> it's in Java and runs under jython.

> But it is a free python ODBC library.

There's also an odbc module with the win32all thing, which is free.

 

[Alan Kennedy]

[Alan Kennedy]
[Paul Rudin]

There's also an odbc module with the win32all thing, which is free.

Thanks Paul, I wasn't previously aware of that.

So it seems the only people without access to a free (in all senses of
the word) python ODBC driver are those who won't use Windows or Java
........

regards,