Re: Capabilities of PyDO

On Thu, Jan 01, 2004 at 08:54:37PM -0500, [email protected] wrote:
> I am considering the use of PyDO as part of a three-tier application- There
> will be a client gui, a business object middle layer and a relational
> database. There is a comment in the Pythoninfo Wiki that "The current
> downfall is that PyDO is hard to use in a multithreaded environment, or any
> environment where more than one connection is desired to be to the *same*
> database. " Is there more specific information ragarding this that I should
> be aware of before starting  my little project? In what way is it 'hard'?

The problem alluded to is that PyDO currently obtains its database
connection from a cache of connection objects, a dictionary mapping
connection aliases to actual connections.  A PyDO class knows its
connection alias, and when you perform an operation on the PyDO class,
it obtains the connection you need implicitly.  One connection
instance exists per alias; connections aren't pooled.  There's the
rub.  

If you are using threads and a database with transactions, without
autocommit, and furthermore the db driver you are using doesn't have
magical super-thread-safe features (as I've heard Psycopg, a
postgresql driver, has), this can be a problem.  Threads will end up
sharing the same transactions.  If you aren't using running db
transactions inside of threads, though, it isn't an issue.  In
SkunkWeb, for instance, it never is, which is why PyDO doesn't support
threads more robustly.

There are possible solutions, but they probably all involve either
patching or augmenting PyDO a little.  One user patched PyDO to take
an additional connection argument to methods like update() and new(),
the idea being that you could manage the connection yourself external
to the class.  This doesn't appeal to me, much, and I don't see how
you'd deal with dictionary-style mutation, e.g., myobj[field]=newval.
Another approach would be to permit an actual connection, or
"transaction object", to be an attribute of the class itself, and
passed at constructor time (and hence to class methods like getSome()
that instantiate new objects).  SQLObject does something like this, I
believe.  Another approach would be to say that thread-safety is the
responsibility of the PyDO db drivers (essentially wrappers around the
Python DB-API drivers), and rewrite them so that each thread gets a
separate connection from a pool.  I haven't thought about it much, but
generally I prefer the last alternative.

In the case of postgresql, I haven't tested psycopg, but there is a
user-contributed psycopg driver in cvs, and if works as advertised,
it should be threadsafe, as the driver manages a connection pool
itself.   Has anyone tried it?

Incidentally, please use the version of PyDO in cvs or in the last
SkunkWeb release (3.4b5), not the somewhat out-of-date separate PyDO
release!

Cheers,

Jacob Smullyan

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/9O0duqamFyFXXLIRAn9HAJ9CpVZ6f613xwsa72hTlKwq9C9JRQCgu07y
jYdr0MJY0rOtVzwCu//gQTY=
=bt/M
-----END PGP SIGNATURE-----