[DB-SIG] how greedy should cursor.fetchmany be?

Tony Locke tlocke at tlocke.org.uk
Wed Jun 11 07:55:46 CEST 2014

Hi, I would say that a user of your driver would expect the full
number of rows specified in fetchmany to be returned (if the rows are
there). So my view would be to stick to PEP 249 in this case.



On 11 June 2014 02:06, Daniel Lenski <dlenski at gmail.com> wrote:
> I'm writing a DBAPI module for a custom database wrapper. The way I get data
> from a query is to ask the underlying database it for a chunk of rows, at
> which point it spits out an unpredictable number (it's a black box to me).
> I'm trying to figure out how to implement cursor.fetchmany on top of this,
> and make it as efficient as possible. There are two limiting cases:
> Python caller asks for the next 10 rows, but the DB gives the next 100 rows.
> Obviously, I just cache the extra rows in this case.
> Python caller asks for the next 100 rows, but the next call to the DB only
> gives me 10 rows.
> In case (2), PEP-249 seems to be telling me that I should keep calling the
> DB to get more rows:
> The number of rows to fetch per call is specified by the parameter. If it is
> not given, the cursor's arraysize determines the number of rows to be
> fetched. The method should try to fetch as many rows as indicated by the
> size parameter. If this is not possible due to the specified number of rows
> not being available, fewer rows may be returned.
> However, I'd rather implement it slightly differently: "... fetch as many
> rows as possible without incurring multiple calls to the underlying DB
> engine, but no more than indicated by the size parameter."
> Having a loop to make multiple DB calls hidden in the DBAPI module, rather
> than in the client code, strikes me as a bad approach because it adds extra
> unpredictable latency. A properly-written client will have to do some
> looping anyway:
> # client code to get next 10k rows from the cursor
> rows = []
> while True:
>     # PEP-249 says that fetchmany should do its best to get as many rows as
> requested
>     # ... but that will mean a similar or identical loop in the DBAPI module
>     more_rows = cur.fetchmany(len(rows) - 10000)
>     if not more_rows:
>         break
>     else:
>         rows += more_rows
> What do you think? Is there some good reason to adhere strictly to PEP-249?
> Thanks,
> Dan
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> https://mail.python.org/mailman/listinfo/db-sig

More information about the DB-SIG mailing list