Python SQLite: Build and Query Databases Without a Server
Every app needs to store data somewhere. A to-do list needs to remember your tasks. A contacts app needs to save names and phone numbers. You could write everything to a text file, but what happens when you need to find all contacts in a specific city, or delete tasks older than a week?
That's where databases come in. SQLite is a lightweight database built right into Python -- no server to install, no configuration needed. You talk to it using SQL (Structured Query Language), the universal language for databases.
In this tutorial, you'll learn to create tables, insert data, query with filters, update records, delete rows, and use parameterized queries for safety. All examples use in-memory databases, so everything runs right in your browser.
How Do You Connect to a SQLite Database?
To work with SQLite in Python, you import sqlite3 and call connect(). For these exercises we use ":memory:", which creates a temporary database in RAM. In real applications, you'd pass a filename like "myapp.db" to save data to disk.
The connection object manages the database. The cursor object is like a pointer that executes SQL commands and fetches results. Think of the connection as the phone line and the cursor as the conversation.
How Do You Create Tables and Insert Data?
A database table is like a spreadsheet -- it has columns (fields) and rows (records). You define the table structure with CREATE TABLE and add data with INSERT INTO.
A few things to notice: INTEGER PRIMARY KEY AUTOINCREMENT creates an auto-incrementing ID column. NOT NULL means the field is required. The ? placeholders are crucial for safety -- more on that later. Always call conn.commit() to save changes.
How Do You Query Data with SELECT and WHERE?
SELECT retrieves data from a table. Add a WHERE clause to filter results. Use fetchall() to get all matching rows as a list of tuples, or fetchone() to get just the first match.
How Do You Update and Delete Records?
UPDATE changes existing records. DELETE removes them. Both use WHERE to target specific rows. Without a WHERE clause, UPDATE changes every row and DELETE removes every row -- so always double-check your conditions.
Why Are Parameterized Queries Important?
Never build SQL queries by concatenating strings with user input. If a user types '; DROP TABLE contacts; -- as their name, your database could be destroyed. This is called SQL injection, and it's one of the most common security vulnerabilities.
# NEVER do this!
name = user_input
cursor.execute(f"SELECT * FROM contacts WHERE name = '{name}'")# Always use ? placeholders
name = user_input
cursor.execute("SELECT * FROM contacts WHERE name = ?", (name,))With parameterized queries, the ? placeholder tells SQLite to treat the value as data, never as SQL code. The database engine handles escaping automatically, making SQL injection impossible.
How Do You Sort Results and Use Aggregate Functions?
ORDER BY sorts your results. Add ASC for ascending (the default) or DESC for descending. Aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() perform calculations across rows.
GROUP BY groups rows with the same value together, which is essential for aggregate functions. In the example above, GROUP BY product lets COUNT(*) count how many sales each product had. DISTINCT removes duplicate rows from results.
Practice Exercises
Create an in-memory SQLite database with a students table having columns id (INTEGER PRIMARY KEY AUTOINCREMENT), name (TEXT), and grade (INTEGER). Insert three students: Alice (grade 90), Bob (grade 85), Charlie (grade 92). Then select and print all students in the format name: grade.
Create a products table with columns id (INTEGER PRIMARY KEY AUTOINCREMENT), name (TEXT), and price (REAL). Insert these products: Laptop (999.99), Mouse (29.99), Keyboard (79.99), Monitor (349.99), Webcam (59.99). Then use a WHERE clause to select and print only products that cost less than 100, in the format name: $price.
What will this code print? Think about what UPDATE does and how the final SELECT will return the data.
Create an inventory table with columns item (TEXT) and quantity (INTEGER). Insert: Apples (50), Bananas (30), Oranges (0), Grapes (15). Update Bananas to quantity 45. Delete all items with quantity 0. Then print the remaining items in the format item: quantity.
This code has a dangerous SQL injection vulnerability. Fix it by converting the string-formatted query to use parameterized queries with ? placeholders. The behavior should remain the same -- just make it safe.
Build a mini contact book that performs all CRUD operations. Create a contacts table with name (TEXT) and phone (TEXT). Insert: Alice (555-0001), Bob (555-0002), Charlie (555-0003). Update Bob's phone to 555-9999. Delete Charlie. Then print all remaining contacts in the format name: phone, followed by a blank line and the total count in the format Total: N contacts.