Access sql with python, please. I'm desperate
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
select * from [a-query] where .......
So, select from a query instead of a table...
Thanks anyway you guys, for answering the previous question.
> DD wrote:
>> 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):
> <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:
> 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()
> 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
> 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