[Zope] Using MySQL from a product

[Gilles Lenfant]

----- Original Message -----
From: "Nick Arnett" <narnett@mccmedia.com>
To: "Zope@Zope.Org" <zope@zope.org>
Sent: Tuesday, July 29, 2003 7:18 PM
Subject: RE: [Zope] Using MySQL from a product


> > -----Original Message-----
> > From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of
> > Gilles Lenfant
>
> ...
>
> > Warning, you should get the MySQLdb.Connection objects from a pool of
> > connections : a MySQLdb.Connection object is not thread safe.
>
> Could you explain what this implies, in a practical sense?  Would it mean
> having a shared module that creates connections, then all external methods
> would import it?  How about products that use MySQLdb?
>
> Nick
>

You can do 2 things :

1/ simple : in your product methods, create a new Connection object for each
method that needs to access MySQL

class Foo(...):
__ def foo(self):
____db = MySQLdb.Connnect(...)
____dbc = db.cursor()
____...
____db.execute(someSqlQuery)
____...
____db.close()
____return xxx

2/ complex but with better performances (creating a new connection may
require some time):
Create a pool of connections. Those connections are created at Zope startup,
and dynamically distributed to (and locked for) the various methods that
need to make SQL queries. If there's not enough available connections at a
give time, a new one is created and included in the pool for future use.
Using such constructs make faster products (for methods that make SQL
queries).

I'll join a sample of connection pool I used some times ago.

--Gilles