Data manipulation through SQL - Study Notes
Chapter Summary
This chapter explores the integration of Python with database management systems, specifically using the lightweight SQLite database library. It details how Python scripts can create databases and tables, perform data manipulation operations such as inserting, updating, and deleting records, and execute complex SQL queries with various clauses like DISTINCT, WHERE, GROUP BY, ORDER BY, and HAVING. Additionally, the chapter illustrates the use of aggregate functions and explains how to export database query results directly to structured CSV files.
Learning Objectives
- Understand how Python communicates with SQLite to establish a database connection.
- Learn how to create tables, define schemas, and handle constraints programmatically.
- Master the execution of basic SQL queries including SELECT, INSERT, UPDATE, and DELETE.
- Apply filtering and grouping clauses such as WHERE, DISTINCT, ORDER BY, GROUP BY, and HAVING.
- Perform calculations using aggregate functions like COUNT, SUM, AVG, MAX, and MIN.
- Implement techniques to dynamically input data from the user and save the results.
- Integrate database queries with external files by writing query outputs to CSV files.
Key Concepts and Definitions
SQLite
A lightweight, serverless relational database engine embedded directly within applications, eliminating the need for a separate database server process.
Connection Object
An active session established between Python and the SQLite database file, instantiated using the connect() method.
Cursor Object
A control structure used to traverse, execute, and fetch records of a database query. It acts as a middleman for executing SQL statements.
Placeholder
A special symbol (such as a question mark ? or a named style variable) used in SQL statements to dynamically insert values securely during runtime.
Commit
An operation that permanently saves all changes made during a database transaction to the disk, ensuring data persistence.
Rollback
An operation that reverts the database state back to the last committed checkpoint, discarding any unsaved transactions.
Worked Methods
Connecting to a Database and Creating a Table
To perform operations, we first establish a connection and create a table schema using the cursor execution method.
First, we import the database module. Next, we establish a connection using the connect() method, which creates a new file if it does not already exist. Then, we create a cursor object using the cursor() method. We define our table creation SQL query in a string, and pass it to execute(). Finally, we commit the transaction and close the database connection.
Inserting and Retrieving Records
To insert records, we execute the INSERT INTO statement. To retrieve records, we execute a SELECT query and use retrieval methods like fetchall() or fetchone() to read the query outputs into Python objects.
Common Exam Traps
- Forgetting to Commit: Any data modification statement (INSERT, UPDATE, DELETE) will not be saved permanently to the database file if commit() is not called. Changes are lost upon closing the connection.
- Tuple Unpacking: Database records fetched via the cursor are returned as tuples. Beginners often try to print them as plain strings. To unpack them nicely, use the asterisk symbol * or loop through the collection.
- WHERE vs HAVING: Students often confuse these two clauses. Remember that the WHERE clause filters rows before grouping, while the HAVING clause filters groups after grouping and works with aggregate functions.
- Handling NULL in aggregations: Be aware that COUNT(*) includes rows with NULL values, whereas COUNT(column_name) and other aggregate functions like SUM() or AVG() ignore them entirely.
Exam Tips
- Always write SQL queries inside Python using triple quotes to handle single or double quotes within column values easily.
- Ensure you call close() on both the cursor and connection objects to free up resources.
- For sorting query results, remember that the ORDER BY clause does not modify the original table; it only sorts the output presentation.
- When using sqlite_master, remember it is the master table containing structural metadata for all tables in the active database.