[Twisted-Python] SQLAlchemy and Twisted

alex clemesha clemesha at gmail.com
Tue May 13 00:32:08 EDT 2008

Hi Valentino,

Thank you very much for the thoughtful response.
I was familiar with the fact that you have some hands on
experience with SQLAlchemy and Twisted, so it's good
to hear true stories from the trenches.

A while back I came upon a talk you did at Europython,
and it had some discussion of Twisted and SQLAlchemy,
with, to be quite honest, some very 'magical' looking code:

Anyways, it's really looking like the answer to
"how do you combine Twisted and SQLAlchemy"
comes down to: "run it in another process".

I guess the canonical solution to dealing with all
inherently blocking API's with respect to twisted is
run them in separate process, which sounds just fine.


On Thu, May 8, 2008 at 6:39 PM, Valentino Volonghi <dialtone at gmail.com> wrote:
> On Thu, May 8, 2008 at 3:03 PM, alex clemesha <clemesha at 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
>  Twisted-Python at twistedmatrix.com
>  http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python

More information about the Twisted-Python mailing list