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.