[Twisted-Python] SQL Abstraction Layer
I was reading through the enterprise module in twisted, and then I came across this website: http://www.penguin.cz/~ondrap/sqlabstr.php It makes using SQL in Python much more readable. I was thinking that it would be nice to incorporate some of the functionality of this module. It has more than would probably be necessary (creating classes dynamically from the schema in the underlying database for example), but I think it would make a nice addition to the enterprise module. Any thoughts? David Blewett ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
On Tue, 17 Jan 2006 08:14:12 -0500, David Blewett <david@dawninglight.net> wrote:
Any thoughts?
The main thought I have is "No" If anything, we should be moving towards *removing* some functionality like this (twisted.enterprise.row) which is unmaintained, poorly documented, poorly tested... It's perfectly possible to use Twisted with an ORM or SQL sweetener of your choice. If one is to become "officially" supported, it would probably be Axiom <http://divmod.org/trac/wiki/DivmodAxiom>, just due to the familiarity that several of the core Twisted developers have with it - but that's not really sufficiently general for inclusion in Twisted at this point. Look for it in the next 5 years, maybe.
----- Original Message ----- From: <glyph@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Tuesday, January 17, 2006 8:35 AM Subject: Re: [Twisted-Python] SQL Abstraction Layer
It's perfectly possible to use Twisted with an ORM or SQL sweetener of your choice.
what would help, i think, is some documentation/examples describing how this can be achieved with specific implementations. there's no reason this has to be in the official twisted documentation or written by twisted folks, necessarily, it would be good for it to just exist ;) dialtone's italian post shows the minimalistic example, but as he (through the translation) states, it probably won't be anywhere near as simple to use sqlalchemy's ORM features. i am unsure whether i'll be using a fullblown ORM for the stuff i'm working on or just some query building facilities, but if nothing happens documentation-wise before i cross that bridge, i'll put up some examples of what i ended up doing. alternatively, does someone have experience/insights to share but has been keeping it on the down low? now's the time to come on out with it ;) -p
Worth looking at: http://www.sqlobject.org I've only been tinkering for a few days, and I haven't done anything complicated with it (yet), but so far I am pleased. On 1/17/06, Paul G <paul-lists@perforge.com> wrote:
Paul G wrote:
My twisted app uses both query-building and an ORM. If anyone wants a look, a nightly tarball can be downloaded from http://ned.gsfc.nasa.gov/PanGalactic.tar.gz. Query-mapping is in pangalactic.utils.sql ORM is in pangalactic.repo.chronosynclastic The sql.py module is easily usable outside of pangalactic, and is just a way of building SQL statements from functions -- pretty rudimentary, and only tested with postgresql. The ORM is simple but is heavily dependent on the somewhat unorthodox way pangalactic does metaobjects, so probably not usable outside of pangalactic, but there might be some ideas that could be used. Docs (such as they are): PanGalactic/doc/PgefManual.html Comments welcome! Cheers, Steve
On Tue, Jan 17, 2006 at 08:58:36AM -0500, Paul G wrote:
ORM features should work without problems but you'll have to use the 'eager' query mode everytime since retrieving stuff lazily means running queries for attribute access and this is what you want to avoid for blocking reasons. If you use fetchall() and the eager query mode for the ORM then SQLAlchemy works as well (if not better) than other solutions. I save Axiom from this because it has a particular attention to integration with Twisted (like powerups and so on). -- Valentino Volonghi aka Dialtone Now Running MacOSX 10.4 Blog: http://vvolonghi.blogspot.com http://weever.berlios.de
----- Original Message ----- From: "Valentino Volonghi aka Dialtone" <dialtone@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Tuesday, January 17, 2006 11:47 AM Subject: Re: [Twisted-Python] SQL Abstraction Layer On Tue, Jan 17, 2006 at 08:58:36AM -0500, Paul G wrote:
correct me if i'm wrong, but hacking/monkeypatching the sqlalchemy accessors which implement this lazy load when attributes are accessed to run their queries with deferToThread() would remove this problem, would it not? more generally speaking, if you're not going to do explicit deferToThread()s in your code (and avoid all attribute access), but rather, say, insert them into the appropriate point in sqlalchemy (sqlengine seems ro tbe the right place, or just a wrapper around the dbapi module), would this not handle the issue as well? am i asking the wrong person/on the wrong list? ;] -p
On Tue, Jan 17, 2006 at 09:34:11PM -0500, Paul G wrote:
I don't think it's the right approach to patch sqlalchemy to run attribute access in a thread. You would still get a deferred from an attribute access and this is pretty hugly to handle. Just using the eager mode would be perfect, the reason why I don't believe that much in lazy queries is that you will end up using all the information, otherwise it's better to query without using the ORM. Currently SQLAlchemy is so good because it doesn't try to be clever but rather only exposes the primitives and tries hard to be thread safe so that people will be free to use it in the best way they want. -- Valentino Volonghi aka Dialtone Now Running MacOSX 10.4 Blog: http://vvolonghi.blogspot.com http://weever.berlios.de
----- Original Message ----- From: "Valentino Volonghi aka Dialtone" <dialtone@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Wednesday, January 18, 2006 5:28 AM Subject: Re: [Twisted-Python] SQL Abstraction Layer On Tue, Jan 17, 2006 at 09:34:11PM -0500, Paul G wrote:
i'm not sure we're on the same page here. alternatively, i don't agree with your reasoning. if we use eager mode, objects get created when we call the mapper. in lazy mode, they can appear as a result of attribute access. to me, the fact that the two are different are a language syntax issue - semantically, i see both as a message to a slot on an object. this is, iirc, how python allows sqlalchemy to intercept with attribute accessors as well ;) to me, integrating sqlalchemy into twisted would ideally work in a way where all sqlalchemy api access is async. as i stated in my original mail, i currently believe that this could be possible to achieve by making all of sqlalchemy's calls into the dbapi module async with deferToThread(). if one does this, and it doesn't break something arcane in sqlalchemy, we shouldn't have to worry about deferreds in the client code. is there a reason why this wouldn't work or why it shouldn't be done that i am missing? you could advance the argument that not having a deferToThread() in the client code would obscure the fact that the current 'cooperative thread' is yielding execution at that point, but that can be elucidated in the comments should one have a need to do that, while keeping the code much cleaner.
there are plenty of examples where lazy loading is useful in an ORM context and where you wouldn't want to do your own queries. in fact, i don't see why wanting to control loading behaviour would make the advantages of an ORM suddenly go away - to me, the principal advantage is making the code cleaner and, in the common case, masking the stuff that's going on under the hood. this becomes even more important, imho, when you want to lazy load things - not less.
full agreement here. i love the way it's designed - simple, gives you access to the various levels of functionality independent of the others and leaves you free to use it however works best for you at any given point in time/code. it's one of the best ORMs, design-wise, that i've seen; the only thing that's missing is some docs for the UnitOfWork stuff. -p
On Wed, 18 Jan 2006 18:25:17 -0500, Paul G <paul-lists@perforge.com> wrote:
I think you are missing the fact that if you do this, attribute access will result in a Deferred, not the value of the attribute from the database, which is not available yet. So client code will have to deal with Deferreds, and in an extremely unusual manner - every attribute lookup will return a new Deferred. Jean-Paul
----- Original Message ----- From: "Jean-Paul Calderone" <exarkun@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Wednesday, January 18, 2006 7:10 PM Subject: Re: making sqlalchemy work with twisted (was Re: [Twisted-Python]SQL Abstraction Layer_
this would be very much like the 'future' in the actor-based concurrency model, so nothing terribly unusual. however, no, this is not what i want to do. maybe my (bad) ascii art will help: normal: attribute access -> sqlalchemy accessor -> sqlalchemy sqlengine -> synchronous db query to dbapi -> return to sqlengine -> return to accessor -> return attribute new: attribute access -> sqlalchemy accessor -> sqlalchemy sqlengine -> async call into dbapi with deferToThread-> control returned to reactor -> another coop thread gets control ... async dbapi result handler -> return to sqlengine -> return to accessor -> return attribute did i explain what i mean well? -p
On Wed, 18 Jan 2006 19:31:20 -0500, Paul G <paul-lists@perforge.com> wrote:
Yes. Unfortunately, this cannot be implemented in CPython without going to extreme lengths. Also, there is some discussion among the core Twisted developers whether it even represents a good idea at all. I think the split is currently something like 4 to 1 against. If you look in either my blog or glyph's blog for "concurrency" you will find some exposition on the matter. Jean-Paul
On Wed, 18 Jan 2006 19:55:32 -0500, Jean-Paul Calderone <exarkun@divmod.com> wrote:
Actually the thing you want to look for in my blog is "Santa Claus". The best post on this topic is "Knowing Santa Claus is Fake Doesn't Ruin Christmas", at http://www.livejournal.com/users/glyf/40037.html It should probably be on twistedmatrix.com's front page or something.
----- Original Message ----- From: "Jean-Paul Calderone" <exarkun@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Wednesday, January 18, 2006 7:55 PM Subject: Re: making sqlalchemy work with twisted (was Re: [Twisted-Python]SQLAbstraction Layer_
it just occurred to me what i was missing. namely, while the dbapi wrapper can deferToThread() for calls into the real dbapi module, there's no good/obvious way to yield control to the reactor and have the deferred's callback return execution to the callsite which invoked the dbapi wrapper in the first place. is this what you are alluding to? if so, i suspect there might be some generator magic which can make this work. is this what you meant by 'extreme lengths'?
there are indeed very good arguments for not doing this in the general case. i believe the decision to make pb users be aware that the objects are remote follows from that. i've found that this makes using pb easier and apps using it easier to design (from a performance risk perspective) and easier to debug, as well as more robust. i wouldn't argue for making attribute access implicitly do asynchronous things in the general case, or in most cases even. however, in the sqlalchemy case, at least for my purposes, it would make sense to do it to make the integration more seamless. -p
On Wed, 18 Jan 2006 21:15:28 -0500, Paul G <paul-lists@perforge.com> wrote:
No, generators are easy. Extreme lengths are extension modules that move pieces of the C call stack around. See Stackless Python and the greenlets module.
You may have more reasons to claim this than you have presented, but what you have presented reduces to "it is a good idea in this case because I think it is a good idea". I still think it's a bad idea :) Jean-Paul
----- Original Message ----- From: "Jean-Paul Calderone" <exarkun@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Wednesday, January 18, 2006 10:32 PM Subject: Re: making sqlalchemy work with twisted (was Re:[Twisted-Python]SQLAbstraction Layer_
yeah, i've looked at those in the past. are you saying they are the only way to do this and generators won't work?
nail. head. ;] in short, i trust myself to know that in those cases, an async call happens. this wouldn't be good in a general use framework, but for an internal product, the cleanliness of the code (and ability to do lazy loading) tip the scale, imo. so, outside of greenlets (reportedly, they make weird things happen from time to time, which isn't acceptable) and stackless (sexy, but unsupported/not actively developed/does it still work/will it stop working?), is there a way to get this done? -p
On Wed, 18 Jan 2006 22:55:43 -0500, Paul G <paul-lists@perforge.com> wrote:
Weird things, you say? I can't imagine... I'm sure you could find or write a solution which is isomorphic to greenlets, but that isn't greenlets. Or, wait, could you? I think the conclusion to this thread belongs in alt.coding.philosophy. Jean-Paul
----- Original Message ----- From: "David Blewett" <david@dawninglight.net> To: <twisted-python@twistedmatrix.com> Sent: Tuesday, January 17, 2006 8:14 AM Subject: [Twisted-Python] SQL Abstraction Layer
sqlalchemy's 'query building' looks to be doing the same job better, imo. it also supports more than just postgres. there was an example of integrating it with twisted earlier in the thread for this type of usage. -p
On Tue, 17 Jan 2006 08:14:12 -0500, David Blewett <david@dawninglight.net> wrote:
Any thoughts?
The main thought I have is "No" If anything, we should be moving towards *removing* some functionality like this (twisted.enterprise.row) which is unmaintained, poorly documented, poorly tested... It's perfectly possible to use Twisted with an ORM or SQL sweetener of your choice. If one is to become "officially" supported, it would probably be Axiom <http://divmod.org/trac/wiki/DivmodAxiom>, just due to the familiarity that several of the core Twisted developers have with it - but that's not really sufficiently general for inclusion in Twisted at this point. Look for it in the next 5 years, maybe.
----- Original Message ----- From: <glyph@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Tuesday, January 17, 2006 8:35 AM Subject: Re: [Twisted-Python] SQL Abstraction Layer
It's perfectly possible to use Twisted with an ORM or SQL sweetener of your choice.
what would help, i think, is some documentation/examples describing how this can be achieved with specific implementations. there's no reason this has to be in the official twisted documentation or written by twisted folks, necessarily, it would be good for it to just exist ;) dialtone's italian post shows the minimalistic example, but as he (through the translation) states, it probably won't be anywhere near as simple to use sqlalchemy's ORM features. i am unsure whether i'll be using a fullblown ORM for the stuff i'm working on or just some query building facilities, but if nothing happens documentation-wise before i cross that bridge, i'll put up some examples of what i ended up doing. alternatively, does someone have experience/insights to share but has been keeping it on the down low? now's the time to come on out with it ;) -p
Worth looking at: http://www.sqlobject.org I've only been tinkering for a few days, and I haven't done anything complicated with it (yet), but so far I am pleased. On 1/17/06, Paul G <paul-lists@perforge.com> wrote:
Paul G wrote:
My twisted app uses both query-building and an ORM. If anyone wants a look, a nightly tarball can be downloaded from http://ned.gsfc.nasa.gov/PanGalactic.tar.gz. Query-mapping is in pangalactic.utils.sql ORM is in pangalactic.repo.chronosynclastic The sql.py module is easily usable outside of pangalactic, and is just a way of building SQL statements from functions -- pretty rudimentary, and only tested with postgresql. The ORM is simple but is heavily dependent on the somewhat unorthodox way pangalactic does metaobjects, so probably not usable outside of pangalactic, but there might be some ideas that could be used. Docs (such as they are): PanGalactic/doc/PgefManual.html Comments welcome! Cheers, Steve
On Tue, Jan 17, 2006 at 08:58:36AM -0500, Paul G wrote:
ORM features should work without problems but you'll have to use the 'eager' query mode everytime since retrieving stuff lazily means running queries for attribute access and this is what you want to avoid for blocking reasons. If you use fetchall() and the eager query mode for the ORM then SQLAlchemy works as well (if not better) than other solutions. I save Axiom from this because it has a particular attention to integration with Twisted (like powerups and so on). -- Valentino Volonghi aka Dialtone Now Running MacOSX 10.4 Blog: http://vvolonghi.blogspot.com http://weever.berlios.de
----- Original Message ----- From: "Valentino Volonghi aka Dialtone" <dialtone@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Tuesday, January 17, 2006 11:47 AM Subject: Re: [Twisted-Python] SQL Abstraction Layer On Tue, Jan 17, 2006 at 08:58:36AM -0500, Paul G wrote:
correct me if i'm wrong, but hacking/monkeypatching the sqlalchemy accessors which implement this lazy load when attributes are accessed to run their queries with deferToThread() would remove this problem, would it not? more generally speaking, if you're not going to do explicit deferToThread()s in your code (and avoid all attribute access), but rather, say, insert them into the appropriate point in sqlalchemy (sqlengine seems ro tbe the right place, or just a wrapper around the dbapi module), would this not handle the issue as well? am i asking the wrong person/on the wrong list? ;] -p
On Tue, Jan 17, 2006 at 09:34:11PM -0500, Paul G wrote:
I don't think it's the right approach to patch sqlalchemy to run attribute access in a thread. You would still get a deferred from an attribute access and this is pretty hugly to handle. Just using the eager mode would be perfect, the reason why I don't believe that much in lazy queries is that you will end up using all the information, otherwise it's better to query without using the ORM. Currently SQLAlchemy is so good because it doesn't try to be clever but rather only exposes the primitives and tries hard to be thread safe so that people will be free to use it in the best way they want. -- Valentino Volonghi aka Dialtone Now Running MacOSX 10.4 Blog: http://vvolonghi.blogspot.com http://weever.berlios.de
----- Original Message ----- From: "Valentino Volonghi aka Dialtone" <dialtone@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Wednesday, January 18, 2006 5:28 AM Subject: Re: [Twisted-Python] SQL Abstraction Layer On Tue, Jan 17, 2006 at 09:34:11PM -0500, Paul G wrote:
i'm not sure we're on the same page here. alternatively, i don't agree with your reasoning. if we use eager mode, objects get created when we call the mapper. in lazy mode, they can appear as a result of attribute access. to me, the fact that the two are different are a language syntax issue - semantically, i see both as a message to a slot on an object. this is, iirc, how python allows sqlalchemy to intercept with attribute accessors as well ;) to me, integrating sqlalchemy into twisted would ideally work in a way where all sqlalchemy api access is async. as i stated in my original mail, i currently believe that this could be possible to achieve by making all of sqlalchemy's calls into the dbapi module async with deferToThread(). if one does this, and it doesn't break something arcane in sqlalchemy, we shouldn't have to worry about deferreds in the client code. is there a reason why this wouldn't work or why it shouldn't be done that i am missing? you could advance the argument that not having a deferToThread() in the client code would obscure the fact that the current 'cooperative thread' is yielding execution at that point, but that can be elucidated in the comments should one have a need to do that, while keeping the code much cleaner.
there are plenty of examples where lazy loading is useful in an ORM context and where you wouldn't want to do your own queries. in fact, i don't see why wanting to control loading behaviour would make the advantages of an ORM suddenly go away - to me, the principal advantage is making the code cleaner and, in the common case, masking the stuff that's going on under the hood. this becomes even more important, imho, when you want to lazy load things - not less.
full agreement here. i love the way it's designed - simple, gives you access to the various levels of functionality independent of the others and leaves you free to use it however works best for you at any given point in time/code. it's one of the best ORMs, design-wise, that i've seen; the only thing that's missing is some docs for the UnitOfWork stuff. -p
On Wed, 18 Jan 2006 18:25:17 -0500, Paul G <paul-lists@perforge.com> wrote:
I think you are missing the fact that if you do this, attribute access will result in a Deferred, not the value of the attribute from the database, which is not available yet. So client code will have to deal with Deferreds, and in an extremely unusual manner - every attribute lookup will return a new Deferred. Jean-Paul
----- Original Message ----- From: "Jean-Paul Calderone" <exarkun@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Wednesday, January 18, 2006 7:10 PM Subject: Re: making sqlalchemy work with twisted (was Re: [Twisted-Python]SQL Abstraction Layer_
this would be very much like the 'future' in the actor-based concurrency model, so nothing terribly unusual. however, no, this is not what i want to do. maybe my (bad) ascii art will help: normal: attribute access -> sqlalchemy accessor -> sqlalchemy sqlengine -> synchronous db query to dbapi -> return to sqlengine -> return to accessor -> return attribute new: attribute access -> sqlalchemy accessor -> sqlalchemy sqlengine -> async call into dbapi with deferToThread-> control returned to reactor -> another coop thread gets control ... async dbapi result handler -> return to sqlengine -> return to accessor -> return attribute did i explain what i mean well? -p
On Wed, 18 Jan 2006 19:31:20 -0500, Paul G <paul-lists@perforge.com> wrote:
Yes. Unfortunately, this cannot be implemented in CPython without going to extreme lengths. Also, there is some discussion among the core Twisted developers whether it even represents a good idea at all. I think the split is currently something like 4 to 1 against. If you look in either my blog or glyph's blog for "concurrency" you will find some exposition on the matter. Jean-Paul
On Wed, 18 Jan 2006 19:55:32 -0500, Jean-Paul Calderone <exarkun@divmod.com> wrote:
Actually the thing you want to look for in my blog is "Santa Claus". The best post on this topic is "Knowing Santa Claus is Fake Doesn't Ruin Christmas", at http://www.livejournal.com/users/glyf/40037.html It should probably be on twistedmatrix.com's front page or something.
----- Original Message ----- From: "Jean-Paul Calderone" <exarkun@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Wednesday, January 18, 2006 7:55 PM Subject: Re: making sqlalchemy work with twisted (was Re: [Twisted-Python]SQLAbstraction Layer_
it just occurred to me what i was missing. namely, while the dbapi wrapper can deferToThread() for calls into the real dbapi module, there's no good/obvious way to yield control to the reactor and have the deferred's callback return execution to the callsite which invoked the dbapi wrapper in the first place. is this what you are alluding to? if so, i suspect there might be some generator magic which can make this work. is this what you meant by 'extreme lengths'?
there are indeed very good arguments for not doing this in the general case. i believe the decision to make pb users be aware that the objects are remote follows from that. i've found that this makes using pb easier and apps using it easier to design (from a performance risk perspective) and easier to debug, as well as more robust. i wouldn't argue for making attribute access implicitly do asynchronous things in the general case, or in most cases even. however, in the sqlalchemy case, at least for my purposes, it would make sense to do it to make the integration more seamless. -p
On Wed, 18 Jan 2006 21:15:28 -0500, Paul G <paul-lists@perforge.com> wrote:
No, generators are easy. Extreme lengths are extension modules that move pieces of the C call stack around. See Stackless Python and the greenlets module.
You may have more reasons to claim this than you have presented, but what you have presented reduces to "it is a good idea in this case because I think it is a good idea". I still think it's a bad idea :) Jean-Paul
----- Original Message ----- From: "Jean-Paul Calderone" <exarkun@divmod.com> To: "Twisted general discussion" <twisted-python@twistedmatrix.com> Sent: Wednesday, January 18, 2006 10:32 PM Subject: Re: making sqlalchemy work with twisted (was Re:[Twisted-Python]SQLAbstraction Layer_
yeah, i've looked at those in the past. are you saying they are the only way to do this and generators won't work?
nail. head. ;] in short, i trust myself to know that in those cases, an async call happens. this wouldn't be good in a general use framework, but for an internal product, the cleanliness of the code (and ability to do lazy loading) tip the scale, imo. so, outside of greenlets (reportedly, they make weird things happen from time to time, which isn't acceptable) and stackless (sexy, but unsupported/not actively developed/does it still work/will it stop working?), is there a way to get this done? -p
On Wed, 18 Jan 2006 22:55:43 -0500, Paul G <paul-lists@perforge.com> wrote:
Weird things, you say? I can't imagine... I'm sure you could find or write a solution which is isomorphic to greenlets, but that isn't greenlets. Or, wait, could you? I think the conclusion to this thread belongs in alt.coding.philosophy. Jean-Paul
----- Original Message ----- From: "David Blewett" <david@dawninglight.net> To: <twisted-python@twistedmatrix.com> Sent: Tuesday, January 17, 2006 8:14 AM Subject: [Twisted-Python] SQL Abstraction Layer
sqlalchemy's 'query building' looks to be doing the same job better, imo. it also supports more than just postgres. there was an example of integrating it with twisted earlier in the thread for this type of usage. -p
participants (7)
-
David Blewett -
glyph＠divmod.com -
Jean-Paul Calderone -
Paul G -
Stephen Waterbury -
Tim Van Steenburgh -
Valentino Volonghi aka Dialtone