Python SQLite
SQLite is a complete SQL database stored in a single file, and Python includes the sqlite3 module to use it, with nothing to install. It is ideal for small apps, test data and learning SQL from Python.
- Connect and create tables
- Insert rows safely with parameters
- Query, update and delete
- Use transactions and load results into pandas
Connect and create a table
sqlite3.connect() opens a database file, creating it if it does not exist. A cursor runs SQL statements.
import sqlite3 conn = sqlite3.connect("shop.db")cur = conn.cursor()cur.execute("DROP TABLE IF EXISTS products")cur.execute(""" CREATE TABLE products ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, category TEXT, price REAL, stock INTEGER DEFAULT 0 )""")conn.commit()print(cur.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall())conn.close()[('products',), ('sqlite_sequence',)]sqlite3.connect(":memory:") creates a temporary database in memory, which is perfect for tests.
Insert rows with parameters
Use ? placeholders and pass the values separately. Never build SQL with f-strings from user input; that opens the door to SQL injection.
import sqlite3 conn = sqlite3.connect("shop.db")cur = conn.cursor()cur.execute("INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?)", ("Keyboard", "electronics", 1499.0, 25)) items = [ ("Mouse", "electronics", 349.0, 60), ("Notebook", "stationery", 45.0, 300), ("Pen", "stationery", 10.0, 0), ("Monitor", "electronics", 12999.0, 8),]cur.executemany("INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?)", items)conn.commit()print("last id:", cur.lastrowid, "rows:", cur.execute("SELECT COUNT(*) FROM products").fetchone()[0])conn.close()last id: 1 rows: 5
Query rows
import sqlite3 conn = sqlite3.connect("shop.db")cur = conn.cursor() for row in cur.execute("SELECT id, name, price FROM products ORDER BY price DESC LIMIT 3"): print(row) cur.execute("SELECT name FROM products WHERE category = ? AND stock > ?", ("electronics", 10))print(cur.fetchall()) print(cur.execute("SELECT AVG(price) FROM products WHERE category = 'stationery'").fetchone())conn.close()(5, 'Monitor', 12999.0)
(1, 'Keyboard', 1499.0)
(2, 'Mouse', 349.0)
[('Keyboard',), ('Mouse',)]
(27.5,)| Method | Returns |
|---|---|
fetchone() | the next row as a tuple, or None |
fetchmany(n) | a list of up to n rows |
fetchall() | a list of all remaining rows |
Rows as dictionaries
Set row_factory to sqlite3.Row to read columns by name:
import sqlite3 conn = sqlite3.connect("shop.db")conn.row_factory = sqlite3.Rowrow = conn.execute("SELECT * FROM products WHERE name = ?", ("Mouse",)).fetchone()print(row["name"], row["price"], row["stock"])print(dict(row))conn.close()Mouse 349.0 60
{'id': 2, 'name': 'Mouse', 'category': 'electronics', 'price': 349.0, 'stock': 60}Update and delete
import sqlite3 conn = sqlite3.connect("shop.db")cur = conn.cursor()cur.execute("UPDATE products SET price = ROUND(price * 0.9, 2) WHERE category = ?", ("electronics",))print("discounted:", cur.rowcount)cur.execute("DELETE FROM products WHERE stock = 0")print("deleted:", cur.rowcount)conn.commit()print(conn.execute("SELECT name, price FROM products ORDER BY id").fetchall())conn.close()discounted: 3
deleted: 1
[('Keyboard', 1349.1), ('Mouse', 314.1), ('Notebook', 45.0), ('Monitor', 11699.1)]Transactions
Changes become permanent only on commit(); rollback() undoes everything since the last commit. Using the connection in a with block commits on success and rolls back on an error:
import sqlite3 conn = sqlite3.connect("shop.db")try: with conn: conn.execute("UPDATE products SET stock = stock - 5 WHERE name = 'Monitor'") conn.execute("INSERT INTO products (name) VALUES (NULL)") # violates NOT NULLexcept sqlite3.IntegrityError as e: print("rolled back:", e) print(conn.execute("SELECT stock FROM products WHERE name = 'Monitor'").fetchone())conn.close()rolled back: NOT NULL constraint failed: products.name (8,)
The stock is still 8 because the whole transaction was rolled back when the second statement failed.
Join two tables
import sqlite3 conn = sqlite3.connect(":memory:")conn.executescript(""" CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL); INSERT INTO users VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meena'); INSERT INTO orders VALUES (1, 1, 500), (2, 1, 250), (3, 3, 900);""")query = """ SELECT u.name, COUNT(o.id), COALESCE(SUM(o.amount), 0) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id ORDER BY u.name"""for row in conn.execute(query): print(row)conn.close()('Asha', 2, 750.0)
('Meena', 1, 900.0)
('Ravi', 0, 0)SQLite with pandas
import sqlite3import pandas as pd conn = sqlite3.connect("shop.db")df = pd.read_sql_query("SELECT name, category, price FROM products", conn)print(df)print(df.groupby("category")["price"].mean().round(1).to_dict())conn.close() name category price
0 Keyboard electronics 1349.1
1 Mouse electronics 314.1
2 Notebook stationery 45.0
3 Monitor electronics 11699.1
{'electronics': 4454.1, 'stationery': 45.0}Check your understanding
Quick quiz
1. Why use ? placeholders instead of f-strings in SQL?
The driver escapes the values safely.
2. What makes inserted rows permanent?
Without commit, changes are lost when the connection closes.
3. Which returns a single row?
It returns a tuple, or None when no rows are left.
Exercise: student marks database
Create an in-memory table of students and marks, then print the average mark and the names of students above the average.
("Asha", 88), ("Ravi", 64), ("Meena", 93), ("John", 71)Average: 79.0 Above average: ['Meena', 'Asha']
Hint 1
A subquery works: WHERE marks > (SELECT AVG(marks) FROM students).
import sqlite3 conn = sqlite3.connect(":memory:")conn.execute("CREATE TABLE students (name TEXT, marks INTEGER)")conn.executemany("INSERT INTO students VALUES (?, ?)", [("Asha", 88), ("Ravi", 64), ("Meena", 93), ("John", 71)]) avg = conn.execute("SELECT AVG(marks) FROM students").fetchone()[0]above = conn.execute("SELECT name FROM students WHERE marks > (SELECT AVG(marks) FROM students) ORDER BY marks DESC").fetchall()print("Average:", avg)print("Above average:", [name for (name,) in above])conn.close()Average: 79.0 Above average: ['Meena', 'Asha']
The subquery computes the average inside SQL, so the filter does not need the Python value. (name,) unpacks each one-column row.
Program Practice
20 Python SQLite Programs
Every program has a step-by-step explanation, colour-coded code and its real output.
Python SQLite FAQ
Do I need to install SQLite for Python?
No. The sqlite3 module and the SQLite engine come with Python.
How do I see the column names of a query result?
Read cursor.description (the first item of each entry is the name), or set conn.row_factory = sqlite3.Row and call row.keys().
Is SQLite good for production?
It is excellent for local apps, tools, tests and small to medium sites with few concurrent writers. Use PostgreSQL or MySQL for many simultaneous writers.
2 thoughts on “Python SQLite”