DatabaseTemplate

Writing SQL-based programs has a familiar pattern that must be repeated over

and over. The DatabaseTemplate resolves that by handling the plumbing of these

operations while leaving you in control of the part that matters the most,

the SQL.

External dependencies

DatabaseTemplate requires the use of external libraries for connecting to

SQL databases. Depending on which SQL connection factory you're about to use,

you need to install following dependencies:

* *springpython.database.factory.MySQLConnectionFactory* -

needs `MySQLdb <http://sourceforge.net/projects/mysql-python/>`_ for connecting to MySQL,

* *springpython.database.factory.PgdbConnectionFactory* -

needs `PyGreSQL <http://www.pygresql.org/>`_ for connecting to PostgreSQL,

* *springpython.database.factory.Sqlite3ConnectionFactory* -

needs `PySQLite <http://pypi.python.org/pypi/pysqlite/>`_ for connecting to SQLite 3, note that PySQLite is part

of Python 2.5 and later so you need to install it separately only if you're

using Python 2.4,

* *springpython.database.factory.cxoraConnectionFactory* -

needs `cx_Oracle <http://pypi.python.org/pypi/cx_Oracle>`_ for connecting to Oracle,

* *springpython.database.factory.SQLServerConnectionFactory* -

needs `PyODBC <http://pypi.python.org/pypi/pyodbc>`_ for connecting to SQL Server.

Traditional Database Query

If you have written a database SELECT statement following Python's

`DB-API 2.0 <http://www.python.org/dev/peps/pep-0249/>`_, it would something

like this (MySQL example)::

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.

Database Template

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::

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::

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.

Mapping rows into objects using convention over configuration

A powerful feature provided by databases is the ability to look up column names.

Spring Python harnesses this by providing an out-of-the-box row mapper that

will automatically try matching a query column name to an class attribute name.

This is known as *convention over configuration* because it relieves you of the

need to code the *RowMapper* provided you follow the convention of naming the

attributes of your :abbr:`POPO (Plain Old Python Object)` after query columns.

The only requirement is that class have a default constructor that doesn't

require any arguments::

.. note::

Convention is based on query, not tables

Query metadata is based on the column names as defined in the query, NOT what

is in the table. This is important when you use expressions like COUNT(*).

These columns should be aliased to fit the attribute name.

Mapping rows into dictionaries

A convenient alternative to mapping database rows into python objects, it

to map them into dictionaries. Spring Python offers *springpython.database.DictionaryRowMapper*

as an out-of-the-box way to query the database, and return a list of dictionary

entries, based on the column names of the queries. Using this mapper, you don't

have to code a *TvRowMapper* as shown earlier::

.. note::

Dictionary keys are based on query not original tables

Query metadata is based on the column names as defined in the query, NOT what

is in the table. This is important when you use expressions like COUNT(*).

These columns should be aliased in order to generate a useful key in the

dictionary.

What is a Connection Factory?

You may have noticed I didn't make a standard connection in the example above.

That is because to support `Dependency Injection <http://en.wikipedia.org/wiki/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.

Creating/altering tables, databases, and other DDL

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.

SQL Injection Attacks

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 <http://en.wikipedia.org/wiki/SQL_injection>`_.

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.

Have you used Spring Framework's JdbcTemplate?

If you are a user of Java's `Spring framework <http://www.springsource.org/>`_

and have used the `JdbcTemplate <http://static.springsource.org/spring/docs/1.2.x/api/org/springframework/jdbc/core/JdbcTemplate.html>`_,

then you will find this template has a familiar feel.

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 rowhandler

query_for_list(sql_query, args = None) query, return list of DB-API tuplesTrue

query_for_int(sql_query, args = None) query for a single column of a single row, and return an integer (throws exception otherwise)

query_for_long(sql_query, args = None) query for a single column of a single row, and return a long (throws 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 possibly no checking

update(sql_statement, args = None) update the database, return number of rows updated

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

Notes on using SQLServerConnectionFactory

*SQLServerConnectionFactory* uses ODBC for connecting to SQL Server instances

and it expects you to pass the ODBC parameters when creating connection

factories or when injecting factory settings through IoC. The ODBC parameters

you provide are directly translated into an ODBC connection string.

That means that you use the exact ODBC parameters your ODBC provider understands

and not the standard username, password, hostname and db parameters as with

other connection factories.

A simple example will demonstrate this. Here's how you would create

a *DatabaseTemplate* on Windows for running queries against an SQL Server

instance::

.. note::

SQLServerConnectionFactory is dictionary driven

Due to SQLServerConnectionFactory's pass-through nature, it is coded to

accept a dictionary. For pure python, this means you MUST name the arguments

and NOT rely on argument position.

.. highlight:: xml

For an XML-based application context, you must populate the argument

odbc_info with a dictionary. See the following example::