Twisted + SQLAlchemy and the best way to do it

python sql database sqlalchemy twisted
Khorkrak picture Khorkrak · Jun 10, 2010 · Viewed 8.6k times · Source

So I'm writing yet another Twisted based daemon. It'll have an xmlrpc interface as usual so I can easily communicate with it and have other processes interchange data with it as needed.

This daemon needs to access a database. We've been using SQL Alchemy in place of hard coding SQL strings for our latest projects - those mostly done for web apps in Pylons.

We'd like to do the same for this app and re-use library code that makes use of SQL Alchemy. So what to do? Well of course since that library was written for use in a Pylons app it's all the straight-forward blocking style code that everyone is accustomed to and all of the non-blocking is magically handled by Pylons via threading, thread locals, scoped sessions and so on.

So now for Twisted I guess I'm a bit stuck. I could:

  1. Just write the sql I need directly if it's minimal and use the dbapi pool in twisted to do runInteractions etc when I need to hit the db.
  2. Use the objects and inherently blocking methods in our library and block now and then in my Twisted daemon. Bah.
  3. Use sAsync which was last updated in 2008 and kind of reuse the models we have defined already but not really and this doesn't address that the library code needs to work in Pylons too. Does that even work with the latest version SQL Alchemy? Who knows. That project looked great though - why was it apparently abandoned?
  4. Spawn a separate subprocess and have it deal with the library code and all it's blocking, the results being returned back to my daemon when ready as objects marshalled via YAML over xmlrpc.
  5. Use deferToThread and then expunge the objects returned having made sure to do eager loads so that I have all my stuff that I might need. Seems kind of ugha to me.

I'm also stuck using Python 2.5.4 atm so no 2.6 yet and I don't think I can just do an import from future to get access to the cool new multiprocessing module stuff in there. That's OK though I guess as we've got dealing with interprocess communication down pretty well.

So I'm leaning towards option 4 mostly as that would avoid the mortal sin of logic duplication with option 1 while also staying the heck away from threads.

My first attempt though will be option 2 to just get the thing going and then separate out the calls to the library code perhaps into a separate process if it looks like there's a good chance that something might take a bit too long to block on. Sad. Maybe a combination of Stackless Python and Twisted would be interesting here.

Any better ideas?

Answer

Glyph picture Glyph · Jul 8, 2014

In the intervening couple of years, Alex Gaynor created https://github.com/alex/alchimia which may be a better central repository for doing integration with SQLAlchemy and Twisted.

Related questions

sqlalchemy filter multiple columns

How do I combine two columns and apply filter? For example, I want to search in both the "firstname" and "lastname" columns at the same time. Here is how I have been doing it if searching only one column: query = …

Read More
SQLAlchemy: how to filter date field?

Here is model: class User(Base): ... birthday = Column(Date, index=True) #in database it's like '1987-01-17' ... I want to filter between two dates, for example to choose all users in interval 18-30 years. How to implement it …

Read More
Select NULL Values in SQLAlchemy

Here's my (PostgreSQL) table -- test=> create table people (name varchar primary key, marriage_status varchar) ; test=> insert into people values ('Ken', 'married'); test=> insert into people values ('May', 'single'); test=> insert into people values ('Joe', …

Read More