database persistence with mysql, sqlite

[M.-A. Lemburg]

On 2007-09-23 01:11, coldpizza wrote:
> Hi,
> 
> I want to run a database query and then display the first 10 records
> on a web page. Then I want to be able to click the 'Next' link on the
> page to show the next 10 records, and so on.
> 
> My question is how to implement paging, i.e. the 'Next/Prev' NN
> records without reestablishing a database connection every time I
> click Next/Prev? Is it at all possible with cgi/mod_python?
> 
> For example, in a NON-web environment, with sqlite3 and most other
> modules, I can establish a database connection once, get a cursor
> object on which I run a single 'SELECT * FROM TABLE' statement and
> then use cursor.fetchmany(NN) as many times as there are still results
> left from the initial query.
> 
> How do I do the same for the web? I am not using any high-level
> framework. I am looking for a solution at the level of cgi or
> mod_python (Python Server Pages under Apache). To call
> cursor.fetchmany(NN) over and over I need to pass a handle to the
> database connection but how do I keep a reference to the cursor object
> across pages? I use mysql and sqlite3 as databases, and I am looking
> for an approach that would work with both database types (one at a
> time). So far I have successfully used the following modules for
> database access: sqlite3, mysqld, and pyodbc.
> 
> So far, with mysql I use 'SELECT * FROM TABLE LIMIT L1, L2' where L1
> and  L2 define the range for the 'Next' and 'Previous' commands. I
> have to run the query every time a click a 'Next/Prev' link. But I am
> not sure that this is the best and most efficient way. I suppose using
> CURSOR.FETCHMANY(NN) would probably be faster and nicer but how do I
> pass an object reference across pages? Is it possible without any
> higher-level libraries?
> 
> What would be the proper way to do it on a non-enterprise scale?

Depends on what "enterprise" scale means to you :-)

The easiest way to get excellent performance for such queries is
using a long running process, mod_scgi and have the browser
send a session cookie for you to use to identify the request.
You can then open the connection and keep it open while the user
browses the site.

If you want to save yourself from most of the details,
just use Zope or Plone + e.g. our mxODBC Zope DA for the
database connection (it works with all the databases
you mention on Windows, Linux and Mac OS X).

Even if you don't want to code things using Zope/Plone,
you should still consider it for taking care of all the
middleware logic and then write your application as
separate package which you hook into Zope/Plone using
"external methods" or "Python scripts" (in their Zope
sense).

> Would SqlAlchemy or SqlObject make things easier with regard to
> database persistence?

Not really: they don't provide the session mechanisms you
would need.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Sep 25 2007)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611