Access sql with python, please. I'm desperate

dd dijk at dijk.nldef
Thu Nov 13 22:00:23 CET 2003


Thank you very much. It really helped. By the way, can one do the
following?
 
select * from [a-query] where .......
 
So, select from a query instead of a table...
 
Thanks anyway you guys, for answering the previous question.


Arjen




> 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