[Twisted-Python] SQLAlchemy and Twisted
Hi all, 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? 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? 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? Thanks in advance for any help! Doug
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
Doug Farrell wrote:
I’ve been doing some searching about how to get SQLAlchemy and Twisted working together in a Twisted application.
Short version: to be safe, anything that touches any SQLAlchemy-mapped object needs to be run in its own thread. Any query or access of an attribute of a mapped object may result in a blocking sql query. (aka: twisted doesn't play nice with orms)
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.
That seems a little odd. What would be the IPC? How would the "sqlachemy application" be run?
1) Would the SQLAlchemy process also be a Twisted application with all the queries running as deferreds in the main thread, and blocking?
What do you men by "all the queries"?
Thanks in advance for any help!
In my case, since most of the app I'm working on is "web requested" (either xmlrpc or http), I just agve up and used a good wsgi stack (repoze.bfg in my case) and munge other incoming requests into wsgi requests. Twisted's wsgi server runs each request in its own thread, so be it. cheers, Chris
Guys, now that I see this and the past conversations about SA and Twisted, and now that I've read some more about the non-blocking concepts, I think that I'am not doing things the best way I am doing this: 1. Twisted IMAP4 Client to read my mails 2. Inside this client I import a module that contains some funtions that parse the email via re 3. Also inside the client I import a module that makes a DB connection and insert the data parsed from those emails, all this via SQL using mapped tables. I'm almost sure that I'm breacking the hole twisted concept doing this thisway , do you guys have any advice for me Thanks 2010/5/5 Chris Withers <chris@simplistix.co.uk>
Doug Farrell wrote:
I’ve been doing some searching about how to get SQLAlchemy and Twisted working together in a Twisted application.
Short version: to be safe, anything that touches any SQLAlchemy-mapped object needs to be run in its own thread. Any query or access of an attribute of a mapped object may result in a blocking sql query. (aka: twisted doesn't play nice with orms)
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.
That seems a little odd. What would be the IPC? How would the "sqlachemy application" be run?
1) Would the SQLAlchemy process also be a Twisted application with all the queries running as deferreds in the main thread, and blocking?
What do you men by "all the queries"?
Thanks in advance for any help!
In my case, since most of the app I'm working on is "web requested" (either xmlrpc or http), I just agve up and used a good wsgi stack (repoze.bfg in my case) and munge other incoming requests into wsgi requests.
Twisted's wsgi server runs each request in its own thread, so be it.
cheers,
Chris
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
You might also check out sAsync: http://sasync.org/ This was a project apparently abandoned (?) by the original author, but it's recently been picked up by someone else. Kevin Horn On Thu, May 6, 2010 at 2:04 PM, César García <celord@gmail.com> wrote:
Guys, now that I see this and the past conversations about SA and Twisted, and now that I've read some more about the non-blocking concepts, I think that I'am not doing things the best way
I am doing this:
1. Twisted IMAP4 Client to read my mails 2. Inside this client I import a module that contains some funtions that parse the email via re 3. Also inside the client I import a module that makes a DB connection and insert the data parsed from those emails, all this via SQL using mapped tables.
I'm almost sure that I'm breacking the hole twisted concept doing this thisway , do you guys have any advice for me
Thanks 2010/5/5 Chris Withers <chris@simplistix.co.uk>
Doug Farrell wrote:
I’ve been doing some searching about how to get SQLAlchemy and Twisted working together in a Twisted application.
Short version: to be safe, anything that touches any SQLAlchemy-mapped object needs to be run in its own thread. Any query or access of an attribute of a mapped object may result in a blocking sql query. (aka: twisted doesn't play nice with orms)
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.
That seems a little odd. What would be the IPC? How would the "sqlachemy application" be run?
1) Would the SQLAlchemy process also be a Twisted application with all the queries running as deferreds in the main thread, and blocking?
What do you men by "all the queries"?
Thanks in advance for any help!
In my case, since most of the app I'm working on is "web requested" (either xmlrpc or http), I just agve up and used a good wsgi stack (repoze.bfg in my case) and munge other incoming requests into wsgi requests.
Twisted's wsgi server runs each request in its own thread, so be it.
cheers,
Chris
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
-- http://celord.blogspot.com/
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
If you want an ORM in Twisted look for STORM twisted integration. Overall, it works (I tried). Some quirks are there, e.g. without twisted you would do something like resultset[10:20].sort(Sort.ASC) but with twsited you have to do (assuming inlineCallbacks) res= yield resultset[10:20] res = yield res.sort(Sort.ASC) which ain't pretty but it sure beats using plain twisted.enterprise -- Konrads Smelkovs Applied IT sorcery. On Thu, May 6, 2010 at 11:00 PM, Kevin Horn <kevin.horn@gmail.com> wrote:
You might also check out sAsync: http://sasync.org/
This was a project apparently abandoned (?) by the original author, but it's recently been picked up by someone else.
Kevin Horn
On Thu, May 6, 2010 at 2:04 PM, César García <celord@gmail.com> wrote:
Guys, now that I see this and the past conversations about SA and Twisted, and now that I've read some more about the non-blocking concepts, I think that I'am not doing things the best way
I am doing this:
1. Twisted IMAP4 Client to read my mails 2. Inside this client I import a module that contains some funtions that parse the email via re 3. Also inside the client I import a module that makes a DB connection and insert the data parsed from those emails, all this via SQL using mapped tables.
I'm almost sure that I'm breacking the hole twisted concept doing this thisway , do you guys have any advice for me
Thanks 2010/5/5 Chris Withers <chris@simplistix.co.uk>
Doug Farrell wrote:
I’ve been doing some searching about how to get SQLAlchemy and Twisted working together in a Twisted application.
Short version: to be safe, anything that touches any SQLAlchemy-mapped object needs to be run in its own thread. Any query or access of an attribute of a mapped object may result in a blocking sql query. (aka: twisted doesn't play nice with orms)
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.
That seems a little odd. What would be the IPC? How would the "sqlachemy application" be run?
1) Would the SQLAlchemy process also be a Twisted application with all the queries running as deferreds in the main thread, and blocking?
What do you men by "all the queries"?
Thanks in advance for any help!
In my case, since most of the app I'm working on is "web requested" (either xmlrpc or http), I just agve up and used a good wsgi stack (repoze.bfg in my case) and munge other incoming requests into wsgi requests.
Twisted's wsgi server runs each request in its own thread, so be it.
cheers,
Chris
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
-- http://celord.blogspot.com/
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
On Fri, May 7, 2010 at 12:53 PM, Konrads Smelkovs <konrads.smelkovs@gmail.com> wrote:
If you want an ORM in Twisted look for STORM twisted integration. Overall, it works (I tried). Some quirks are there, e.g. without twisted you would do something like resultset[10:20].sort(Sort.ASC) but with twsited you have to do (assuming inlineCallbacks)
res= yield resultset[10:20] res = yield res.sort(Sort.ASC)
If you wanted to, you could write this as: (yield resultset[10:20]).sort(Sort.ASC) assuming a new enough version of Python. -- mithrandi, i Ainil en-Balandor, a faer Ambar
(yield resultset[10:20]).sort(Sort.ASC)
assuming a new enough version of Python.
I wonder if it a good idea to subclass Deferred and define __call__() for it, making it attach callbacks to itself. Theoretically should work in earlier versions and make the syntax look more transparent.
On 03:37 pm, drwxrwxr.x+twistedmail@gmail.com wrote:
(yield resultset[10:20]).sort(Sort.ASC)
assuming a new enough version of Python.
I wonder if it a good idea to subclass Deferred and define __call__() for it, making it attach callbacks to itself. Theoretically should work in earlier versions and make the syntax look more transparent.
Let me clear that up for you, then. No, this is not a good idea. :) Jean-Paul
If you let SQLAlchemy block twisted would there be any impact besides performance? Dan On Fri, May 7, 2010 at 1:35 PM, <exarkun@twistedmatrix.com> wrote:
On 03:37 pm, drwxrwxr.x+twistedmail@gmail.com<drwxrwxr.x%2Btwistedmail@gmail.com>wrote:
(yield resultset[10:20]).sort(Sort.ASC)
assuming a new enough version of Python.
I wonder if it a good idea to subclass Deferred and define __call__() for it, making it attach callbacks to itself. Theoretically should work in earlier versions and make the syntax look more transparent.
Let me clear that up for you, then.
No, this is not a good idea. :)
Jean-Paul
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
On 05/09/2010 07:07 AM, Daniel Griffin wrote:
If you let SQLAlchemy block twisted would there be any impact besides performance?
Depends how long it blocks for, and what else your process is doing. With the reactor blocked: * no socket reads or accepts can be done * no callLater or LoopingCall can be scheduled ...and so on. If you've only got a small number of clients and no time-based scheduling and you're only blocking for a short time (tens or hundreds of milliseconds, say) it may be tolerable. If the server has a lot of clients, or needs to accept new ones in a timely fashion, or has time-based work to do, and your blocking might go on for a while, it's probably not tolerable. I'd avoid it personally (but then I'm utterly unconvinced by ORMs) and stick to using SA in a thread as your SQL query layer.
That is kind of what I found. In my app I basically have the following triggered using callLater: do a select and create a new SSL connection for each item returned Wait for the response Do between 2 and 4 inserts depending on results Close the connection. As measured in connections completed per seconds my results have been: 15/second with blocking twisted 7/second using deferred to thread 18/second using SQLalchemy in another process over PB. I know these are anecdotal but they might help someone else. The two problems with PB are that you quickly overrun the maximum amount of FD's available for select reactors and you end up roughly doubling the amount of DB querying you are doing. I intend to try using something like ampoule(deferToProcess) but I think I will have the same problems as with PB since it just uses TCP sockets anyways. Dan On Sun, May 9, 2010 at 4:45 AM, Phil Mayers <p.mayers@imperial.ac.uk> wrote:
On 05/09/2010 07:07 AM, Daniel Griffin wrote:
If you let SQLAlchemy block twisted would there be any impact besides performance?
Depends how long it blocks for, and what else your process is doing.
With the reactor blocked:
* no socket reads or accepts can be done * no callLater or LoopingCall can be scheduled
...and so on.
If you've only got a small number of clients and no time-based scheduling and you're only blocking for a short time (tens or hundreds of milliseconds, say) it may be tolerable.
If the server has a lot of clients, or needs to accept new ones in a timely fashion, or has time-based work to do, and your blocking might go on for a while, it's probably not tolerable.
I'd avoid it personally (but then I'm utterly unconvinced by ORMs) and stick to using SA in a thread as your SQL query layer.
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
I think that nice syntax contributes towards adoption. Subclassing deferred and adding a __get__ function that queues calls for future Deferreds is at least worth a shot. Perhaps adding a safety net with allowed function names would help. This list could be per "project" - storm orm integration would have its StormDeferred and something else - its own. 2010/5/7, exarkun@twistedmatrix.com <exarkun@twistedmatrix.com>:
On 03:37 pm, drwxrwxr.x+twistedmail@gmail.com wrote:
(yield resultset[10:20]).sort(Sort.ASC)
assuming a new enough version of Python.
I wonder if it a good idea to subclass Deferred and define __call__() for it, making it attach callbacks to itself. Theoretically should work in earlier versions and make the syntax look more transparent.
Let me clear that up for you, then.
No, this is not a good idea. :)
Jean-Paul
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
-- Nosūtīts no manas mobilās ierīces -- Konrads Smelkovs Applied IT sorcery.
On May 9, 2010, at 4:14 PM, Konrads Smelkovs wrote:
I think that nice syntax contributes towards adoption.
Not as much as consistency and coherency.
Subclassing deferred and adding a __get__ function that queues calls for future Deferreds is at least worth a shot.
Nope. If you want to implement promise pipelining, Deferred is the wrong place to do it. Maybe try something that wraps up a Deferred. Composition > Inheritance.
Perhaps adding a safety net with allowed function names would help.
Deferred already has way too many methods, so you wouldn't be able to pipeline a promise to anything with an 'addCallbacks' or 'addErrbacks'... or 'called' or 'timeoutCall' or 'debug' (etc). It's important that these catch-all namespaces be clean, so that edge cases don't have extremely surprising behavior.
This list could be per "project" - storm orm integration would have its StormDeferred and something else - its own.
I don't even know what this means, but it sounds bad.
On 01:34 pm, mithrandi@mithrandi.net wrote:
On Fri, May 7, 2010 at 12:53 PM, Konrads Smelkovs <konrads.smelkovs@gmail.com> wrote:
If you want an ORM in Twisted look for STORM twisted integration. Overall, it works (I tried). Some quirks are there, e.g. without twisted you would do something like resultset[10:20].sort(Sort.ASC) but with twsited you have to do (assuming inlineCallbacks)
res= yield� resultset[10:20] res = yield res.sort(Sort.ASC)
If you wanted to, you could write this as:
(yield resultset[10:20]).sort(Sort.ASC)
assuming a new enough version of Python.
You left off one of the yields, though. res = yield (yield resultset[10:20]).sort(Sort.ASC) Jean-Paul
participants (13)
-
Chris Withers -
César García -
Daniel Griffin -
Don Dwiggins -
Doug Farrell -
exarkun＠twistedmatrix.com -
Glyph Lefkowitz -
Jaroslaw Fedewicz -
Kevin Horn -
Konrads Smelkovs -
Konrads Smelkovs -
Phil Mayers -
Tristan Seligmann