Using Data "Slices"
Databases tend to be large, and accessing an entire data set can be very slow. If you have 50,000 records in a table, and only need twenty of them right now, grabbing the whole set would be very inefficient. However, taking only one record at a time is also inefficient. To solve this, use slices. Python provides slicing for list-like objects, which can select a range of records quickly and easily.
The previous examples executed one database query for each row accessed in the table. Pulling tens or hundreds of records at a time would be more efficient though. The following demonstrates the syntax for taking a slice of a list, or a list-like object:
# create a list of all items to go on the third page
page3 = books[20:30]
# or, pull ten records into a list, and loop over them
for book in books[20:30]:
To implement slice support in the Table class, modify the __getitem__()
method. Both individual lookups and slice lookups are implemented with this method, and determining which type of result to return is simply a matter of checking whether the parameter is an integer or a slice. The built-in isinstance()
function, along with the types module
can help you decide what to do.
def __getitem__(self, item):
q = "select * from %s" % (self.name)
# isinstance is recommended over direct type(a) == type(b) comparisons,
# to accomodate derived classes
if isinstance(item, types.SliceType):
q = q + " limit %s, %s" % (item.start, item.stop - item.start)
elif isinstance(item, types.IntType):
q = q + " limit %s, 1" % (item)
raise IndexError, "unsupported index type"
The Slice object contains three attributes: start
, and step
. The step
part will probably not be useful, because SQL databases don't generally support step sizes other than one. However, start
are useful, and translate easily into arguments to MySQL's LIMIT
parameter. The start
attribute is used directly as the first parameter. Subtracting start
produces the second parameterlength.
Different databases use different syntax for selecting data slices, which can become confusing if you work with more than one. It can be difficult to remember if the LIMIT
function takes first, last
as arguments, or if it uses first, length
, or if it expects something else. This tends to cause silent errors in programs because the syntax is still correctaccidentally getting too many records, or too few, or even none at all. Hopefully, using the easy Slice syntax will help you avoid that type of error.