Chapter 4. Easily Writing SQL Queries with Spring Python

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:

  • The classic SQL query issue that affects APIs in many modern programming languages
  • Using Spring Python's DatabaseTemplate to reduce query management code
  • Comparing DatabaseTemplate with Object Relational Mappers (ORMs)
  • Combining DatabaseTemplate with an ORM to build a robust application
  • Testing queries with mocks

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.

  1. First, let's create a database schema for our wiki engine so that we can store and retrieve wiki context.
    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].'),
  2. 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
  3. 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.

  4. First, we connect to the database. For sqlite3, all we needed was a path. Other database engines usually require a username and a password.
  5. Next, we create a cursor in which to hold our result set.
  6. Then we execute the query. To protect ourselves from any exceptions, we need to wrap this with some exception handlers.
  7. After completing the query, we fetch the results.
  8. After pulling the results from the result set into a variable, we close the cursor.
  9. 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 signifi cantly.

  2. Let's replace the earlier coded query with a call using our instance of 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.

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

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