Using DatabaseTemplate to retrieve objects

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.

  1. First, let's define a simple object we want to populate with the information retrieved from the database. As shown on the Spring triangle diagram, using simple objects is a core facet to the 'Spring way'.
    class Article(object):
def __init__(self, id=None, title=None, wiki_text=None):
self.id = id
self.title = title
self.wiki_text = wiki_text
  2. If we wanted to code this using Python's standard API, our code would be relatively verbose like this:
    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.

  3. Instead, let's use DatabaseTemplate to cut down on the volume of code.
    return dt.query("SELECT id, title, wiki_text FROM ARTICLE",
ArticleMapper())
  4. We aren't done yet. We have to code 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.

Note

This is slightly different from the parameterized example shown earlier where we defined a row-handling function. Here we define a class that contains the map_row function. But the concept is the same: inject a row-handler to convert the data.

Mapping queries by convention over configuration

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.

Note

It's important to remember that column-to-property matching is based on the query, not the table. This means we can use SQL aliasing to link up table columns with objects.

Mapping queries into dictionaries

Spring Python also offers the DictionaryRowMapper, which conveniently maps the query into a Python dictionary.

Instead of using the SimpleRowMapper, let's use Spring Python's DictionaryRowMapper instead.

return dt.query("SELECT id, title, wiki_text FROM ARTICLE",
DictionaryRowMapper())

Note

This last step breaks out of our original requirement to return a list of Article objects. But it is a convenient way of providing a simple 'window on data' scenario and may perfectly match our needs.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.219.71.21