Easily Writing SQL Queries with Spring Python

(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.


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

    INSERT INTO article
    (id, title, wiki_text
    '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
    '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.


  • 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
    cursor.execute("SELECT COUNT(*) FROM ARTICLE")
    results = cursor.fetchall()
    except Exception, e:
    print "execute: Trapped %s" % e
    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
results = cursor.fetchall()
except Exception, e:
print "execute: Trapped %s" % e
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
results = cursor.fetchall()
except Exception, e:
print "execute: Trapped %s" % e
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 = []
    cursor.execute("SELECT id, title, wiki_text FROM ARTICLE")
    temp = cursor.fetchall()
    for row in temp:
    except Exception, e:
    print "execute: Trapped %s" % e
    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",

  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.


(For more resources on Spring, see here.)

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 requires that the class has a default constructor, and also that the class's properties match the query's.

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",

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.

DatabaseTemplate and ORMs

  • DatabaseTemplate focuses on accessing the database without writing lots of boiler plate code
  • ORMs focus on mapping tables to objects

DatabaseTemplate does not contest with ORM. The choice we must make is between using SQL and processing result sets or using an ORM.

Before going into detail about ORMs and DatabaseTemplate, it may be useful to look at a quick example of a popular Python ORM: SQLAlchemy (http://www.sqlalchemy.org). We could have picked any number of ORMs for this demonstration.

from sqlalchemy import *

engine = create_engine("sqlite:/tmp/springpython.db", echo=True)
metadata = BoundMetaData(engine)
article_table = Table('Article', metadata,
Column('id', Integer, primary_key=True),
Column('title', String()),
Column('wiki_text', String()))
article_mapper = mapper(Article, article_table)

session = create_session(bind_to=engine)
articles = session.query(Article)

This demonstrates how we would use SQLAlchemy to define the mapping between the ARTICLE table and the Article class. ORMs also offer many other query options, including filters and, critieria. The key purpose of ORMs is to map databases to objects. There is boiler plate with using ORMs just as there is with raw SQL.

Solutions provided by DatabaseTemplate

If we choose DatabaseTemplate for our data needs, we would write our updates, inserts, deletes, and queries using pure SQL. If our team was comprised of database designers and software developers who are all familiar with SQL, this would be of huge benefit—being a more natural fit to their skills. The whole team could contribute to the effort of designing tables, queries, and data management by speaking the common language of SQL.

In this scenario DatabaseTemplate would definitely make things easier, as shown earlier. This would allow our team to spend its effort on designing and managing our application's data.

The set of operations provided by DatabaseTemplate is provided in the following table.

Operation Description
execute(sql_statement, args=None) Execute any statement, return number of rows affected
query(sql_query, args=None, rowhandler=None) Query, return list converted by
query_for_list(sql_query, args=None Query, return list of Python tuples
query_for_int(sql_query, args=None) Run query for a single column of a single row, and return an integer, throws an exception otherwise
query_for_long(sql_query, args=None) Query for a single column of a single row, and return a long, throws an exception otherwise
query_for_object(sql_query, args=None, required_type=None) Query for a single column of a single row, and return the object with an optional type check
update(sql_statement, args=None) Update the database, return number of rows affecte

This may not appear like a lot of operations, but the purpose of DatabaseTemplate is to provide easy access to writing SQL. This API provides the power to code inserts, updates, deletes, and queries, while also being able to call stored procedures.

DatabaseTemplate also works nicely with Spring Python transactions.

How DatabaseTemplate and ORMs can work together

Often, while building our application we tend to start with one paradigm, and discover it has its limits. Building an enterprise grade application that supports many users with lots of complex functions from either a pure SQL perspective or from an ORM perspective may exceed the capacity of both. This is when it may be time to use both DatabaseTemplate and an ORM in the same application.

It would be a practical solution to use an ORM to code and manage the simple entities and straightforward relationships. We could quickly build persistence into our application and move onto real business solutions.

But the queries needed to generate complex reports, detailed structures, and stored procedures may be better managed using DatabaseTemplate.

If we can free up our team from coding custom SQL for the simple objects, they could focus on writing specialized SQL for the hard queries.

Using the right tool for the right job should be a key element of our software development process, and having both DatabaseTemplate and an ORM in our toolbox is the pragmatic thing to do.

Testing our data access layer with mocks

Now we are in the heart of writing the data access layer, we need to look at ways to test our queries. To be specific, we need to make sure we are using DatabaseTemplate correctly, along with any custom row mapper we write.

Mocks are used to primarily record what functions are called, and provide options of returning certain values. The idea is to create a set of expected actions, and then call the actual API and measure if this is what happened. This is compared to stubs, which you code yourself and provide canned answers and don't necessarily care what methods were called. Both of these tools are useful for automated testing.

Spring Python uses pmock, a Python library inspired by the fluent API of jMock (http://www.jmock.org/), to do some of its automated testing. You don't have to use this particular mocking library. There are lots of other candidates around. For our purposes we are going to use it here to show the general idea of mocking your data access layer for testing. Due to lack of updates from the original developers of pmock, the source code of this library was added to Spring Python's set of managed code, and has some custom updates. See http://springpython.webfactional.com for details on downloading.

  1. First, let's code a simple DataAccess class that uses DatabaseTemplate to fetch the number of articles we have.

    class DataAccess(object):
    def __init__(self, conn_factory):
    self.dt = DatabaseTemplate(conn_factory)

    def count_wiki_articles(self):
    return self.dt.query_for_object("SELECT COUNT(*) FROM

    This simple data access layer has one method: count_wiki_articles. It utilizes the code we wrote earlier involving the DatabaseTemplate. In this example, DataAccess expects to be initialized with a connection factory.

    Now, to test this out, we need DatabaseTemplate to do its job, but we want to catch it at the right point in order to inject some pre-built values. The piece of code that does the heavy lifting is Spring Python's cursor object, which is supplied by a connection. This means we need to code a special stubbed out connection factory that will hold a mocked cursor.

  2. Let's write a specialized class to act as a mocked connection.

    class MockedConnection(object):
    def __init__(self):
    self.mockCursor = None
    def cursor(self):
    return self.mockCursor

    This connection will supply DatabaseTemplate with a special type of mocked cursor. Further down, we will see how mockCursor gets populated.

  3. In order to use this, let's code a connection factory for DatabaseTemplate that produces this type of connection.

    class MockingDBFactory(ConnectionFactory):
    def __init__(self):
    ConnectionFactory.__init__(self, [types.TupleType])
    self.mockedConnection = MockedConnection()
    def connect(self):
    return self.mockedConnection

    When this connection factory is asked to connect to the database, it will return a MockedConnection.

  4. To tie this rigging together, we need to setup a MockTestCase. This is a special type of unit test that provides extra hooks to library calls of pmock.

    from pmock import *

    class DataAccessMockTestCase(MockTestCase):
    def setUp(self):
    # Create a mock instance to record events
    self.mock = self.mock()
    conn_factory = MockingDBFactory()
    conn_factory.mockedConnection.mockCursor = self.mock

    Here in the setUp method for our test, we grab an instance of mock(). This object has APIs to record expectations. The object is meant to be injected so that function calls are then made against it, and at the end of a MockTestCase test method, the results are compared with the expectations.

    In this situation, the mockCursor is the key holder of the mock. There is also a local copy, so that the MockTestCase has a handle to check out the results.

  5. After setting all this up, let's define a mocked test method.

    def testCountingArticles(self):
    count = self.data_access.count_wiki_articles()
    self.assertEquals(count, 2)

The first two steps of this test use the mock object to define expectations. The mock is expected to receive an execute method call once, and also a fetchall method call. The fetchall will return a value of [(2,)].

Not only do we get to check the assertions, but pmock will verify that each of these methods was invoked by DatabaseTemplate.

How much testing is enough?

How much testing is enough?For this test scenario, we dug down deep. This type of testing can't get much closer to the hardware of the database server without directly talking to a live database.

It could be viewed that we were really testing the core of DatabaseTemplate. In many development situations, this isn't needed. Testing a 3rd party library would probably be out of scope.

This example test scenario is largely based on automated tests used to confirm Spring Python's functionality. Adequate testing for your business needs may involve stubbing or mocking out the data access layer.

This should introduce you to the concept of mocking, where you measure method calls and answers. This type of testing may perfectly fit other test needs. If the SQL queries have been well isolated in this layer, then it may be safe to say that the only testing needed would be the queries themselves against a live database and in turn, not require any mocking or stubbing at all.


This article has shown a way to write pure SQL without having to deal with the low level cursor and connection management. This removes a lot of boiler plate and error handling that is perfect for a framework to handle.

We have also explored how DatabaseTemplate and ORMs can work together to make persistence management easier for developers.

We took a look at mocking, and how we were able to get inside the querying process to verify that the right method calls were being made. Then we stepped back and considered how much testing is enough.

In this article we learned:

  • The classic SQL query issue that affects APIs in many modern programming languages extends into Python as well
  • Using Spring Python's DatabaseTemplate let's us get rid of query boiler plate code that gets in the way of solving use cases
  • DatabaseTemplate is useful for writing SQL code without dealing with cursor management
  • DatabaseTemplate combined with an ORM can help us build robust applications
  • Mocking is a valuable tool in automated testing, but we must choose the right tool for the right situation

Further resources on this subject:

You've been reading an excerpt of:

Spring Python 1.1

Explore Title