8 min read

(For more resources on Spring, see here.)

Many of our applications contain dynamic data that needs to be pulled from and stored within a relational database. Even though key/value based data stores exist, a huge majority of data stores in production are housed in a SQL-based relational database.

Given this de facto requirement, it improves developer efficiency if we can focus on the SQL queries themselves, and not spend lots of time writing plumbing code and making every query fault tolerant.

The classic SQL issue

SQL is a long existing standard that shares a common paradigm for writing queries with many modern programming languages (including Python). The resulting effect is that coding queries by hand is laborious. Let’s explore this dilemma by writing a simple SQL query using Python’s database API.

  • SQL is a long existing standard that shares a common paradigm for writing queries with many modern programming languages (including Python). The resulting effect is that coding queries by hand is laborious. Let’s explore this dilemma by writing a simple SQL query using Python’s database API.

    DROP TABLE IF EXISTS article;

    CREATE TABLE article (
    id serial PRIMARY KEY,
    title VARCHAR(11),
    wiki_text VARCHAR(10000)
    );

    INSERT INTO article
    (id, title, wiki_text
    VALUES
    (1,
    'Spring Python Book',
    'Welcome to the [http://springpythonbook.com Spring Python] book,
    where you can learn more about [[Spring Python]].');
    INSERT INTO article
    (id, title, wiki_text
    VALUES
    (2,
    'Spring Python',
    ''''Spring Python''' takes the concepts of Spring and
    applies them to world of [http://python.org Python].');

  • Now, let’s write a SQL statement that counts the number of wiki articles in the system using the database’s shell.

    SELECT COUNT(*) FROM ARTICLE

  • Now let’s write some Python code that will run the same query on an sqlite3 database using Python’s official database API (http://www.python.org/dev/peps/pep-0249).

    import sqlite3

    db = sqlite3.connect("/path/to/sqlite3db")

    cursor = db.cursor()
    results = None
    try:
    try:
    cursor.execute("SELECT COUNT(*) FROM ARTICLE")
    results = cursor.fetchall()
    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[0][0]

    That is a considerable block of code to execute such a simple query. Let’s examine it in closer detail.

  • First, we connect to the database. For sqlite3, all we needed was a path. Other database engines usually require a username and a password.
  • Next, we create a cursor in which to hold our result set.
  • Then we execute the query. To protect ourselves from any exceptions, we need to wrap this with some exception handlers.
  • After completing the query, we fetch the results.
  • After pulling the results from the result set into a variable, we close the cursor.
  • Finally, we can return our response. Python bundles up the results into an array of tuples. Since we only need one row, and the first column, we do a double index lookup.

What is all this code trying to find in the database? The key statement is in a single line.

cursor.execute("SELECT COUNT(*) FROM ARTICLE")

What if we were writing a script? This would be a lot of work to find one piece of information. Granted, a script that exits quickly could probably skip some of the error handling as well as closing the cursor. But it is still is quite a bit of boiler plate to just get a cursor for running a query.

But what if this is part of a long running application? We need to close the cursors after every query to avoid leaking database resources. Large applications also have a lot of different queries we need to maintain. Coding this pattern over and over can sap a development team of its energy.

Parameterizing the code

This boiler plate block of code is a recurring pattern. Do you think we could parameterize it and make it reusable? We’ve already identified that the key piece of the SQL statement. Let’s try and rewrite it as a function doing just that.

import sqlite3

def query(sql_statement):
db = sqlite3.connect("/path/to/sqlite3db")

cursor = db.cursor()
results = None
try:
try:
cursor.execute(sql_statement)
results = cursor.fetchall()
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[0][0]

Our first step nicely parameterizes the SQL statement, but that is not enough. The return statement is hard coded to return the first entry of the first row. For counting articles, what we have written its fine. But this isn’t flexible enough for other queries. We need the ability to plug in our own results handler.

import sqlite3

def query(sql_statement, row_handler):
db = sqlite3.connect("/path/to/sqlite3db")

cursor = db.cursor()
results = None
try:
try:
cursor.execute(sql_statement)
results = cursor.fetchall()
except Exception, e:
print "execute: Trapped %s" % e
finally:
try:
cursor.close()
except Exception, e:
print "close: Trapped %s, and throwing away" % e
return row_handler(results)

We can now code a custom handler.

def count_handler(results):
return results[0][0]

query("select COUNT(*) from ARTICLES", count_handler)

With this custom results handler, we can now invoke our query function, and feed it both the query and the handler. The only thing left is to handle creating a connection to the database. It is left as an exercise for the reader to wrap the sqlite3 connection code with a factory solution.

What we have coded here is essentially the core functionality of DatabaseTemplate. This method of taking an algorithm and parameterizing it for reuse is known as the template pattern. There are some extra checks done to protect the query from SQL injection attacks.

Replacing multiple lines of query code with one line of Spring Python

Spring Python has a convenient utility class called DatabaseTemplate that greatly simplifies this problem.

  1. Let’s replace the two lines of import and connect code from the earlier example with some Spring Python setup code.

    from springpython.database.factory import Sqlite3ConnectionFactory
    from springpython.database.core import DatabaseTemplate

    conn_factory = Sqlite3ConnectionFactory("/path/to/sqlite3db")
    dt = DatabaseTemplate(conn_factory)

    At first glance, we appear to be taking a step back. We just replaced two lines of earlier code with four lines. However, the next block should improve things significantly.

  2. Let’s replace the earlier coded query with a call using our instance of

    return dt.query_for_object("SELECT COUNT(*) FROM ARTICLE")

Now we have managed to reduce a complex 14-line block of code into one line of Spring Python code. This makes our Python code appear as simple as the original SQL statement we typed in the database’s shell. And it also reduces the noise.

The Spring triangle—Portable Service Abstractions

From this diagram earlier , as an illustration of the key principles behind Spring Python is being made.

The DatabaseTemplate represents a Portable Service Abstraction because:

  • It is portable because it uses Python’s standardized API, not tying us to any database vendor. Instead, in our example, we injected in an instance of Sqlite3ConnectionFactory
  • It provides the useful service of easily accessing information stored in a relational database, but letting us focus on the query, not the plumbing code
  • It offers a nice abstraction over Python’s low level database API with reduced code noise. This allows us to avoid the cost and risk of writing code to manage cursors and exception handling

DatabaseTemplate handles exceptions by catching and holding them, then properly closing the cursor. It then raises it wrapped inside a Spring Python DataAccessException. This way, database resources are properly disposed of without losing the exception stack trace.

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

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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here