Thanks for this insightful comments on Sqlalchmy and Twisted.

I hope I had read your mail before I start my current project.  

At that time, I simply thought using deferToThread would solve
everything since the twisted.enterprise.adbapi simply use threads.deferToThreadPool
to make database interface asynchronous.

Today, I suddenly feel that, as sqlalchemy is not thread safe, using deferToThread might cause unexpected problems.
And I found this mail and it seems that all the code I wrote this week is going to be useless.

But, I got another question:

Since adbapi uses deferToThreadPool to make db api asynchronous, does this mean that all python db apis are *thread safe*?

BTW: what does 'query generator' means?  Is it a software genrating SQLs?

On Fri, May 9, 2008 at 9:39 AM, Valentino Volonghi <dialtone@gmail.com> wrote:
On Thu, May 8, 2008 at 3:03 PM, alex clemesha <clemesha@gmail.com> wrote:
> But, I hoping to solve some immediate problems with Twisted and
> SQLAlchemy, so ...

I've long worked on trying to integrate sqlalchemy and twisted in a
useful way but that can only be done when using simple queries. There
are countless reasons behind this limitation, like the fact that
sqlalchemy objects can do a lot of stuff behind the scenes or because
code that you write in other places might use some assumptions because
it's sync rather than async, and there are also problems with possible
bugs and assumptions within sqlalchemy itself. Basically it's not safe
and I wouldn't recommend it for anyone to use the orm together within
a Twisted process.

What I ended up doing is creating subprocesses that have fairly
complex logic and usually spend more than a few seconds doing their
job. By doing this I simply use sqlalchemy synchronously and without
any problems (I was starting to go completely crazy while trying to
debug time-dependent errors when I decided to refactor my code to use
subprocesses instead, everything started working smoothly without any
relevant change in the logic or in the test code and now I can also
run the subprocess as a system script to check and verify stuff or
debug (with pdb)).

Considering that an ORM is only useful (?) for very complex logic I
consider my approach completely sane. Use the query generator for
everything and reserve the orm for long running tasks.

nadbapi and storm-twisted tries to solve the orm problem in a similar
way but nadbapi is no longer maintained, anyway both currently suffer
from an incredible number of deferreds that they generate making the
use of defgen an absolute requirement (and thus lowering overall
performance). If you look hard enough in glyph's old blog or in this
list (I can't remember where exactly) you can find a post about
letting the database deal with concurrency and not spread it in
application code (aka having tons of deferreds). It is true that not
using threads increases speed and memory usage but for this particular
case 99% of the time you are not going to need neither of them anyway.

closing: I recently refactored some code that used sqlalchemy orm into
using just the query generator and instead of taking 17 seconds it now
takes 7 seconds and memory usage has decreased considerably. It's not
really just because an orm is slower than simply executing queries,
but rather because querying the db was so easy; it was basically done
"recklessly" without thinking too much about the performance hit of
going back and forth to the database. This is basically why I'd rather
not use ORMs unless forced by legacy code or because generating a
query would introduce so much complexity to make it an application in
itself (very rare case IMHO).

Valentino Volonghi aka Dialtone
Now running MacOS X 10.5
Home Page: http://www.twisted.it

Twisted-Python mailing list