using access databases from python

[Christopher N. Deckard]

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?  

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

-Chris

> #
> # 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/