Our first example showed how we can easily reduce our code volume. But it was really only for a simple case. A really useful operation would be to execute a query, and transform the results into a list of objects.
class Article(object): def __init__(self, id=None, title=None, wiki_text=None): self.id = id self.title = title self.wiki_text = wiki_text
cursor = db.cursor() results = [] try: try: cursor.execute("SELECT id, title, wiki_text FROM ARTICLE") temp = cursor.fetchall() for row in temp: results.append( Article(id=temp[0], title=temp[1], wiki_text=temp[2])) except Exception, e: print "execute: Trapped %s" % e finally: try: cursor.close() except Exception, e: print "close: Trapped %s, and throwing away" % e return results
This isn't that different from the earlier example. The key difference is that
instead of assigning fetchall
directly to results
, we instead iterate over it,
generating a list of Article
objects.
DatabaseTemplate
to cut down on the volume of code.return dt.query("SELECT id, title, wiki_text FROM ARTICLE", ArticleMapper())
ArticleMapper
, the object class used to iterate over our result set.from springpython.database.core import RowMapper class ArticleMapper(RowMapper): def map_row(self, row, metadata=None): return Article(id=row[0], title=row[1], wiki_text=row[2])
RowMapper
defines a single method: map_row
. This method is called for each row of data, and includes not only the information, but also the metadata provided by the database. ArticleMapper
can be re-used for every query that performs the same mapping.
Our class definition happens to have the same property names as the columns in our database. Spring Python offers SimpleRowMapper
as a convenient out-of-the-box mapper that takes advantage of this.
Instead of writing the specialized ArticleMapper
, let's use Spring Python's SimpleRowMapper
instead.
return dt.query("SELECT id, title, wiki_text FROM ARTICLE", SimpleRowMapper(Article))
SimpleRowMapper
requires that the class has a default constructor, and also that the class's properties match the query's.