Using MySQL from a product
I amd creating a product, that needs to communicate with MySQL (inserting/updating records and making select queries). I've installed MySQLdb and Z MySQLDA and can easily create connections from ZMI. I don't know however, how to communicate directly with MySQL from my product. Should I rely on a connection created in ZMI? Or do I create my own connection - if so: how? And how do I make the queries? - Carsten
----- Original Message ----- From: "Carsten Gehling" <carsten@gehling.dk> To: "Zope@Zope.Org" <zope@zope.org> Sent: Monday, July 28, 2003 11:37 PM Subject: [Zope] Using MySQL from a product
I amd creating a product, that needs to communicate with MySQL (inserting/updating records and making select queries).
I've installed MySQLdb and Z MySQLDA and can easily create connections from ZMI.
I don't know however, how to communicate directly with MySQL from my product. Should I rely on a connection created in ZMI? Or do I create my own connection - if so: how? And how do I make the queries?
- Carsten
If you manage the security programmatically, you should prefer using directly MySQLdb rather than a ZMySQLDA object. This gives more flexibility and is easier to use for complex queries from Python. Warning, you should get the MySQLdb.Connection objects from a pool of connections : a MySQLdb.Connection object is not thread safe. --Gilles
-----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
----- 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
Thank you Gilles and Fritz. Funny... Just an hour before getting your suggestions, I thought "Why don't I just call the MySQLdb directly? I do that in other python scripts. And lo and behold! It worked :-) I actually did your suggestion number one Gilles, with connecting to the database from inside the method. I am very interested in the connection pool idea - I'll probably build it into my product when time comes. - Carsten
-----Oprindelig meddelelse----- Fra: zope-admin@zope.org [mailto:zope-admin@zope.org]På vegne af Gilles Lenfant Sendt: 29. juli 2003 23:31 Til: Nick Arnett; Zope@Zope.Org Emne: Re: [Zope] Using MySQL from a product
----- 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
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
participants (3)
-
Carsten Gehling -
Gilles Lenfant -
Nick Arnett