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

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