DatabaseTemplatefocuses 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.
Note
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.
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 any statement, return number of rows affected |
|
|
Query, return list converted by |
|
|
Query, return list of Python tuples |
|
|
Run query for a single column of a single row, and return an integer, throws an exception otherwise |
|
|
Query for a single column of a single row, and return a long, throws an exception otherwise |
|
|
Query for a single column of a single row, and return the object with an optional type check |
|
|
Update the database, return number of rows affected |
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. This cross cutting feature will be explored in detail in the next chapter.
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.