On Sat, Jan 9, 2010 at 1:00 PM, Dennis Lee Bieber <wlfr...@ix.netcom.com>wrote:
> On Sat, 9 Jan 2010 10:28:31 -0500, Victor Subervi > <victorsube...@gmail.com> declaimed the following in > gmane.comp.python.general: > > > On Sat, Jan 9, 2010 at 10:14 AM, Iuri <iurisil...@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
-- http://mail.python.org/mailman/listinfo/python-list