There are many applications where we need to persist a large number of distinct objects. The techniques we looked at in Chapter 10, Serializing and Saving - JSON, YAML, Pickle, CSV, and XML, were biased toward persistence for a single, monolithic object. Sometimes, we need to persist separate, individual objects from a larger domain. For example, we might be saving blog entries, blog posts, authors, and advertisements; each of which must be handled separately.
In Chapter 11, Storing and Retrieving Objects via Shelve, we looked at storing distinct Python objects in a shelve data store. This allowed us to implement the CRUD processing on a domain of individual objects. Each object can be created, retrieved, updated, or deleted without having to load and dump the entire file.
In this chapter, we'll look at mapping Python objects to a relational database; specifically, the sqlite3 database that is bundled with Python. This will be another example of the three-tier architecture design pattern.
In this case, the SQLite data tier is a more sophisticated database than shelve. SQLite can allow concurrent database updates via locking. SQLite offers an access layer based on the SQL language. It offers persistence by saving SQL tables to the filesystem. Web applications are one example where a database is used instead of simple file persistence to handle concurrent updates to a single pool of data. RESTful data servers, too, frequently use a relational database to provide access to persistent objects.
For scalability, a standalone database server process can be used to isolate all the database transactions. This means persistence can be allocated to a relatively secure host computer, separate from the web application servers and behind appropriate firewalls. MySQL, for example, can be implemented as a standalone server process.
The SQLite3 database that is part of Python is not a standalone database server; it must be embedded into a host application.
In this chapter, we will cover the following topics:
- SQL databases, persistence, and objects
- Processing application data with SQL
- Mapping Python objects to SQLite BLOB columns
- Mapping Python objects to database rows manually
- Improving performance with indices
- Adding an ORM layer
- Querying pasts given a tag
- Improving performance with indices