473,573 Members | 2,462 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 "<interacti ve input>", line 1, in ?
File "C:\Python23\Li b\site-packages\win32c om\client\__ini t__.py", line 95,
in Dispatch
dispatch, userName =
dynamic._GetGoo dDispatchAndUse rName(dispatch, userName,clsctx )
File "C:\Python23\Li b\site-packages\win32c om\client\dynam ic.py", line 84,
in _GetGoodDispatc hAndUserName
return (_GetGoodDispat ch(IDispatch, clsctx), userName)
File "C:\Python23\Li b\site-packages\win32c om\client\dynam ic.py", line 72,
in _GetGoodDispatc h
IDispatch = pythoncom.CoCre ateInstance(IDi spatch, None, clsctx,
pythoncom.IID_I Dispatch)
com_error: (-2147221230, 'Klasse is niet gelicenseerd voor gebruik', None,
None)

thank in advance,

Arjen
Jul 18 '05 #1
9 3525
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" <ad@ad.nl>
To: <py*********@py thon.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 "<interacti ve input>", line 1, in ?
File "C:\Python23\Li b\site-packages\win32c om\client\__ini t__.py", line

95, in Dispatch
dispatch, userName =
dynamic._GetGoo dDispatchAndUse rName(dispatch, userName,clsctx )
File "C:\Python23\Li b\site-packages\win32c om\client\dynam ic.py", line 84, in _GetGoodDispatc hAndUserName
return (_GetGoodDispat ch(IDispatch, clsctx), userName)
File "C:\Python23\Li b\site-packages\win32c om\client\dynam ic.py", line 72, in _GetGoodDispatc h
IDispatch = pythoncom.CoCre ateInstance(IDi spatch, None, clsctx,
pythoncom.IID_I Dispatch)
com_error: (-2147221230, 'Klasse is niet gelicenseerd voor gebruik', None,
None)

thank in advance,

Arjen

Jul 18 '05 #2
"DD" <ad@ad.nl> 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 "<interacti ve input>", line 1, in ?
...
File "C:\Python23\Li b\site-packages\win32c om\client\dynam ic.py", line 72,
in _GetGoodDispatc h
IDispatch = pythoncom.CoCre ateInstance(IDi spatch, 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 "gelicensee rd"
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.
Jul 18 '05 #3
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>
<Administrati ve 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 "Explorator y" 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
"Explorator y" 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\dbx x.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("Expl oratory")
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***********@A cm.Org

Jul 18 '05 #4
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>
<Administrati ve 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 "Explorator y" 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
"Explorator y" 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\dbx x.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("Expl oratory")
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***********@A cm.Org


Jul 18 '05 #5
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***********@A cm.Org

Jul 18 '05 #6
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***********@A cm.Org

Jul 18 '05 #7
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

Jul 18 '05 #8
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
Jul 18 '05 #9
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***********@A cm.Org

Jul 18 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2116
by: SCS | last post by:
System: Windows 2003 Server PHP 5 Final IIS 6 Problem: Every time I run a PHP page I get "PHP has encountered an Access Violation at 017473CD" at the bottom of the page... But even worse, when I try to run a mysql/php page I get "Fatal error: Call
57
4195
by: John Howard | last post by:
I've sent several messages over the last year asking about python - Who teaches python? Is python losing steam? etc. I have noticed, eg, the declinng number of books at my local borders. The last time I visited a borders (last week), there was 1 (sic) book about python on the shelve compared to dozens on perl & java! On my last inquiry about...
6
2130
by: John Baker | last post by:
Hi: As those who have looked at this newsgroup recently will realize, I am a neophyte with Access, although I have experienced with Approach (the Lotus product). There are things I could easily do in Approach that appear to require some subtle and complex manipulation in Access. Herein lies my present problem. I have a number of tables,...
13
3292
by: BigDaDDY | last post by:
Um yeah....In case you haven't figured it out, Microsoft sucks. I'm going to be kicked back in my chair eating popcorn and watching football 10 years from now, while all you clowns are scrambling to rewrite all your code because Microsoft upgraded all their crap and nothing you wrote 10 years earlier works. It doesn't take a rocket scientist...
5
3000
by: Bec | last post by:
I'm in desperate need of your help.. I need to build an access database and have NO idea how to do this.. Not even where to start.. It IS for school, and am not asking anyone to do my homework for me.. I am merely asking for help, perhaps pointers as to where to begin.. I've never used access before.. I'm rather cluey when it comes to
118
6646
by: 63q2o4i02 | last post by:
Hi, I've been thinking about Python vs. Lisp. I've been learning Python the past few months and like it very much. A few years ago I had an AI class where we had to use Lisp, and I absolutely hated it, having learned C++ a few years prior. They didn't teach Lisp at all and instead expected us to learn on our own. I wasn't aware I had to...
15
4583
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to communicate with a MySQL database table on a web server, from inside of my company's Access-VBA application. I know VBA pretty well but have never...
1
1770
by: Charles Wilson | last post by:
Can someone point me to some good instructions on using the WinInet.dll functions from VBA? I need to interact with a MySQL database via HTTP strings. We already have the database set up and a PHP script that can receive commands via HTTP. The script receives the HTTP commands and performs the database actions, then responds with XML. ...
32
14633
by: Licheng Fang | last post by:
Basically, the problem is this: 'do' Python's NFA regexp engine trys only the first option, and happily rests on that. There's another example: 'oneself' The Python regular expression engine doesn't exaust all the
0
7670
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7987
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7740
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8036
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5557
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5263
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3703
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1269
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1005
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.