using access databases from python

[Steve Holden]

[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.

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.

> 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.

#
# Create the gadfly database from the "Webdata" ODBC source
#
import gadfly
import odbc

ocn = odbc.odbc("Webdata")
ocu = ocn.cursor()

gcn = gadfly.gadfly()
gcn.startup("webdata", "gadfly")
gcu = gcn.cursor()

gcu.execute("create table PgData (PgName varchar, PgNum integer, PgText
varchar)")
ocu.execute("select PgName, PgNum, PgText from PgData")
gcu.execute("insert into PgData (PgName, PgNum, PgText) values (?, ?, ?)",
                ocu.fetchall())
gcn.commit()

for obj in (gcu, ocu, gcn, ocn):
    obj.close()

print "gadfly database created"

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