Something More Elegant

[Victor Subervi]

On Sat, Jan 9, 2010 at 1:00 PM, Dennis Lee Bieber <wlfraed at ix.netcom.com>wrote:

> On Sat, 9 Jan 2010 10:28:31 -0500, Victor Subervi
> <victorsubervi at gmail.com> declaimed the following in
> gmane.comp.python.general:
>
> > On Sat, Jan 9, 2010 at 10:14 AM, Iuri <iurisilvio at gmail.com> wrote:
> >
> > > And you should use cursor.fetchall() instead of cursor in list
> > > comprehension:
> > >
> > > packageIDs = [itm[0] for itm in cursor.fetchall()]
> > >
> >
> > Now, someone else on this list told me the other. Can you explain the
> > difference?
>
>         Since a (one-liner) list comprehension is going to result in
> processing all the data, it may be faster to fetch all the data at the
> start. Depending upon the implementation of the database API, iterating
> over a cursor object to retrieve one record at a time may result in a
> hit on the database engine (some engines may hold the data in the server
> and only transmit it record by record unless explicitly asked for all of
> it -- and if the database server is on a different machine that could
> make for a lot of slow network traffic).
>
>        Also, you need to take the database locking system into account --
> until you not only read all the data, but commit the transaction (even a
> read-only transaction), the engine may lock any other user from
> accessing those records (and some engines may lock the entire table, not
> just records).
>
>        Iterating over a cursor may be useful if: 1) you have a really
> massive database and your query is not filtering the data to manageable
> levels (better done by looping over the query itself using limit and
> offset features to control how many records are returned in a batch); 2)
> it is a single user/single access database where locking data won't have
> an impact; 3) you need to use the data in each record to update or
> otherwise perform some other query on the database (though unless this
> is done within the same transaction you might still have locking
> issues).
>
>
Wow! Thanks. I'll digest this over the weekend.
beno
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100109/2705ad7d/attachment-0001.html>