If you have written a database SELECT statement following Python's DB-API 2.0, it would something like this (MySQL example):

conn = MySQL.connection(username="me", password"secret", hostname="localhost", db="springpython")
cursor = conn.cursor()
results = []
try:
    cursor.execute("select title, air_date, episode_number, writer from tv_shows where name = %s", ("Monty Python",))
    for row in cursor.fetchall():
        tvShow = TvShow(title=row[0], airDate=row[1], episodeNumber=row[2], writer=row[3])
        results.append(tvShow)
finally:
    try:
        cursor.close()
    except Exception:
        pass
conn.close()
return results

I know, you don't have to open and close a connection for every query, but let's look past that part. In every definition of a SQL query, you must create a new cursor, execute against the cursor, loop through the results, and most importantly (and easy to forget) close the cursor. Of course you will wrap this in a method instead of plugging in this code where ever you need the information. But every time you need another query, you have to repeat this dull pattern over and over again. The only thing different is the actual SQL code you must write and converting it to a list of objects.

I know there are many object relational mappers (ORMs) out there, but sometimes you need something simple and sweet. That is where DatabaseTemplate comes in.

The same query above can be written using a DatabaseTemplate. The only thing you must provide is the SQL and a RowMapper to process one row of data. The template does the rest.

""" The following part only has to be done once."""
from springpython.database import *
connectionFactory = MySQLConnectionFactory(username="me", password"secret", hostname="localhost", db="springpython")
dt = DatabaseTemplate(connectionFactory)

class TvShowMapper(RowMapper):
    """This will handle one row of database. It can be reused for many queries if they
       are returning the same columns."""
    def map_row(self, row):
        return TvShow(title=row[0], airDate=row[1], episodeNumber=row[2], writer=row[3])


results = dt.query("select title, air_date, episode_number, writer from tv_shows where name = %s", \
                   ("Monty Python",), TvShowMapper())

Well, no sign of a cursor anywhere. If you didn't have to worry about opening it, you don't have to worry about closing it. I know this is about the same amount of code as the traditional example. Where DatabaseTemplate starts to shine is when you want to write ten different TV_SHOW queries.

results = dt.query("select title, air_date, episode_number, writer from tv_shows where episode_number < %s", \
                   (100,), TvShowMapper())
results = dt.query("select title, air_date, episode_number, writer from tv_shows where upper(title) like %s", \
                   ("%CHEESE%",), TvShowMapper())
results = dt.query("select title, air_date, episode_number, writer from tv_shows where writer in ('Cleese', 'Graham')",
                   rowhandler=TvShowMapper())

You don't have to reimplement the rowhandler. For these queries, you can focus on the SQL you want to write, not the mind-numbing job of managing database cursors.

You may have noticed I didn't make a standard connection in the example above. That is because to support Dependency Injection, I need to setup my credentials in an object before making the actual connection. MySQLConnectionFactory holds credentials specific to the MySQL DB-API, but contains a common function to actually create the connection. I don't have to use it myself. DatabaseTemplate will use it when necessary to create a connection, and then proceed to reuse the connection for subsequent database calls.

That way, I don't manage database connections and cursors directly, but instead let Spring Python do the heavy lifting for me.

Data Definition Language includes the database statements that involve creating and altering tables, and so forth. DB-API defines an execute function for this. DatabaseTemplate offers the same. Using the execute() function will pass through your request to a cursor, along with the extra exception handler and cursor management.

You may have noticed in the first three example queries I wrote with the DatabaseTemplate, I embedded a "%s" in the SQL statement. These are called binding variables, and they require a tuple argument be included after the SQL statement. Do NOT include quotes around these variables. The database connection will handle that. This style of SQL programming is highly recommended to avoid SQL injection attacks.

For users who are familiar with Java database APIs, the binding variables are cited using "?" instead of "%s". To make both parties happy and help pave the way for existing Java programmers to use this framework, I have included support for both. You can mix-and-match these two binding variable types as you wish, and things will still work.

If you are a user of Java's Spring framework and have used the JdbcTemplate, then you will find this template has a familiar feel.

Inserts are implemented through the execute() function, just like in JdbcTemplate.