Python MySQL Connection Pool
July 25th, 2007 by NerdyNick
Recently I have been working on a Python application that required the use of connection pool to help speed up the processing time of the whole application. As making a new connection to the Database takes a lot of time. A connection pool is one of the best things to do to help speed this up.
I started to look all over the net and really never found out any good ideas for one that would support threading. So I set out to create my own. So here is what I came up with. MySQLPool Source
The MySQLPool class is the primary class for managing all the connections. It is never touched outside of the MySQL class. The MySQLPool class uses The Borg Pattern to store all the connections between all the instances and threads. As well as uses the Threading.Condition() to lock each connection as well as the connection dictionary.
The MySQL class is the primary class used by all the queries though out each thread and class. Each time you need to run a query. You create a new instance of MySQL passing it all your connection information in the form of a dictionary. Then call the query function with your SQL and replacement arguments (Just as if you where using the native cursor.execute() function).
When use correctly all your threads will be able to share the same connections and help speed things up.
Example:
conn= {”host”:”localhost”,”user”:”root”,”passwd”:”python”,”db”:”companies”}
query = MySQL(conn, True)
query.Query(”select * from company where company_id = %s”, (self.company_id))
if (int(query.rowcount) > 0):
for row in query.record:
print row["company"]
Source Distro Package: MySQLPool-1.0.zip
Linux Tar package to come soon.
Posted in Projects | 1 Comments