[Twisted-Python] Simplifying database access in Twisted
Just read thru http://www.twistedmatrix.com/documents/howto/enterprise and tried to fit the code to my needs, but ... this seems way too complicated. All I want is to execute a SQL-query and do something similar to fetchone or fetchall etc. in the DB-API 2.0 spec. I don't want to be forced to write classes and callbacks etc. just for a simple list of tuples or something returned from a SQL-query. Is there no other way to do simple database access in Twisted? Something more like the DB-API 2.0? The docs says "This is straightforward ...". No, it's not. The example a bit further up on the same page, now that's straightforward. Perhaps it's just me not getting all the callbacks and whatnot, but this seems too hard, too much code for such a simple task. All I want is a database-object to feed sql-statements, which returns the results as dictionaries or list of tuples, as close to DB-API 2.0 as possible. Any hint or clue would be highly appreciated. PS! I don't mean to step on anyones toes or anything, so forgive me if I'm a bit harsh. But this might be the bitter end of my journey into the realms of Twisted. Quick, fast and easy access to a database will make-or-break all of my current projects, all of which are based on Twisted. Thomas
On Thursday, Jun 19, 2003, at 17:13 America/New_York, Thomas Weholt wrote:
Just read thru http://www.twistedmatrix.com/documents/howto/enterprise and tried to fit the code to my needs, but ... this seems way too complicated. All I want is to execute a SQL-query and do something similar to fetchone or fetchall etc. in the DB-API 2.0 spec. I don't want to be forced to write classes and callbacks etc. just for a simple list of tuples or something returned from a SQL-query.
Is there no other way to do simple database access in Twisted? Something more like the DB-API 2.0? The docs says "This is straightforward ...". No, it's not. The example a bit further up on the same page, now that's straightforward. Perhaps it's just me not getting all the callbacks and whatnot, but this seems too hard, too much code for such a simple task. All I want is a database-object to feed sql-statements, which returns the results as dictionaries or list of tuples, as close to DB-API 2.0 as possible. Any hint or clue would be highly appreciated.
PS! I don't mean to step on anyones toes or anything, so forgive me if I'm a bit harsh. But this might be the bitter end of my journey into the realms of Twisted. Quick, fast and easy access to a database will make-or-break all of my current projects, all of which are based on Twisted.
Here are some doc-examplets not using Augmentation. from twisted.enterprise import adbapi noop = lambda x:x cp = adbapi.ConnectionPool('pyPgSQL.PgSQL', '::yourdatabase::') deferred = cp.interaction(yourcallback, noop, *yourcallbackargs, **yourcallbackkwargs) # pass in a callback that receives a cursor as its first argument, DBAPI your little heart out.# # returns whatever you return in your callback. # note that 'yourcallback' does not run in the main reactor thread! be careful not to try and call into it directly from here. deferred = cp.operation(noop, noop, *executeargs, **executekwargs) # does cursor.execute(*executeargs, **executekwargs) # returns a deferred that calls back with None. It fetches no data at all, useful for insert/update/stored procedure type stuff. deferred = cp.query(noop, noop, *executeargs, **executekwargs) # does cursor.execute(*executeargs, **executekwargs) # returns a deferred that calls back with the fetchall() noop is used to make these things act more like newer Twisted interfaces that were designed with Deferred in mind. Presumably if all of your current projects are based on Twisted, you're comfortable enough with Deferred, so I won't go into that. -bo
On Thu, 19 Jun 2003 23:13:07 +0200 "Thomas Weholt" <2002@weholt.org> wrote:
Just read thru http://www.twistedmatrix.com/documents/howto/enterprise and tried to fit the code to my needs, but ... this seems way too complicated. All I want is to execute a SQL-query and do something similar to fetchone or fetchall etc. in the DB-API 2.0 spec. I don't want to be forced to write classes and callbacks etc. just for a simple list of tuples or something returned from a SQL-query.
I updated the docs in CVS so they don't discuss Augmentation and instead talk about the nice clean API that ConnectionPool exposes. -- Itamar Shtull-Trauring http://itamarst.org/ http://www.zoteca.com -- Python & Twisted consulting
On Thu, Jun 19, 2003 at 08:58:11PM -0400, Itamar Shtull-Trauring wrote:
On Thu, 19 Jun 2003 23:13:07 +0200 "Thomas Weholt" <2002@weholt.org> wrote:
Just read thru http://www.twistedmatrix.com/documents/howto/enterprise and tried to fit the code to my needs, but ... this seems way too complicated. All I want is to execute a SQL-query and do something similar to fetchone or fetchall etc. in the DB-API 2.0 spec. I don't want to be forced to write classes and callbacks etc. just for a simple list of tuples or something returned from a SQL-query.
I updated the docs in CVS so they don't discuss Augmentation and instead talk about the nice clean API that ConnectionPool exposes.
Does this mean that Augmentation should raise DeprecationWarnings? -Andrew.
On Thursday, June 19, 2003, at 04:13 PM, Thomas Weholt wrote:
Is there no other way to do simple database access in Twisted? Something more like the DB-API 2.0?
Feel free to just use DB-API modules directly, if that's appropriate. twisted.enterprise exists to facilitate non-blocking access; if your database queries are quick, you can block your server. The API certainly is a bit grotty from disuse; a few cleanups and some better documentation would probably be better. Speaking of being grotty - Bob's examples were correct, but there is a more natural API that you can use. (The methods he suggested using pre-date Deferreds.) Assuming you want to run a bunch of code which munges some data from a database, you can write something like this: -- def myInteraction(t): t.execute('select * from my_numbers') # need some arbitrary code, so I'll sum these in memory rather than db sum = 0 while True: row = t.fetchone() if not row: break num = row[0] sum += num return sum def printx(x): print x cp.runInteraction(myInteraction).addCallback(printx) -- Assuming that your 'interaction' method doesn't touch any non-thread-safe state, this will work for pretty much any massaging you want to do to your data. Since it's a Deferred, you can return it to various places in Twisted that can already manage waiting on deferreds for you, including woven. runInteraction is probably the only method you really need, and it has the added benefit of allowing you to combine reads and writes in a single function, and rolling back any changes if there is a bug in your Python code.
PS! I don't mean to step on anyones toes or anything, so forgive me if I'm a bit harsh. But this might be the bitter end of my journey into the realms of Twisted. Quick, fast and easy access to a database will make-or-break all of my current projects, all of which are based on Twisted.
I can understand your frustration (twisted.enterprise is crap, especially the docs), but this is a bit much. If you feel you're getting to this point with any bit of Twisted, remember that it's all just python code. Twisted provides these components together in order to ease deployment and provide as complete an environment as possible, not to ram them down your throat. There are many parts of Twisted which could use improvement, and we've attempted to do quite a few things that we are not expert at. Certainly implementing your own database threadpool on top of reactor.callFromThread/callInThread or making database calls synchronously is easier than throwing out all of Twisted and starting over.
Thomas, If you have *big* result sets which you have to process incrementally, then you may want to look at flow.py in the sandbox. It has a QueryIterator that I use, essentially it executes the query in a separate thread, and you can use generators to write your data processing code. When I get time, I intend to hook in postgresql's async query library so that threads are not required. Best, Clark
participants (6)
-
Andrew Bennetts -
Bob Ippolito -
Clark C. Evans -
Glyph Lefkowitz -
Itamar Shtull-Trauring -
Thomas Weholt