SQLite3 is the latest version of SQLite and is one of the most common databases found in application development. This database, unlike others, is stored as a single file and does not require a server instance to be running or installed. For this reason, it is widely used due to its portability and is found in many applications for mobile devices, desktop applications, and web services. SQLite3 uses a slightly modified SQL syntax, though of the many SQL variations that exist, it is one of its simpler implementations. Naturally, there are some limitations to this lightweight database. These limitations include a restriction of one writer being connected to the database at a time, 140 TB of storage, and that it is not client-server based. Because our application will not execute multiple write statements simultaneously, uses less than 140 TB of storage, and does not require a client-server setup for distribution, we will be using SQLite for our example in this chapter.
Before developing our code, let's take a look at the basic SQL statements we will be using. This will help us understand how we can interact with databases even without Python. In SQL, commands are commonly written in uppercase, although they are case-insensitive. For this exercise, we will use uppercase to improve legibility. All SQL statements must end in a semicolon to execute, as it denotes the end of a statement.
If you would like to follow along, install a SQLite management tool, such as the command-line tool sqlite3. This tool can be downloaded from https://www.sqlite.org/download.html. The output shown in this section has been generated with the sqlite3 command-line tool, though the statements that have been given will generate the same database in most other sqlite3 graphical applications. When in doubt, use the official sqlite3 command-line tool.
To begin, we will create a table, a fundamental component of any database. If we compare a database to an Excel workbook, a table is tantamount to a worksheet. Tables contain named columns, as well as rows of data that are mapped to these columns. Just like how an Excel workbook may contain multiple worksheets, so too can a database contain multiple tables. To create a table, we will use the CREATE TABLE command, specifying the table name and then wrapping, in parentheses, the column names and their data types as a comma-separated list. Finally, we end the SQL statement with a semicolon:
>>> CREATE TABLE custodians (id INTEGER PRIMARY KEY, name TEXT);
As we can see in the CREATE TABLE statement, we specify the id and name columns in the custodians table. The id field is an integer and primary key. This designation of INTEGER PRIMARY KEY in SQLite3 will create an automatic index that sequentially increments for each added row, therefore creating an index of unique row identifiers. The name column has the data type of TEXT, which allows any character to be stored as a text string. SQLite supports five data types, two of which we've already introduced:
- INTEGER
- TEXT
- REAL
- BLOB
- NULL
The REAL data type allows floating point numbers (for example, decimals). The BLOB (short for Binary Large OBject) data type preserves any input data exactly as is, without casting it as a certain type. The NULL data type simply stores an empty value.
After creating the table, we can begin to add data to it. As we can see in the following code block, we can use the INSERT INTO command to insert data into the table. The syntax following this command specifies the table name, the columns to insert the data into, followed by the VALUES command specifying the values to be inserted. The columns and data must be wrapped in parentheses, as shown in the following code. Using the null statement as a value, the auto-incrementing feature of SQLite will step in and fill in this value with the next available unique integer. Remember that this auto-incrementing is only true because we designated it as INTEGER PRIMARY KEY. As a general rule, only one column in a table should have this designation:
>>> INSERT INTO custodians (id, name) VALUES (null, 'Chell');
>>> INSERT INTO custodians (id, name) VALUES (null, 'GLaDOS');
We've inserted two custodians, Chell and GLaDOS, and we let SQLite assign IDs to each of them. After the data has been inserted, we can select and view this information using the SELECT command. The basic syntax involves invoking the SELECT command, followed by the columns to select (or an asterisk * to designate all columns) and the FROM statement, indicating the table name following a trailing semicolon. As we can see in the following code, SELECT will print out a pipe (|) separated list of the values stored:
>>> SELECT * FROM custodians;
1|Chell
2|GLaDOS
In addition to showing only the desired columns from our table, we can also filter data on one or more conditions. The WHERE statement allows us to filter results and return only responsive items. For the purpose of the script in this chapter, we will stick to a simple where statement and only use the equals operator to return responsive values. When executed, the SELECT-WHERE statement returns only the custodian information where the id value is 1. In addition, note that the order of the columns reflects the order in which they were specified:
>>> SELECT name,id FROM custodians WHERE id = 1;
Chell|1
There are more operations and statements available to interact with SQLite3 databases, although the preceding operations highlight all that we require for our scripts. We invite you to explore additional operations in the SQLite3 documentation, which can be found at https://sqlite.org.
![]()
The first iteration of our script focuses on performing the task at hand with a standard module, sqlite3, in a more manual fashion. This entails writing out each SQL statement and executing them as if you were working with the database itself. Although this is not a very Pythonic manner of handling a database, it demonstrates the methods that are used to interact with a database with Python. Our second iteration employs two third-party libraries: peewee and jinja2.
Peewee is an object-relational mapper (ORM), which is a term that's used to describe a software suite that uses objects to handle database operations. In short, this ORM allows the developer to call functions and define classes in Python that are interpreted as database commands. This layer of abstraction helps to standardize database calls and allows for multiple database backends to be easily interchanged. Peewee is a light ORM, as it is a single Python file that supports PostgreSQL, MySQL, and SQLite3 database connections. If we needed to switch our second script from SQLite3 to PostgreSQL, it would only require that we modify a few lines of code; our first script would require more attention to handle this same conversion. This being said, our first version does not require any dependencies beyond the sta...
