my first post, writing a Web interface to a PostgreSQL db
![](https://secure.gravatar.com/avatar/0d989ba45fd7f6799393af910e32a5c8.jpg?s=120&d=mm&r=g)
I need to write a Web interface to a PostgreSQL database and I am considering to use Twisted for that. So, I have a few questions. First of all, I would like to know if there already something available that I should look at. Second, I have a more specific question. Suppose I have a large table with 1,000,000 records and a SELECT query that returns 1000 results and takes 10 minutes. I don't want my users to wait. I want to display the result of the query as an HTML table of 50 rows. The user should click, wait 30 seconds, see the result, then click a 'next' button to see the other 50 results, etc. In other words, I am looking for a lazy query functionality. Is that already available? If not, is it easy/difficult to implement? Thanks for any suggestion, Michele Simionato
![](https://secure.gravatar.com/avatar/10aa56aec887e973085025c6ddfdc46b.jpg?s=120&d=mm&r=g)
On Fri, Oct 14, 2005 at 10:34:36AM +0000, Michele Simionato wrote: Hi Michele :),
I need to write a Web interface to a PostgreSQL database and I am considering to use Twisted for that. So, I have a few questions.
Let's see if I can answer all of them.
First of all, I would like to know if there already something available that I should look at. Second, I have a more specific question.
Depends on what 'Something' means in your view. There are 2 projects that I know of that work with twisted and databases (or postgresql) that are worth a try. The first one is called Tada (Twisted Async Database API): http://www.darkarts.co.za/projects/tada/ which is a new version of adbapi that instead of incredibly ignoring DB-API2 it makes it so that each DB-API2 method call returns a deferred. It also has an SQLObject-like way of dealing with the database that may be useful. Another effort is pgasync: svn://svn.jamwt.com/pgasync/trunk pgasync is a pure python database module that uses Twisted to implement the PostgreSQL protocol. It tries to be DB-API2 compliant although failing in some cases (not hard to fix but requires a bit of time that you may not have). Like Tada it returns deferreds from DB-API2 method calls, but manages to do some magic that avoids returning a deferred in some places. The last one is the standard abapi module that while maybe not beautiful it is indeed very simple and well tested in real life.
Suppose I have a large table with 1,000,000 records and a SELECT query that returns 1000 results and takes 10 minutes.
I don't want my users to wait. I want to display the result of the query as an HTML table of 50 rows. The user should click, wait 30 seconds, see the result, then click a 'next' button to see the other 50 results, etc. In other words, I am looking for a lazy query functionality.
Lazy query functionality requires you to store the cursor that run the query somewhere (togheter with the connection from which it was created) so that you can use fetchmany() on it. There is no other way that comes to my mind. I haven't experimented with this kind of jobs with any of the above, but I can guess that adbapi is not the right tool for the object, whereas pgasync or tada might be the right starting point (although they might reveal to be the wrong approach too).
Is that already available? If not, is it easy/difficult to implement?
It shouldn't be hard to implement as long as you don't need to migrate the cursor and the connection to some other machine. The idea that comes to mind is that you can store the connection with the cursor in the session (or something like that, maybe the session is not the right place but depends on the framework you are using) and reuse them for each user. Of course if you are in a load balanced environment then you need to be able to 'migrate' those connection from one computer to another one (unless you are using stateful load balancers) which is extremely hard. Another solution could be to take advantage of OFFSET and LIMIT keywords in SQL to avoid calculating the whole 1000 results but only limiting the resultset to the strictly necessary thus lowering response times. Hope I've been helpful :) -- Valentino Volonghi aka Dialtone Now Running MacOSX 10.4 Blog: http://vvolonghi.blogspot.com http://weever.berlios.de
![](https://secure.gravatar.com/avatar/0d989ba45fd7f6799393af910e32a5c8.jpg?s=120&d=mm&r=g)
On 10/14/05, Valentino Volonghi aka Dialtone <dialtone@divmod.com> wrote:
On Fri, Oct 14, 2005 at 10:34:36AM +0000, Michele Simionato wrote:
Hi Michele :)
Hi Valentino! (funny to write to you in a foreign language ;)
There are 2 projects that I know of that work with twisted and databases (or postgresql) that are worth a try.
I will have a look at them
Lazy query functionality requires you to store the cursor that run the query somewhere (togheter with the connection from which it was created) so that you can use fetchmany() on it. There is no other way that comes to my mind.
Trouble is, .fetchmany() does not work. It first perform the full query (having the user to wait 10 minutes in my example) and then it returns the results in bunches. Also LIMIT and OFFSET do not work. I want to resume an already started query, not to restart the full query and extracts a subset of it. I don't see any way to do that in the DB API 2.0, and I am beginning to suspect that it is much harder than I expected ... Michele Simionato
![](https://secure.gravatar.com/avatar/0d989ba45fd7f6799393af910e32a5c8.jpg?s=120&d=mm&r=g)
On 10/14/05, Valentino Volonghi aka Dialtone <dialtone@divmod.com> wrote:
The last one is the standard abapi module that while maybe not beautiful it is indeed very simple and well tested in real life.
Does the standard module have any introspection feature? I mean, for instance getting the field names? Using the DB API 2.0 interface I can extract the information from the cursor .description attribute, but how do I get the information from twisted.enterprise? Michele Simionato
![](https://secure.gravatar.com/avatar/10aa56aec887e973085025c6ddfdc46b.jpg?s=120&d=mm&r=g)
On Mon, Oct 17, 2005 at 09:34:20AM +0000, Michele Simionato wrote:
Does the standard module have any introspection feature? I mean, for instance getting the field names? Using the DB API 2.0 interface I can extract the information from the cursor .description attribute, but how do I get the information from twisted.enterprise?
With runInteraction you get a standard DB-API2 cursor on which you can call whatever you want. -- Valentino Volonghi aka Dialtone Now Running MacOSX 10.4 Blog: http://vvolonghi.blogspot.com http://weever.berlios.de
![](https://secure.gravatar.com/avatar/f9a435166977fabd5d9fdf4bba5d5459.jpg?s=120&d=mm&r=g)
This is very simple to do, using the LIMIT OFFSET functionality in Postgres. Just create a Resource which accepts a request arg indicating the offset, and then execute your query like so: cursor.execute('SELECT foo, bar from mytable LIMIT 50 OFFSET %d' % offset) As your users move forward and back through the dataset, you just change the offset. If you are using Postgres, you should look at pgasync (search the mailing lists), which is a fully async (no threading) adapter for use with twisted and Postgres; awesome stuff. The only real work to do here is to keep track of which offset your user currently is viewing, and that's trivial as well. Hope this helps, L. Daniel Burr P.S. There's an even cooler way to do this: http://openrico.org/rico/livegrid.page On Fri, 14 Oct 2005 05:34:36 -0500, Michele Simionato <michele.simionato@gmail.com> wrote:
I need to write a Web interface to a PostgreSQL database and I am considering to use Twisted for that. So, I have a few questions.
First of all, I would like to know if there already something available that I should look at. Second, I have a more specific question.
Suppose I have a large table with 1,000,000 records and a SELECT query that returns 1000 results and takes 10 minutes.
I don't want my users to wait. I want to display the result of the query as an HTML table of 50 rows. The user should click, wait 30 seconds, see the result, then click a 'next' button to see the other 50 results, etc. In other words, I am looking for a lazy query functionality.
Is that already available? If not, is it easy/difficult to implement?
Thanks for any suggestion,
Michele Simionato
_______________________________________________ Twisted-web mailing list Twisted-web@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-web
![](https://secure.gravatar.com/avatar/f9a435166977fabd5d9fdf4bba5d5459.jpg?s=120&d=mm&r=g)
My apologies, I didn't understand that you wanted to cache the entire dataset at the beginning. In that case, I'd select the data into a temporary table ( a temp table containing only 1000 records should be very fast to access, once it is populated) at startup time, and them use the LIMIT OFFSET strategy against the temporary table. Does this meet your need a little better? L. Daniel Burr On Fri, 14 Oct 2005 08:33:18 -0500, Michele Simionato <michele.simionato@gmail.com> wrote:
On 10/14/05, L. Daniel Burr <ldanielburr@mac.com> wrote:
This is very simple to do, using the LIMIT OFFSET functionality in Postgres.
No, doing a full select or a select with OFFSET takes the same amount of time, the query is restarted from the beginning.
_______________________________________________ Twisted-web mailing list Twisted-web@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-web
participants (3)
-
L. Daniel Burr
-
Michele Simionato
-
Valentino Volonghi aka Dialtone