One of the key requirements of any web-application is that is has access to some sort of persistent storage. This might be used to store core data like a catalog of car parts, but a password file also needs a form of persistent storage.
Often it is possible to store the information in files on the filesystem and indeed some of the applications we develop in this book do just that, but if you have a lot of structured data or you find that many people want to access this data at the same time, it is usually a better choice to store this data in a database and access this data through a database engine.
When choosing a database engine, you should consider the following points:
- Does it offer the functionality you need? Database engines are sophisticated pieces of software and in general offer a lot of functionality, often more than you need. Although this may sound like an advantage, all these features must be learned by a developer to take advantage of them and may complicate your code which may increase the effort to maintain an application.
- Is it easy to install and maintain? Database engines often run as separate applications that are accessed over a network. This means that they have to be installed, tested, and maintained separately. This may add significantly to the effort needed to deploy your application. And installation isn't even everything; you will have to consider operational issues as well, for example, how much effort it is to set up a suitable backup scheme or how to monitor the availability of the database.
- Does it offer an API that is simple to use from your chosen programming language and does this API provide access to all necessary functionality?
- And finally, does it perform well enough to respond swiftly to the requests of your application, even during peaks?
Python offers a standardized API to access many available database engines, including MySQL and PostgreSQL. Fully in line with its 'batteries included' philosophy, Python also comes included with a database engine and a module to access it. This database is called SQLite and is a so called embedded database: it doesn't run as a standalone process that can be accessed through some means of inter-process communication, but the database engine is an integral part of the program that uses it. Its only external part is a single file containing the data in the database itself and that may be shared by other programs that include the SQLite engine. As it fits our requirements, SQLite will be the database engine we will use for the applications we develop in this book.
Our choice for SQLite as the database for many of our applications is easily justified:
- Although not as feature-rich as, for example, MySQL, it does provide the functionality we need.
- Installation is practically a no brainer as SQLite comes included with Python.
- The API offered by the
sqlite3module gives access to all functionality. - It performs well enough for our needs (although statements about performance are very difficult to make in advance).
The main arguments supporting the use of SQLite in our applications are not its speed, small memory footprint, or reliability (although these are certainly not drawbacks as SQLite's reputation as database engine of choice for mobile telephone appliances proves) but the fact that because it is embedded in your program, it obviates the need for a separately configured and maintained database engine. This cuts down on maintenance in a serious manner as database engines are demanding beasts that take a lot of care and feeding. Also, because it is included in Python, it reduces the number of external dependencies when deploying an application.
A final argument is its type system that closely resembles Python's type system; in contrast to many other database engines, SQLite allows you to store any value in a column no matter how this column was typed when it was created, just like you can store a string in a Python variable that was first used to store an integer value. This close correspondence of types allows for an intuitive mapping of Python values to values stored in the database, an advantage that we will study closely when we encounter our first application that uses SQLite.
Tip
The integration with Python is so close that it is possible to use Python functions within the SQL expressions used to query SQLite. The native set of functions in SQLite is quite small compared to other database engines but the ability to use Python functions removes this limitation completely. It is, for example, straightforward to add a hash function from Python's hashlib module, that is very convenient when implementing a password database.