sqlobject-discuss

[SQLObject] large retrieval of data

[Dave P. <dpo...@te...>]

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?

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__

Re: [SQLObject] large retrieval of data

[Ian B. <ia...@co...>]

On Tue, 2003-08-05 at 21:35, 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 presume from the example that it's actually the database driver that's
taking a long time for you -- or perhaps the underlying database takes a
long time to prepare the results (which is quite likely).  Did Frank's
suggestion to batch (i.e., using limit/offset) help?  If it's the
database that is the bottleneck, it might not (or even be worse), since
the database may take a long time to decide what portion of the results
to send.  Anyway, curious how it turned out.

  Ian