using access databases from python

[Steve Holden]

"Christopher N. Deckard" <cnd at ecn.purdue.edu> wrote in message
news:3C0783DB.BFB3CAC6 at ecn.purdue.edu...
> Steve Holden wrote:
> >
> > [posted & mailed]
> >
> > "Christopher N. Deckard" <cnd at ecn.purdue.edu> wrote ...
> > > Do to M$ Access's inability to correctly export data from its
> > > database, I am searching for other ways to dump an access database
> > > to tab delimited files.  I was wondering if there is an access
> > > database python module.  Even if it only works on Windows, that's
> > > fine.  I just need to get data out of Access to put it into a
> > > Postgres database.
> > >
> > You should be able to do considerably better than transfer via
tab-delimited
> > files.
>
> Yes.  I know.  :-)
>
> > You can access both PostgreSQL and Access through the Python DB API. I
> > haven't actually used PostgreSQL in anger yet, but there are at least
three
> > modules for it. I usually use mxODBC to access Jet databases. It's
freeware
> > for personal use, though you have to buy a license for commercial
> > applications.
>
> Great.  I will look at that.  I guess I can read the license, but is
> it ok for use by educational institutions?
>
Look at the license :-)

Seriously, I doubt legitimate academic use will violate the freeware terms,
but I didn't write the license, and have only scrutinized it to the extent
that I know my uses are allowed.

> > > If something like that doesn't exist, how else can I do it?
> > >
> > You'll need to take a look at the DB API documentation, which you'll
find
> > under the DB SIG's area of www.python .org. It's not that hard to use.
> >
> > To give you some idea of exactly how simple database transfer is, here's
a
> > program I used to copy a small Access database into Gadfly so I could
test
> > an application on that platform. Only one table is involved, but the
> > principles would be the same for any number. In this particular program
I
> > used the odbc module which comes with the Win32 extensions. I was lucky:
> > often program will fail if you don't import the dbi module before
importing
> > odbc.
>
> Thanks for the example.  Does this only work on Windows?  My real
> end goal is to write an importer product for Zope.  User uploads
> access database, product does all of the inserts and table creation
> if needed.
>
[Example snipped]

Well, generally speaking, it's difficult to access Jet engines from other
than Windows platforms. I'm not aware of a remote ODBC Jet driver (though
that doesn't mean one doesn't exist). However, there should be nothing to
stop you using a PostgreSQL driver that can handle remote connections from a
Windows client. So the answer is, you'll need a Windows machine to run the
program on, so that you can get at the Access database, but there's probably
nothing stopping you from using a PostgreSQL server on some other host on
your network.

regards
 Steve
--
http://www.holdenweb.com/