Access sql with python, please. I'm desperate

[DD]

Hello,

Could anyone please help me??
Is there somebody who could explain me how to make a connection to a access
database with a python cgi script.
I would like to use common sql commands in my python scripts as I can with
MySQLdb.
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?)
I normally use Linux, but this has to be in MS office

import win32api
import win32com.client
engine = win32com.client.Dispatch("DAO.DBEngine.35")

Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "C:\Python23\Lib\site-packages\win32com\client\__init__.py", line 95,
in Dispatch
dispatch, userName =
dynamic._GetGoodDispatchAndUserName(dispatch,userN ame,clsctx)
File "C:\Python23\Lib\site-packages\win32com\client\dynamic.py", line 84,
in _GetGoodDispatchAndUserName
return (_GetGoodDispatch(IDispatch, clsctx), userName)
File "C:\Python23\Lib\site-packages\win32com\client\dynamic.py", line 72,
in _GetGoodDispatch
IDispatch = pythoncom.CoCreateInstance(IDispatch, None, clsctx,
pythoncom.IID_IDispatch)
com_error: (-2147221230, 'Klasse is niet gelicenseerd voor gebruik', None,
None)

thank in advance,

Arjen

[Jon Franz]

Have you tried using the adodbapi module?

It wraps ADO on win32 platforms, and exposes the DBAPI 2.0 spec,
thus you can use the same sort of calls as you did using MySQLdb.

See:
http://adodbapi.sourceforge.net/

If you still want to go the COM route, I'd suggest at least using
ADO and not DAO.

~Jon Franz
NeuroKode Labs, LLC

----- Original Message -----
From: "DD" <[e-mail]>
To: <py*********@python.org>
Sent: Wednesday, November 12, 2003 6:07 PM
Subject: Access sql with python, please. I'm desperate

Hello,

Could anyone please help me??
Is there somebody who could explain me how to make a connection to a access database with a python cgi script.
I would like to use common sql commands in my python scripts as I can with
MySQLdb.
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?)
I normally use Linux, but this has to be in MS office

import win32api
import win32com.client
engine = win32com.client.Dispatch("DAO.DBEngine.35")

Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "C:\Python23\Lib\site-packages\win32com\client\__init__.py", line

95, in Dispatch
dispatch, userName =
dynamic._GetGoodDispatchAndUserName(dispatch,userN ame,clsctx)
File "C:\Python23\Lib\site-packages\win32com\client\dynamic.py", line 84, in _GetGoodDispatchAndUserName
return (_GetGoodDispatch(IDispatch, clsctx), userName)
File "C:\Python23\Lib\site-packages\win32com\client\dynamic.py", line 72, in _GetGoodDispatch
IDispatch = pythoncom.CoCreateInstance(IDispatch, None, clsctx,
pythoncom.IID_IDispatch)
com_error: (-2147221230, 'Klasse is niet gelicenseerd voor gebruik', None,
None)

thank in advance,

Arjen

[Tim Roberts]

"DD" <[e-mail]> wrote:

Could anyone please help me??
Is there somebody who could explain me how to make a connection to a access
database with a python cgi script.
...
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?)
I normally use Linux, but this has to be in MS office

engine = win32com.client.Dispatch("DAO.DBEngine.35")

Traceback (most recent call last):
File "<interactive input>", line 1, in ?
...
File "C:\Python23\Lib\site-packages\win32com\client\dynamic.py", line 72,
in _GetGoodDispatch
IDispatch = pythoncom.CoCreateInstance(IDispatch, None, clsctx,
pythoncom.IID_IDispatch)
com_error: (-2147221230, 'Klasse is niet gelicenseerd voor gebruik', None,
None)

The error code (-2147221230 is 80040112) and the word "gelicenseerd"
indicates that you don't have Microsoft Access or Microsoft Visual Basic
installed on your system. If you DO have one of them installed, there are
instructions in the Microsoft knowledge base in article number 240377 on
how to fix up the registry.
--
- Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.

[sdd]

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
Sc***********@Acm.Org

[dd]

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
Sc***********@Acm.Org

[sdd]

dd wrote:

Thank you very much. It really helped. You are welcome. By the way, we try to avoid "top-posting" here
in the c.l.p newsgroup, since it makes the message hard to read.
These messages get searched pretty intensively by people looking
for answers, and we like to keep them readable.
By the way, can one do the following?
select * from [a-query] where .......
So, select from a query instead of a table...

Well, that depends on your database provider (Access in this case),
rather than ODBC. I have two suggestions:

1) (A Python suggestion) Just try it. Fire up Idle, pythonwin,
the python interpreter, or whatever interactive python
you find the most comfortable and type away.

2) (a database guy suggestion) Avoid it if you can. Your query
is best handled if it is in a nice, flat, and-only query. A
query optimizer "wants" to try a lot of different ways to
evaluate your query, and pick the one with the least estimated
cost based on things like indices, estimated sizes, and actual
sizes. The "nested query" form is not trivially transformed
into a form that allows for many rewrites; several cheaper
optimizers just "punt" on such queries and run the obvious way.
That said, on at least some systems you can say:

SELECT * for (SELECT age, weight FROM personnel)
WHERE age in (SELECT age FROM retirees)

-Scott David Daniels
Sc***********@Acm.Org

[sdd]

I wrote:

That said, on at least some systems you can say:

SELECT * for (SELECT age, weight FROM personnel)
WHERE age in (SELECT age FROM retirees)

When of course I meant to write:
SELECT * FROM (SELECT age, weight FROM personnel)
WHERE age IN (SELECT age FROM retirees)

-Scott David Daniels
Sc***********@Acm.Org

[Alex Martelli]

sdd wrote:

I wrote:

That said, on at least some systems you can say:

SELECT * for (SELECT age, weight FROM personnel)
WHERE age in (SELECT age FROM retirees)

When of course I meant to write:
SELECT * FROM (SELECT age, weight FROM personnel)
WHERE age IN (SELECT age FROM retirees)

Yes, any "true" relational DB supports nested queries
(has mySQL gotten around to that yet...?), but I'm not
sure why you want the FIRST of the two nestings here --
why not just one nesting, as in:

SELECT age, weight FROM personnel
WHERE age in (SELECT age FROM retirees)

?
Alex

[Gerhard Häring]

Alex Martelli wrote:

[...] Yes, any "true" relational DB supports nested queries
(has mySQL gotten around to that yet...?), [...]

Apparently, only in 4.1, the alpha version
(http://www.mysql.com/doc/en/Roadmap.html).

Looks like I can wait a few years before trying out MySQL again ...

-- Gerhard

[sdd]

Alex Martelli wrote:

sdd wrote:

SELECT * FROM (SELECT age, weight FROM personnel)
WHERE age IN (SELECT age FROM retirees)

...I'm not sure why you want the FIRST of the two nestings here --
why not just one nesting, as in:
SELECT age, weight FROM personnel
WHERE age in (SELECT age FROM retirees)
?

There is no value to the nesting; I was simply trying to
echo the pattern the original poster's nested query in a
syntactically correct way. The fillip was in adding the
plausibly reasonable second subquery.

-Scott David Daniels
Sc***********@Acm.Org