473,287 Members | 1,588 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

database persistence with mysql, sqlite

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?

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

Sep 22 '07 #1
4 3011
In message <11**********************@22g2000hsm.googlegroups. com>, coldpizza
wrote:
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.
Try it first, then see what happens. Remember, premature optimization is the
root of all (programming) evil.
Sep 24 '07 #2
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.

Apache/cgi just dont work this way. When apache receives a new request
(a cgi being called), it starts a new thread, it execute him, and gives
the client some result. AND THEN KILL THE THREAD. Altough i never used
it, what i think you need is fast cgi (fcgi), wich takes care of
persistent connections to a web server.

Cheers.
Gerardo
Sep 24 '07 #3
On Sep 24, 7:23 am, Lawrence D'Oliveiro <l...@geek-
central.gen.new_zealandwrote:
In message <1190502677.970811.181...@22g2000hsm.googlegroups. com>, coldpizza
wrote:
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.
Try it first, then see what happens. Remember, premature optimization is the
root of all (programming) evil.
It turned out that the method above ('SELECT * FROM TABLE LIMIT L1,
L2') works ok both with mysql and sqlite3, therefore I have decided to
stick with it until I find something better. With Sqlite3 you are
supposed to use LIMIT 10 OFFSET NN, but it also apparently supports
the mysql syntax (LIMIT NN, 10) for compatibility reasons.

Sep 24 '07 #4
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
Sep 25 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: chris | last post by:
how would i duplicate a database content into another database i have ssh access and want to do this without downloading the database and uploading it again thanks
6
by: ionel | last post by:
I'm looking for a thread-safe database. Preferably an embedded, sql database. What are the best choices in terms of speed ? -- ionel.
33
by: Alexander Muylaert | last post by:
Hi I'm looking for a small and light file structure for my app. I need to store a bunch of small streams. It is always like key - data. I need to be able to get, add, delete and update these...
6
by: nc | last post by:
Anyone able to help me out and tell me if there is a default datafile type installed with PHP? If there is no default datafile type, what would be the most common type of datafile and does the...
3
by: ljh | last post by:
If I am using VB.Net 2005 with SQL Express or Developer edition and want to embed the db into my project, how do I do that? I can't find anything on embedding the db into a VB.Net 2005 app...
19
by: jupiter | last post by:
Hi guys!!! Just one quick question... Which database module should I use when I want to use multi threading as my application requires lots of data from internet ???? I also want this database...
2
by: webcm123 | last post by:
People say that structural programming isn't good for database connection. I code fast-running structural oriented CMS and I don't know what I should do. I use mysql connection using mysql_*. I...
24
by: sonos | last post by:
Hi, I am working on a program to archive data to disk. At what point is it best to use a relational database like MySQL as the backend instead of a C program alone? Thanks to any and all...
17
by: darien.watkins | last post by:
Kindof a poll, kindof curiosity... What is your favorite python - database combination? I'm looking to make an app that has a local DB and a server side DB. I'm looking at python and sqlite...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.