What is the best solution for database connection pooling in python?

By : John
Source: Stackoverflow.com
Question!

I have developed some custom DAO-like classes to meet some very specialized requirements for my project that is a server-side process that does not run inside any kind of framework.

The solution works great except that every time a new request is made, I open a new connection via MySQLdb.connect.

What is the best "drop in" solution to switch this over to using connection pooling in python? I am imagining something like the commons DBCP solution for Java.

The process is long running and has many threads that need to make requests, but not all at the same time... specifically they do quite a lot of work before brief bursts of writing out a chunk of their results.

Edited to add: After some more searching I found anitpool.py which looks decent, but as I'm relatively new to python I guess I just want to make sure I'm not missing a more obvious/more idiomatic/better solution.

By : John


Answers

Wrap your connection class.

Set a limit on how many connections you make. Return an unused connection. Intercept close to free the connection.

Update: I put something like this in dbpool.py:

import sqlalchemy.pool as pool
import MySQLdb as mysql
mysql = pool.manage(mysql)
By : Chris


Old thread, but for general-purpose pooling (connections or any expensive object), I use something like:

def pool(ctor, limit=None):
    local_pool = multiprocessing.Queue()
    n = multiprocesing.Value('i', 0)
    @contextlib.contextmanager
    def pooled(ctor=ctor, lpool=local_pool, n=n):
        # block iff at limit
        try: i = lpool.get(limit and n.value >= limit)
        except multiprocessing.queues.Empty:
            n.value += 1
            i = ctor()
        yield i
        lpool.put(i)
    return pooled

Which constructs lazily, has an optional limit, and should generalize to any use case I can think of. Of course, this assumes that you really need the pooling of whatever resource, which you may not for many modern SQL-likes. Usage:

# in main:
my_pool = pool(lambda: do_something())
# in thread:
with my_pool() as my_obj:
    my_obj.do_something()

This does assume that whatever object ctor creates has an appropriate destructor if needed (some servers don't kill connection objects unless they are closed explicitly).



Making your own connection pool is a BAD idea if your app ever decides to start using multi-threading. Making a connection pool for a multi-threaded application is much more complicated than one for a single-threaded application. You can use something like PySQLPool in that case.

It's also a BAD idea to use an ORM if you're looking for performance.

If you'll be dealing with huge/heavy databases that have to handle lots of selects, inserts, updates and deletes at the same time, then you're going to need performance, which means you'll need custom SQL written to optimize lookups and lock times. With an ORM you don't usually have that flexibility.

So basically, yeah, you can make your own connection pool and use ORMs but only if you're sure you won't need anything of what I just described.

By : flexo


This video can help you solving your question :)
By: admin