Learn AI with Python · Lesson

SQLite with Python's sqlite3 Module

Creating databases, tables, INSERT/SELECT/UPDATE, parameterized queries, connection management.

SQLite with Python's sqlite3 Module is a free Learn AI with Python lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Learn AI with Python learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Databases for AI?

Flat files work until your data grows or many parts of a project need the same records. A database gives you fast queries, structured schemas, and safe concurrent access.

SQLite is a zero-setup file-based database built into Python via the sqlite3 module — perfect for AI experiments and small projects.

Connecting with sqlite3.connect

sqlite3.connect opens (or creates) a database file and returns a connection. Use :memory: for a temporary in-RAM database.

import sqlite3

conn = sqlite3.connect("ml.db")   # creates ml.db if missing
print(conn)

Cursors and cursor.execute

You run SQL through a cursor obtained from the connection. cursor.execute(sql) sends a statement to the database.

import sqlite3

conn = sqlite3.connect("ml.db")
cur = conn.cursor()
cur.execute("SELECT sqlite_version()")
print(cur.fetchone())

CREATE TABLE IF NOT EXISTS

Define a table with a schema. IF NOT EXISTS makes the statement safe to run repeatedly — it will not error if the table already exists.

cur.execute("""
CREATE TABLE IF NOT EXISTS experiments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    accuracy REAL,
    created_at TEXT
)
""")

INSERT with Placeholders

Never build SQL with string formatting — it invites SQL injection and breaks on quotes. Use ? placeholders and pass values as a tuple.

cur.execute(
    "INSERT INTO experiments (name, accuracy, created_at) VALUES (?, ?, ?)",
    ("baseline", 0.82, "2026-05-29"),
)

Inserting Many Rows with executemany

executemany inserts a list of tuples in one efficient call — much faster than looping execute.

rows = [
    ("rf", 0.88, "2026-05-29"),
    ("xgb", 0.91, "2026-05-29"),
]
cur.executemany(
    "INSERT INTO experiments (name, accuracy, created_at) VALUES (?, ?, ?)",
    rows,
)

conn.commit() Saves Changes

Writes happen inside a transaction and are not persisted until you call conn.commit(). Forgetting this is the most common beginner bug — your data silently vanishes on close.

conn.commit()   # persist all pending INSERT/UPDATE/DELETE

Querying with fetchall and fetchone

After a SELECT, retrieve results:

fetchone() — next single row
fetchall() — all remaining rows as a list of tuples
iterate the cursor directly for large results

cur.execute("SELECT name, accuracy FROM experiments")
for row in cur.fetchall():
    print(row)   # ("baseline", 0.82) ...

Filtering and Ordering

SQL does the heavy lifting: filter with WHERE, sort with ORDER BY, limit with LIMIT. Use placeholders for query values too.

cur.execute(
    "SELECT name, accuracy FROM experiments WHERE accuracy > ? ORDER BY accuracy DESC LIMIT 3",
    (0.85,),
)
print(cur.fetchall())

Row Factory for Named Access

By default rows are tuples. Set conn.row_factory = sqlite3.Row to access columns by name, which is far more readable.

import sqlite3

conn = sqlite3.connect("ml.db")
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute("SELECT * FROM experiments")
row = cur.fetchone()
print(row["name"], row["accuracy"])

Context Managers for Safety

Using with on the connection auto-commits on success and rolls back on error. Combined with closing, it guarantees cleanup.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("ml.db")) as conn:
    with conn:   # auto commit/rollback
        conn.execute(
            "INSERT INTO experiments (name, accuracy) VALUES (?, ?)",
            ("svm", 0.79),
        )

Quick Check: Persisting Writes

You ran several INSERT statements, then closed the connection — but the data was gone next time.

Recap: SQLite with sqlite3

You can now use a real database from Python:

sqlite3.connect and a cursor
CREATE TABLE IF NOT EXISTS for safe schemas
INSERT with ? placeholders and executemany
conn.commit() to persist writes
fetchone/fetchall, WHERE/ORDER BY/LIMIT
sqlite3.Row and context managers for clean, safe code

Next: bridging pandas and SQL.

Frequently asked questions

Is the “SQLite with Python's sqlite3 Module” lesson free?

Yes — the full text of “SQLite with Python's sqlite3 Module” is free to read here on the web, and the Learn AI with Python course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Learn AI with Python course, upgrade to CoddyKit PRO.

What will I learn in “SQLite with Python's sqlite3 Module”?

Creating databases, tables, INSERT/SELECT/UPDATE, parameterized queries, connection management. You practise Learn AI with Python with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Learn AI with Python?

No prior experience is required. Learn AI with Python on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “SQLite with Python's sqlite3 Module” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Learn AI with Python lesson?

Yes. Every Learn AI with Python lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Learn AI with Python