Database and object relational mappers

As we discussed in the previous chapters, Python provides us with a lot of object-oriented capabilities, and allows us to map our use cases in terms of classes and objects. Now, when we can map our problem set into a class and its objects, why shouldn't we also map our database tables as objects, where a particular class represents a table, and its objects represent the rows in the table. Going down this route helps us to maintain not only the consistency of how we write our code, but also how we model our problem.

The frameworks that provide the functionality through which we can map our databases to objects are known as ORMs and they help us to visualize our database as a set of classes and objects.

In the Python landscape, it is quite common to see ORMs. For example, a popular Python web framework, Django, provides its own ORM solution. Then, there is SQLAlchemy, which provides a fully-fledged ORM solution and database toolkit supporting a wide variety of relational databases.

But to convince developers to use ORM frameworks, there should be better advantages than merely saying that they are able to map your database into classes and objects, and provide you with an object-oriented interface to access the database. Let's take a look at a few of the advantages the use of ORMs brings to the table:

• Abstraction from the vendor-specific SQL: The relational database space is full of choices, with several companies marketing their products. Each of these products can have differences in how to achieve a certain functionality through the use of SQL. Sometimes, some of the databases may implement some SQL keywords that are not yet supported in other databases. For developers, this can become a problem if they need to support multiple databases with a disjointed set of functionality. Since ORMs already know how to deal with these differences in databases, they help the developer alleviate the problem of supporting multiple databases. Most of the time, when using an ORM, all the developer has to do is modify a database connection Uniform Resource Identifier (URI) and they are ready to work with a new database in their application.
• Reduces the need for repetitive SQL: When writing an application, there are quite a lot of places where the data needs to be retrieved from the same tables using similar queries. This will cause a lot of repetitive SQL being written in a lot of places, not only giving rise to quite a lot of poorly formatted code, but also opening doors for errors to creep in due to an improperly constructed SQL query (humans are quite vulnerable to losing their focus when doing repetitive work, so won't this apply to developers also?). ORM solutions help by reducing the need for writing SQL to achieve the same results, by providing abstractions over SQL commands and generating SQL on the fly, based on how we call the different methods.
• Increased application maintainability: Since ORMs allow you to define a database model once and reuse it throughout the application by instantiating the classes, it allows you to make changes in one place, which are then reflected across the whole application. This makes the task of maintaining the application a bit less tiresome, (at least the parts related to the handling of the database).
• Increased productivity: This in itself is not a feature but a side effect of the points mentioned previously. With the use of ORM solutions, developers are now a bit more relaxed about always thinking about SQL queries, or trying to follow a particular design pattern. They can now just focus on how to best architect their applications. This significantly improves developers' productivity, and allows them to get more done and improve the utilization of their time.

In this chapter, we will focus on how we can utilize ORMs to best develop our enterprise applications so that they can easily interact with databases and handle large-scale database operations efficiently. For the sake of keeping the chapter simple, we will stick with the use of SQLAlchemy, which markets itself as an SQL toolkit, and an ORM solution for Python, and provides a lot of bindings for different frameworks in the Python landscape. It is being used by some quite large-scale projects, such as OpenStack, the Fedora Project, and Reddit.