Relational database engines like SQLite use tables consisting of rows and columns as their primary data abstraction model. Object-oriented languages like Python define classes to instantiate objects that have attributes. There is a fair amount of correspondence between these concepts as class definitions mimic table definitions where object instances with attributes relate to records with columns but maintaining the integrity of that relation is not so straightforward.
The problem not only lies in the different languages used to define tables and classes. The main issue in relational databases is maintaining referential integrity. If you have, for example, a record representing a car part that references a record in a different table that represents a car type, then a relational database lets you define explicit actions to execute if, for example, the record representing the car type is deleted. These constraints are of course possible to implement in Python data structures as well, but it does take serious effort to implement.
Finally, most database engines require fixed data types for each column whereas Python variables and attributes may refer to any kind of data type. This restriction is not present in SQLite but even SQLite cannot store everything without conversion. A Python variable, for example, may refer to a list of objects, something that cannot be stored in a single column of a relational database.
Still, we would very much like to have a way to store object instances in a relational database or at least the data contained in those object instances, and have the means to define the relation between classes and tables in a maintainable way. To this end, many people have designed solutions in the form of object relational mappers. For Python, quite a few exist that are both mature and robust tools (like SQLAlchemy).
When deciding which tool to use, you should at least consider the following:
- How much time it will cost to learn to use it. Those tools are usually very versatile and quite often require a considerable amount of effort to learn.
- How will it affect development and maintenance? Complex tools may help to solve the challenge of creating an effective and efficient mapping between classes and tables, but may require an idiom that detracts from a clear overview of your implementation. This may well be worth it, if your data model consists of many classes and performance is an important consideration, but for smaller projects the added complexity might be too great of a disadvantage when it impacts significantly on the development time.
Because the focus in this book is on understanding the choices in implementing web applications and persistent storage, using a complex tool like an object relational mapper may hide all kinds of aspects necessary to gain understanding.
Therefore, we will not use a third party object relational mapper in the examples in this book but implement increasingly versatile storage solutions in each chapter, tackling specific requirements as we encounter them. We will see that in many situations an object relational mapper is superfluous, but in the final chapters, we will build a simple framework ourselves to give us not only a tool but an insight into the intricacies of mapping complex assemblies of classes to tables in a database as well.