Access sql with python, please. I'm desperate

sdd daniels at dsl-only.net
Thu Nov 13 00:07:04 EST 2003


DD wrote:

> Hello,
> But I cannot even connect to the access database (see below).
> Could anyone explain it to me as simple as possible please. I'm using
> Windows XP, ActivePython 2.3.2 build 230 and Microsoft access(XP?)
OK, as several others have said, one solution (and the one I know)
is via ODBC.  I use Win2K, not XP; there will be slight differences.

First, we create and ODBC (Open Data Base Connection) DSN (Data Source Name)
I use (forgive the notation; I don't know a generally accepted one):
     <Start>
       <Settings>
         <Control Panel>
            <Administrative Tools>
              <Data Sources (ODBC)>
Gives a tabbed window labeled "ODBC Data Source Administrator".
Choose tab "User DSN" (or "System DSN") and press the "Add..." button.
It asks you to:
    "Select a driver for which you want to set up a data source."

If you cannot find the menus I discuss above, explore using:
     <Start>
       <Help>
Got to tab "Search" and type "ODBC" without the quotes.  Check out
the various subjects that follow.
Choose some version of "Microsoft Access Driver (*.mdb)" -- my
machine offers the choice in a couple of human languages; I
inevitably go for English (my native language), but your
performance my vary.
Having chose the correct driver, I click "Finish"
This takes me to a dialog "ODBC Microsoft Access Setup"
That wants a Data Source Name (name by which I will later refer
to the DB) and a Description.  I always choose a simple DSN
such as "Exploratory" and skip the Description, but I am on a home
machine.  Remember the DSN namespace is "flat" -- no directories;
all the names you use must be distinct.  So for my example we use
"Exploratory" for the DSN and "None of your business" for the
description.  In the "Database" box, I click "Select...", which
will make a file chooser appear.  I then navigate to my access
database's file (c:\imports\dbxx.mdb in this case) and click OK.
Under the options section, I'm clicking "Read Only", but I'm paranoid.
I now click OK here and again at the "ODBC Data Source Administrator"
window, and this dialog session is at an end.

 From your attempts, I see you have loaded the win32 extensions, so
the following should work work for you as well (changing names as
appropriate -- I have a table named category):

     import odbc
     conn = odbc.odbc("Exploratory")
     cursor = conn.cursor()
     cursor.execute("SELECT COUNT(*) FROM category")
     print "lines in category = %s" % cursor.fetchone()
     cursor.close()
     conn.close()

If you get this far you are in business.  You can use idle (or your
other favorite python interactive environment) to explore the results
you get.  Know the difference between fetchone, fetchall and fetchmany,
understand setautocommit, and have fun.

I personally used mxODBC
	http://www.lemburg.com/files/python/mxODBC.html

to do a lot of Database work at one job; Lemburg dots 'i's and
crosses 't's to give you a great exploratory database environment.
You get column names for all row returned and so on.  The odbc
package that came with win32 does the bare bones.  The difference
is well worth the price in time you save, in deployed systems
(especially those given away) you may have to give up the features
Marc-Andre provides.

-Scott David Daniels
Scott.Daniels at Acm.Org





More information about the Python-list mailing list