16SQLITE DATABASES

Spreadsheets vs. Databases

In a spreadsheet, rows contain individual records, while columns represent the kind of data stored in the fields of each record. We can store this same information in a database. You can think of a database table as a spreadsheet, and a database can contain one or more tables.

1. 1. How do you pronounce “SQLite”?

2. 2. What is the database analogue of a spreadsheet sheet?

3. 3. How many tables can be in a database?

4. 4. What is a primary key?

5. 5. By convention, what is the name of the primary key column in SQLite?

6. 6. Does a database record ever change its primary key value?

7. 7. Some spreadsheets have rows of identically structured data, where each column corresponds to a field of data (such as the name, price, or size). But other spreadsheets look like forms, have a fixed size and layout, and enable a human user to fill in the blanks with data. Is the second kind of spreadsheet easy to convert to a database table?

SQLite vs. Other SQL Databases

Like other database software, SQLite uses Structured Query Language (SQL) to read and write massive amounts of data, but it runs within your program and operates in a single file.

1. 8. Does SQLite run as a server application you must install on your computer?

2. 9. Does SQLite strictly enforce the data types for its columns?

3. 10. What permissions and user roles does SQLite have?

4. 11. Do you need to pay for permission to use SQLite?

Creating Databases and Tables

SQL is a mini-language you can use from within Python, much like regex for regular expressions. Also like regex, we write SQL queries as Python string values. Calling sqlite3.connect() creates a SQLite database file, and the SQL query CREATE TABLE SQL creates a table within it. Answer the following questions about databases and tables.

1. 12. What module do you import to use SQLite in Python?

2. 13. After importing the module, what Python instruction connects to a database named, say, example.db?

3. 14. What does the isolation_level=None argument do?

4. 15. What does a CREATE TABLE IF NOT EXISTS query do?

5. 16. What are the SQLite data types analogous to Python’s NoneType, int, float, str, and bytes data types?

6. 17. If you try to insert the value '42' into an INTEGER column, what does SQLite’s type affinity feature do?

7. 18. If you try to insert the value 'Hello' into an INTEGER column, what does SQLite’s type affinity feature do? (Assume strict mode isn’t enabled.)

8. 19. How do you enable strict mode?

9. 20. Does SQLite have a data type for times and dates?

10. 21. Does SQLite have a data type for Boolean values?

11. 22. What query returns a description of a table’s columns if the table is named, say, 'cats'?

12. 23. How can you list all of the tables in a database?

CRUD Database Operations

CRUD stands for the four basic operations that databases carry out: creating data, reading data, updating data, and deleting data. In SQLite, we perform these operations with INSERT, SELECT, UPDATE, and DELETE statements, respectively.

For each operation in questions 24 through 27, state which kind of SQLite query performs it.

1. 24. The “create data” operation

2. 25. The “read data” operation

3. 26. The “update data” operation

4. 27. The “delete data” operation

5. 28. What is wrong with this query: 'INSERT INTO cats ("Zophie", "2021-01-24", "black", 5.6)'?

6. 29. Is it possible for an INSERT query to insert only half of its data into the database?

7. 30. If two Python programs carry out INSERT queries on the same SQLite database, can one transaction affect the other transaction?

8. 31. What does using ? placeholders instead of f-strings prevent?

9. 32. In the query 'SELECT * FROM cats', what does * mean?

10. 33. What is wrong with this query: 'SELECT FROM cats'?

11. 34. What does a WHERE clause do in a SQLite SELECT query?

12. 35. What are 10 operators you can use in a WHERE clause? (Hint: They’re similar to Python’s comparison and Boolean operators.)

13. 36. What does the LIKE operator do?

14. 37. In the query 'SELECT rowid, name FROM cats', how could you sort the returned results by rowid?

15. 38. What does the LIMIT clause do?

16. 39. Does a column index speed up or slow down the process of reading data?

17. 40. Does a column index speed up or slow down the process of inserting or updating data?

18. 41. What Python code returns a list of all of the indexes for a table named cats?

19. 42. The query 'UPDATE cats SET fur = "black"' lacks a WHERE clause. What possible bug will this query have?

20. 43. The query 'DELETE FROM cats' lacks a WHERE clause. What possible bug will this query have?

21. 44. If you ran the following code, what would the second instruction return?

    conn.execute('DELETE FROM staff WHERE rowid = 42')
    conn.execute('SELECT * FROM staff WHERE rowid = 42').fetchall()

22. 45. Run a SELECT query, but use a table name that doesn’t exist, like conn.execute('SELECT * FROM does_not_exist').fetchall(). Does this code raise an exception and, if so, what is the error message?

23. 46. Run a SELECT query but use a rowid that doesn’t exist, like conn.execute('SELECT * FROM cats WHERE rowid=9999').fetchall(). Does this code raise an exception and, if so, what is the error message?

Rolling Back Transactions

You may sometimes want to run several queries all together, or else not run those queries at all, but you won’t know which you want to do until you’ve run at least some of the queries. One way to handle this situation is to begin a new transaction, execute the queries, and then either commit all of the queries to the database to complete the transaction or roll them back so that the database looks as if none of them were made.

1. 47. What does conn.execute('BEGIN') do?

2. 48. If you execute multiple INSERT queries after starting a transaction, when will the data actually be inserted into the database?

3. 49. If you execute multiple INSERT queries after starting a transaction, what Python code will finish the transaction?

4. 50. After you’ve started a transaction, what Python code will cancel the transaction?

5. 51. Can you roll back a transaction after it has been committed?

Backing Up Databases

If a program isn’t currently accessing the SQLite database, you can back it up by simply copying the database file. If your software is continuously reading or updating the database’s contents, however, you’ll need to use the Connection object’s backup() method instead.

1. 52. If no program is currently connected to a SQLite database, what is the easiest way to make a backup?

2. 53. If your program is constantly connected to a SQLite database, what method can make a backup of it in another database file?

3. 54. Assume a variable conn contains a connection to a database. What code will print the text of the SQLite queries that can re-create this database and its data?

Altering and Dropping Tables

After creating a table in a database and inserting rows into it, you may want to rename the table or its columns. You may also wish to add or delete columns in the table, or even delete the table itself. You can use an ALTER TABLE query to perform these actions.

1. 55. What query would rename a table from spam to eggs?

2. 56. What query would rename a column named foo to bar in a table named spam?

3. 57. What query would add a new column named price to a table named spam and set price to the value 42 for all existing records?

4. 58. What query would delete the entire table named spam?

Joining Multiple Tables with Foreign Keys

The structure of SQLite tables is rather strict; for example, each row has a set number of columns. But real-world data is often more complicated than a single table can capture. In relational databases, we can store complex data across multiple tables, then create links between them called foreign keys.

1. 59. If a foreign key column has the name cat_id, what can you assume its values represent?

2. 60. A database has two tables, customers and orders, to store details about customers, like their names and emails, and details about orders, like the product and date of purchase. Each customer may have purchased multiple orders, while an order may have been purchased by only one customer. Which table should have a foreign key column, and which values should this column contain?

3. 61. Say you have two tables, cats and vaccinations, and want to create a foreign key in the vaccinations table to link it to a cat. What goes into the ANSWER_GOES_HERE part of this code to create the foreign key?

   conn.execute('CREATE TABLE IF NOT EXISTS vaccinations
   (vaccine TEXT, date_administered TEXT, administered_by TEXT,
   cat_id INTEGER, ANSWER_GOES_HERE) STRICT')

In-Memory Databases and Backups

If your program makes a large number of queries, you can significantly improve the speed of your database by using an in-memory database. These databases live entirely in the computer’s memory, rather than in a file on the computer’s hard drive, making changes incredibly fast.

1. 62. What is the benefit of an in-memory SQLite database?

2. 63. What is the disadvantage of an in-memory SQLite database?

3. 64. Assume you have a Connection object to an in-memory database in a variable named memory_db_conn, as well as a Connection object to a file-based database in a variable named file_db_conn. What code would save the in-memory database data to the file-based database?

4. 65. If your computer becomes unplugged and the battery dies while your program is updating an in-memory database, how can you recover the database data?

5. 66. If your Python program crashes from an unhandled ZeroDivisionError exception in the following code, will the in-memory database’s data have been saved or lost?

   import sqlite3
   file_db_conn = sqlite3.connect('cats.db', isolation_level=None)
   memory_db_conn = sqlite3.connect(':memory:', isolation_level=None)
   
   try:
       memory_db_conn.execute('CREATE TABLE cats (name TEXT, fur
   TEXT)')
       memory_db_conn.execute('INSERT INTO cats VALUES ("Zophie",
   "gray")')
       spam = 42 / 0  # Causes a crash
   except:
       memory_db_conn.backup(file_db_conn)

Monitoring Free Disk Space Levels

Let’s use a new module,  psutil, to monitor disk free space with its psutil.disk_usage() function and store those records in a SQLite database. You can install it by running pip install psutil. The following code will print the number of bytes of free space if you uncomment the line corresponding to your operating system:

import psutil
#print(psutil.disk_usage('C:\\').free)  # Windows
#print(psutil.disk_usage('/').free)  # macOS and Linux

Let’s record how the amount of free space on your computer changes over time. Write a program that creates a SQLite database in a file named monitorFreeSpace.db. This database should have one table named freespace with two columns: free (of the INT data type) and timestamp (of the TEXT data type, with the 'YYYY-MM-DD HH:MM:SS.SSS' format). You can get the current time and date as a string by running import datetime and then calling str(datetime.datetime.now()).

Upon starting, the program should create the database and table (if they don’t already exist) and then repeatedly record the amount of free space, with one-second pauses in between recordings. Print the message "Monitoring disk free space. Press Ctrl-C to quit." at the start of the program, and print the free disk space and timestamp as they’re recorded, to give a visual indication that the program is running and working. The program can run inside an infinite loop, stopping when the user presses ctrl-C to raise a KeyboardInterrupt exception.

Save this program in a file named monitorFreeSpace.py.

Leave the program running on your computer for an hour or a few days. On your own, run some SELECT queries to see how the amount of free space has changed over time. The SQLite database can easily handle millions of recordings, and you can write queries that extract the recordings for a particular day or hour.

Database-to-String Converter

Write a program containing a function, db_to_txt(db_filename), that takes the filename of a SQLite database, then creates a .txt file with the same name as that SQLite database. For example, given example.db, it should produce an example.db.txt file. The text file should contain all of the information in the database, allowing you to skim the data in a text editor or share it with others who don’t know how to write SQLite queries.

For the purposes of this exercise, assume that the database always has exactly one table. Your program must find the name of this table, then find the names of every column in this table. The first line of the text file should list the names of the columns separated by commas, and the remaining lines should each list one row from the table. Remember to include the rowid column’s values.

You may use the sweigartcats.db database available for download at https://nostarch.com/automate-boring-stuff-python-3rd-edition for testing purposes. For example, calling db_to_txt('sweigartcats.db') would create a file named sweigartcats.db.txt with the following contents:

rowid,name,birthdate,fur,weight_kg
1,Zophie,2021-01-24,gray tabby,5.6
2,Miguel,2016-12-24,siamese,6.2
3,Jacob,2022-02-20,orange and white,5.5
4,Gumdrop,2020-08-23,white,6.4
--snip--

Save this program in a file named dbToTxt.py.