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.
In this chapter, we will learn:
DatabaseTemplate
to reduce query management code DatabaseTemplate
with Object Relational Mappers (ORMs) DatabaseTemplate
with an ORM to build a robust applicationSQL 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].'),
SELECT COUNT(*) FROM ARTICLE
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.
connect
to the database. For sqlite3
, all we needed was a path. Other database engines usually require a username and a password. cursor
in which to hold our result set. execute
the query. To protect ourselves from any exceptions, we need to wrap this with some exception handlers. close
the cursor.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.
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.
Spring Python has a convenient utility class called DatabaseTemplate
that greatly simplifies this problem.
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 signifi cantly.
DatabaseTemplate
.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.