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


Fast, Easy Database Access with Python : Page 4

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.

Searching and Sorting
So far, this article has ignored two of the most important aspects of databases. Searching and sorting, or finding and arranging data, let you narrow and organize results automatically. If you were writing an address book application, the user would often ask questions such as "Who do I know named 'Bob'?" and expect to get a sorted list. The following code is a simple and feasible way to implement such a query:
for c in contacts:
   print "%s %s: %s" % (c[FNAME], c[LNAME], c[PHONE]) 
The output would print a list of "Bob" names and phone numbers, sorted by last name:

   Bob Barker: 123-4567
   Bob Dobbs: 234-5678
   Bob Monroe: 345-6789
   Bob Zemeckis: 456-7890
Python does not provide operators for searching or sorting, so a class method must suffice. These methods are very simple, because SQL implements them as parameters to SELECT instead of allocating separate commands for them. The only work the search and sort methods need to do is save data for later use:
def search(self, method):
   self._search = ""
   if method: self._search = "where %s" % (method)

def sort(self, method):
   self._sort = ""
   if method: self._sort = "order by %s" % (method) 
Making these functions actually do something requires modifying the other class methods. The self._search and self._sort attributes must be defined, and inserted into queries where appropriate. The following methods should be modified:
  • Table.__init__(): Set self._search = "" and self._sort = "" to avoid accessing them before they are defined.
  • Table.__len__(): Change the query to: self._query("select count(*) from %s %s" % (self.name, self._search))
  • Table.__iter__(): Change the query to: q = "select * from %s %s %s" % (self.name, self._search, self._sort)
  • Table.__getitem__(): Change the first line to: q = "select * from %s %s %s" % (self.name, self._search, self._sort)
Now, at this point, you may have noticed that some SQL slipped into the interface for the Table class. The parameters for Table.search() and Table.sort() are exactly what you would write in a WHERE or ORDER BY clause in a normal query. The table's name is also placed directly into the query. This affords some interesting opportunities, or tricks.

Some simple tricks include sorting by several columns, or searching with more than one condition. A more complicated trick is to turn the Table into something like a view, by connecting it to more than one database table. The following code demonstrates all three techniques:
books = Table(db, "book, series")
books.search("book.series=series.id AND book.publish_year>1995")
books.sort("series.name, book.publish_year")

for b in books:
   print "%s: %s (%s)" % (b[SERIES_NAME], 
      b[BOOK_TITLE], b[BOOK_YEAR]) 
The preceding code tells Python to get rows from two tables, book and series. The search method then adds two additional instructions—join the tables by using the series ID, and ignore anything from 1995 or earlier. Then sort the results first by the name of the series, and then by the year each book was published. This has the effect of grouping related items together and showing them in chronological order. The code then chooses three interesting columns from the data, and prints the information in a human-readable form.

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