Working with a SQLite Database in Python

In the files tutorial we saw how to read and write with open. But a text file or CSV only gets you so far: the moment you need “just the quotes by this author” or “don’t add duplicates,” you end up looping through the whole file in Python. A database does that with one line of SQL. Good news: sqlite3 ships in Python’s standard library — nothing to install.

Create a database and your first table

import sqlite3

conn = sqlite3.connect("quotes.db")
cursor = conn.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS quotes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        text TEXT NOT NULL,
        author TEXT NOT NULL,
        UNIQUE(text, author)
    )
""")
conn.commit()

sqlite3.connect("quotes.db") creates the file if it doesn’t exist yet. UNIQUE(text, author) guarantees the same quote from the same author never gets inserted twice.

Inserting data safely — and why you should never paste strings by hand

# Wrong — never write it this way:
cursor.execute(f"INSERT INTO quotes (text, author) VALUES ('{text}', '{author}')")

If text contains an apostrophe or SQL syntax, this breaks your query or, worse, opens the door to SQL injection — the same family of risk we warned about with token handling in the Telegram bot tutorial. The correct approach is always the ? placeholder:

cursor.execute(
    "INSERT OR IGNORE INTO quotes (text, author) VALUES (?, ?)",
    (text, author)
)
conn.commit()

Python escapes the value for you; your data never gets pasted directly into the SQL string. INSERT OR IGNORE means a duplicate row (caught by that UNIQUE constraint) is silently skipped instead of raising an error.

A real example: importing quotes.csv into the database

Let’s move the entire quotes.csv we built in the web scraping tutorial into SQLite:

import csv
import sqlite3

conn = sqlite3.connect("quotes.db")
cursor = conn.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS quotes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        text TEXT NOT NULL,
        author TEXT NOT NULL,
        UNIQUE(text, author)
    )
""")

with open("quotes.csv", encoding="utf-8-sig") as f:
    reader = csv.DictReader(f)
    for row in reader:
        cursor.execute(
            "INSERT OR IGNORE INTO quotes (text, author) VALUES (?, ?)",
            (row["text"], row["author"])
        )

conn.commit()
print(f"{cursor.rowcount} row(s) inserted this run")
conn.close()

Reading data

conn = sqlite3.connect("quotes.db")
cursor = conn.cursor()

cursor.execute("SELECT text, author FROM quotes")
for text, author in cursor.fetchall():
    print(f"{text} — {author}")

fetchall() returns every row at once; for very large tables, call fetchone() inside a loop instead so you don’t load everything into memory.

Filtering and searching

# Only quotes from one author
cursor.execute("SELECT text FROM quotes WHERE author = ?", ("Albert Einstein",))

# Search inside the text
cursor.execute("SELECT text, author FROM quotes WHERE text LIKE ?", ("%life%",))

Same rule applies here: the search value always goes through the ? placeholder, never pasted into the SQL string — no exceptions.

Updating and deleting

cursor.execute("UPDATE quotes SET author = ? WHERE id = ?", ("New Author", 3))
cursor.execute("DELETE FROM quotes WHERE id = ?", (3,))
conn.commit()

Browsing the database without writing code

To poke around quotes.db without Python, install DB Browser for SQLite — free and open source. Open the .db file and browse the tables like a spreadsheet.

When SQLite is enough — and when to migrate

• For personal scripts, desktop tools, or anything with a single concurrent user, SQLite is exactly enough: lightweight, no server to run, just one file
• Once several users need to write at the same time — a web app with real traffic, for instance — it’s time to move to PostgreSQL or MySQL

Exercise

Add a tags column to the quotes table (ALTER TABLE quotes ADD COLUMN tags TEXT), then update the import script so it also reads each quote’s tags from the CSV and stores them in that column.