[Zope-DB] using connection from script

[Matthew T. Kromer]

Jochen Mader wrote:

>Hello,
>I'm a newbie with zope,  switched to zope 3 days ago after programming wih 
>j2ee for 2 years (may that darn thing burn in hell). After recreating some of 
>my projects within zope (it took me weeks to utilize ejbs to do that, in zope 
>it worked in just some hours *sigh*). 
>My problem now is that I don't know wether it is possible to do the following:
>I have defined a Postgres connection using psycopg. I know that it's 
>possible to get a connection by doing 
>context.<connectionname>.connect(<credentials>)
>but that is not what I want to do. I'd like to just use the defined 
>connection (it has already been opened with the right credentials so why 
>should I open a new connection and giving the credentials again).
>So what I'm asking is: is it possible to use a defined conenction as some 
>kind of a connectionpool.
>

OK, let's set the context for what you're doing.  You're making a python 
script to grab the database connection and perform a query manually, as 
opposed to using a SQL method.

There are two ways to go about this:

One is to call the connection object to get a connection -- this will 
give you the connection, e.g.

     db = context.connection_name()
     c = db.cursor()

etc..

However, you can also end up shooting yourself in the foot if you dont 
release things properly when you do it that way.  Make sure you dont end 
up leaving any references around in other objects that would keep things 
open and leak connections; you'll run out pretty soon! (I speak from 
experience!)  That means, dont do something like peristent_object.db = db...

The other way to do it is to have a SQL method which is a helper method 
-- one that is passed the ENTIRE query you wish to perform.  Now, let's 
say I make a SQLMethod like "genericQuery" and it takes one argument, 
query. Inside the body of the method, I have <dtml-var query>.  Then 
I've got a helper utility which can be passed any arbitrary query.   
*CAUTION* doing so in an unsecured manner can wreak havoc on your 
database.  What you want to do is protect that SQL method with a new 
permission (assign it a role) and give any python script that needs to 
invoke it a proxy role.