[Zope-DB] Managing Connections with SQL Methods

Seth Gottlieb sgottlieb at optaros.com
Tue Oct 25 17:12:22 EDT 2005


I have a Python class that my Zope application uses to talk to a 
relational database (MySQL for now).  I am using a Z SQL Connection 
(ZMySQLDA) object to handle DB connectivity.  When I initialize, I get a 
handle of the connection object like this:

import Zope
setattr(self, CONNECTION_NAME, getattr(Zope.app(),CONNECTION_NAME))

Where CONNECTION_NAME is a string that matches the ID of the 	connection 
object that I have in the Root folder of Zope.

Then I run queries like this:

from Products.ZSQLMethods.SQL import SQL

setattr(self, '_selectProperties',
             SQL('_selectProperties', '', CONNECTION_NAME, 'schemaId',
             'SELECT * FROM ' + TABLE_NAME[self._context.id] + ' 
WHERE 			     SCHEMA_ID = <dtml-sqlvar schemaId type="int">'))

The life of this class should be Zope transaction (I am hooking into 
Shared.DC.ZRDB.TM.TM to manage these objects).

The problem I am running into is I seem to be running out of 
connections.   Everytime I inititialize and run "show processlist;" 
within MySQL, the number of connections increases.  I get to a maximum 
of 3 and then my Zope instance hangs.

Is there any reason why these DB connections do not get reused or 
destroyed after queries are made?

Any ideas?



Seth Gottlieb
Optaros, Inc.
60 Canal Street
Boston, MA 02114
e: sgottlieb at optaros.com
v: 617.227.1855 ext. 107
f: 617.227.1755
m: 617.852.2956

More information about the Zope-DB mailing list