[Twisted-Python] SQLAlchemy and Twisted

[Don Dwiggins]

Doug Farrell wrote:
> I’ve been doing some searching about how to get SQLAlchemy and Twisted 
> working together in a Twisted application. Though I’ve found a lot of 
> information, I haven’t seen (or figured out) a good working solution or 
> definitive answer. The most promising one I’ve run across concerns 
> running the SQLAlchemy queries in a separate process (rather than a 
> separate thread) and communicating the queries between the Twisted 
> application in one process and the SQLAlchemy application in another. 
> Does anyone have any good pointers, suggestions, ideas, links to how I 
> might go about setting something like this up? 

The best advice I've gotten was from David Bolen; you can find an 
interchange between him and me on the SA list on October 22 and 23 of 
last year.  The upshot of it is, it shouldn't be a problem to use the 
SQL level if you're careful to keep the database accesses in a separate 
thread (and Bolen has done that); using the ORM level, however, can be 
problematic if you're tempted to access ORM objects in the main thread 
(since you're not directly in control of when database accesses occur).

> Here’s a couple questions that come to mind:
> 
> 1)      Would the SQLAlchemy process also be a Twisted application with 
> all the queries running as deferreds in the main thread, and blocking?

There might be value in reworking the SA concepts into a Twisted 
package, so that the asynchrony is "built in".  I haven't heard of any 
indications of that happening.

> 2)      How would the Twisted process communicate with the SQLAlchemy 
> process, using something like XMLRPC, calling methods to perform the 
> queries? Or would the XMLRPC methods convey something more generic like SQL?

Well, Bolen used a dedicated worker thread to do the SA operations (all 
SQL level), passing functions to it to be executed in that context.  He 
also used a single connection in the thread to do all DB operations.

-- 
Don Dwiggins
Advanced Publishing Technology