SQLite
SQLite is a lightweight, serverless, self-contained relational database that stores its data in a single file on disk. It is included with Python through t...
Working with SQLite in Python Using sqlite3
SQLite is a lightweight, serverless, self-contained relational database that stores its data in a single file on disk. It is included with Python through the sqlite3 module in the standard library, making it the easiest database to get started with. You do not need to install or configure a database server. SQLite is ideal for desktop applications, development and testing, small-to-medium web apps, and storing structured local data.
import sqlite3
# Connect to a database file (creates it if it does not exist) conn = sqlite3.connect("school.db") cursor = conn.cursor()
# Create a table cursor.execute(""" CREATE TABLE IF NOT EXISTS students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, grade INTEGER ) """)
# Insert rows using parameterized queries to prevent SQL injection students = [("Alice", 90), ("Bob", 85), ("Carol", 92)] cursor.executemany("INSERT INTO students (name, grade) VALUES (?, ?)", students) conn.commit()
# Query data cursor.execute("SELECT * FROM students WHERE grade > ?", (88,)) for row in cursor.fetchall(): print(row)
conn.close()
Using ? placeholders in queries is mandatory for any user-supplied data. String formatting SQL queries directly with f-strings or % is a severe security vulnerability known as SQL injection. Always use parameterized queries.
The connection object manages transactions. Changes made with INSERT, UPDATE, or DELETE are not saved to disk until you call conn.commit(). If an error occurs and you want to discard changes, call conn.rollback(). Using a with statement on a connection handles this automatically: it commits on success and rolls back on exception.
with sqlite3.connect("school.db") as conn: conn.execute("UPDATE students SET grade = 95 WHERE name = 'Alice'")
SQLite supports most standard SQL: SELECT, INSERT, UPDATE, DELETE, JOIN, CREATE TABLE, DROP TABLE, and transactions. It does not support all data types or concurrent multi-writer access, which is where MySQL or PostgreSQL become necessary.
Exam tip: Remember that sqlite3 uses ? as the placeholder for parameterized queries, while MySQL uses %s. Both prevent SQL injection, but the syntax differs. Also know that conn.commit() must be called explicitly unless you use the connection as a context manager.