Re: [Myghty-users] Database connection pool/reuse

[David G. <dg...@sp...>]

Curious as to how this would work with SQLObject, if it can....

Thanks!

David

mike bayer wrote:
> The thing Ive been working on (well lately not much) has a working
> connection pool using Queue.Queue as well as a thread-local mechanism,
> which is transparently referenced via an adapter that returns the
> connection once it falls out of scope.
> 
> Since I'm not getting much done on the rest of the SQL library lately, the
> pool.py is attached.  heres an example of running it with SQLLite:
> 
>     from pysqlite2 import dbapi2 as sqlite
>     import pool
> 
>     # create a "connection manager" that transparently pools
>     # connections based on their connect args
>     manager = pool.manage(sqlite, use_threalocal = True)
> 
>     # create/retrieve a connection to 'foo.db'
>     connection = manager.connect('foo.db')
> 
> ... and thats it.  when the connection object falls out of scope, it gets
> returned to the pool.  calling connect() repeatedly in the same thread
> returns the same connection that is already checked out in that thread, if
> any.
> 
> to use a Pool object directly, do:
> 
>     p = pool.QueuePool(creator = lambda: sqlite.connect('foo.db'),
>     pool_size = 3, max_overflow = -1, use_threadlocal = True, echo = False)
> 
>     connection = p.connect()
> 
> where max_overflow specifies how many connections you can use beyond the
> pool_size, use_threadlocal indicates that a call to a new connection will
> return the currently-checked out connection of the same thread if one
> exists, and echo makes it print what its doing to stdout (all those
> options can be sent to manage() as well).
> 
> the "scope="thread"" thing in Myghty results in all the objects within the
> template being created in the scope of a closure that is executed
> per-thread.  which is not a big deal though I personally prefer if the
> individual thread-sensitive objects come out of some interface that works
> it out on its own....that way it can also be used by module components and
> other non-template objects without issue.
> 
> for transactions I am thinking doing something like this:
> 
>     def transaction(connection, func):
>         connection.begin()
>         try:
>             func()
>         except:
>             connection.rollback()
>             raise
>         connection.commit()
> 
> ...but also with more wrappers around the connection/cursor object which
> keep track of the "scope" of the transaction, i.e. that the "begin" and
> "commit" happen at the same level.
> 
> 
> Deron Meranda said:
> 
>>I was wondering how people like to do database connection
>>pooling in Myghty.  I can see several ways to do this, but was
>>wondering what the typical/best way is.  Also are thread-safe
>>techniques, like Python's Queue module best; or perhaps by
>>using the Myghty scope="thread"?  And how are exceptions
>>handled (such as doing a db rollback and putting it back in
>>the pool)?
>>
>>(This seems like another useful topic for the Wiki)
>>--
>>Deron Meranda
>>
>>
>>-------------------------------------------------------
>>SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
>>from IBM. Find simple to follow Roadmaps, straightforward articles,
>>informative Webcasts and more! Get everything you need to get up to
>>speed, fast. http://ads.osdn.com/?ad_idt77&alloc_id492&op=click
>>_______________________________________________
>>Myghty-users mailing list
>>Myg...@li...
>>https://lists.sourceforge.net/lists/listinfo/myghty-users
>>