Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Fast, Easy Database Access with Python : Page 3

You don't have to keep writing the same old database access code over and over again—instead, it's simple to roll your own database wrapper and use Python syntax to retrieve, search, and update data. Implement the classes and methods shown here to speed up your Python database development process.

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]: print book

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) self._query(q) return self.dbc.fetchall() elif isinstance(item, types.IntType): q = q + " limit %s, 1" % (item) self._query(q) return self.dbc.fetchone() else: raise IndexError, "unsupported index type"

The Slice object contains three attributes: start, stop, and step. The step part will probably not be useful, because SQL databases don't generally support step sizes other than one. However, start and stop are useful, and translate easily into arguments to MySQL's LIMIT parameter. The start attribute is used directly as the first parameter. Subtracting start from stop produces the second parameter—length.

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 correct—accidentally 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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date