Data manipulation through SQL - Study Notes
Chapter Summary
This chapter connects Python to a database using SQLite, a small database library that comes with Python as the sqlite3 module. A Python script can create a database and tables, insert, update and delete records, and run SELECT queries with clauses such as DISTINCT, WHERE, GROUP BY, ORDER BY and HAVING. The chapter also uses aggregate functions and shows how to write the result of a query into a CSV file.
Learning Objectives
- Explain how a Python program connects to an SQLite database.
- Create tables from Python, with column types and constraints.
- Run SELECT, INSERT, UPDATE and DELETE statements from Python.
- Use WHERE, DISTINCT, ORDER BY, GROUP BY and HAVING to filter and group results.
- Calculate values with the aggregate functions COUNT, SUM, AVG, MAX and MIN.
- Take data from the user at run time and store it in a table.
- Write the output of a query into a CSV file.
Key Concepts and Definitions
SQLite
A lightweight relational database that runs inside the application itself. It is serverless, so no separate database server has to be installed or started.
Connection Object
The link between the Python program and the SQLite database file. It is created with the connect() method.
Cursor Object
A control structure used to traverse and fetch the records of the database. SQL statements are run through the cursor with execute(), and the rows they return are read from it.
Placeholder
A symbol, such as a question mark ? or a named parameter, written in an SQL statement in place of a value. The actual value is supplied when the statement runs, which is safer than joining strings together.
Commit
Saves all changes made in the current transaction to the database file for good.
Rollback
Cancels the changes made since the last commit and returns the database to that state.
Worked Methods
Connecting to a Database and Creating a Table
Every program in this chapter follows the same pattern: connect, get a cursor, run the SQL, commit, close.
First, import the module with import sqlite3. Then call connect() with a file name; if that database file does not exist, it is created. Next, get a cursor with the cursor() method. Put the CREATE TABLE statement in a string and pass it to execute(). Finally, call commit and close the connection.
Inserting and Retrieving Records
To add records, run an INSERT INTO statement through execute(). To read records, run a SELECT query and then call fetchall() to get every row, or fetchone() to get one row at a time, as Python objects.
Common Exam Traps
- Forgetting to Commit: changes made by INSERT, UPDATE or DELETE are not saved to the database file until commit() is called. If you close the connection without committing, those changes are lost.
- Printing Fetched Rows: each row returned by the cursor is a tuple, and fetchall() gives a list of tuples. To print one row per line, loop through the list, or unpack it with the asterisk, as in print(*result, sep="\n").
- WHERE vs HAVING: the WHERE clause filters individual rows before they are grouped. The HAVING clause filters the groups made by GROUP BY, and it can use aggregate functions such as COUNT.
- NULL in Aggregate Functions: COUNT(*) counts every row, including rows with NULL values. COUNT(column_name), SUM() and AVG() skip NULL values in that column.
Exam Tips
- Write longer SQL statements in triple quotes. The query can then run over several lines, and single or double quotes inside it cause no trouble.
- Call close() when you have finished, so the connection and its resources are released.
- ORDER BY only sorts the rows in the output. The order of rows stored in the table does not change.
- sqlite_master is the master table that stores the structure of every table in the database. Querying it is how you list the table names.