Re: [SQLObject] large retrieval of data

[Frank B. <fb...@fo...>]

Hallo,
Dave Poirier hat gesagt: // Dave Poirier wrote:

> I'm trying to import around 7 million rows from one database connection
> to another.  Due to the enormous quantity of data, displaying some
> progress to the user is a basic requirement.  I've tried various methods
> but it seems that SQLObject, even when using the _connection directly,
> fetch all the data from the database connection before returning from
> the execute statement.
> 
>   #! /usr/local/bin/python -u
> 
>   from myclasses import *
> 
>   connection = X_sqlobject._connection
>   c = connection.getConnection()
>   print "selecting..."
>   c.execute('SELECT * FROM remote_table;')
>   print "fetching..."
>   print c.fetchone()
> 
> I get the "selecting..." right away but it takes some serious time, with
> heavy network activity before "fetching..." ever appears.  Is there any
> way to use the database connection so as to be able to display progress
> while the records are being fetched?

I'd use a combination of COUNT and LIMIT to do what the SO docs call a 
"batched query" like 
* select count(*) from rtable
* store count, divide by 10k to get decent intervals
* select * from rtable limit(x,y)

Depending on the underlying database the limit-clause will be different.

In SQLObject this would be like this pseudocode: 

length = len( RemoteTable.select("all") )
steps = int( length/10000 )
start=0
results = []

while r = RemoteTable.select("all")[start:start + steps]:
	results.append(r)
	print "still selecting..."
	start += steps

ciao
-- 
 Frank Barknecht                               _ ______footils.org__