Build your first Python database interface.

Why build a Python database interface?

At some point, every Python project outgrows ad-hoc CSV files and in-memory lists. You need a place to store data reliably, query it flexibly and avoid rewriting the same SQL over and over. That is exactly what a small, well-structured database interface gives you.

In this tutorial, you will build a reusable DatabaseInterface class around SQLite, seed it from a CSV file, run a simple demo script and validate everything with automated tests. All of the code is available in a public GitHub repository you can clone and extend.

What you’re going to build

The project in the repo is called python-database-interface-tutorial. Inside it, you have a small but realistic layout:

python-database-interface-tutorial/
├── src/
│   ├── db_interface.py      # DatabaseInterface and Record
│   ├── setup_db.py          # Load CSV data into SQLite
│   ├── run_demo.py          # Command-line CRUD demo
│   └── utils.py             # Paths and pretty-print helpers
├── data/
│   └── python_db_records.csv
├── tests/
│   └── test_db_interface.py # Standard-library unit tests
└── records.db               # Created by your scripts
    

The goal of this article is to walk through the design and implementation of these files so that you understand every line and can adapt the pattern to your own databases.

Step 1: Run the project and see it working

Before diving into the code, make sure you can run the scripts end-to-end. From the project root after cloning the repo:

# Seed the database from CSV
python3 src/setup_db.py

# Run the demo CRUD workflow
python3 src/run_demo.py

# Run the tests
python3 -m unittest tests.test_db_interface
    

You should see the records from the CSV printed out, a new record created, updated, deleted and the tests finishing with OK. If that works, you have a working baseline and the rest of this tutorial is about understanding and customising it.

Step 2: Understand the records table and CSV data

The project starts with a simple records table. Each row has: an integer id, a text name, a numeric value and an ISO-formatted created_at date.

The SQLite schema

The table is created by DatabaseInterface.create_table_if_not_exists, which executes a simple CREATE TABLE statement if the table does not already exist.

def create_table_if_not_exists(self) -> None:
    sql = """
    CREATE TABLE IF NOT EXISTS records (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        value REAL NOT NULL,
        created_at TEXT NOT NULL
    )
    """
    with self.get_connection() as conn:
        conn.execute(sql)
    

This gives you a concrete schema to work with while keeping the example compact enough to read and extend in one sitting.

Step 3: Build the DatabaseInterface skeleton

The heart of the project is src/db_interface.py, which provides a small, reusable wrapper around sqlite3. It hides connection management and gives you clean, parameterised methods for reads and writes.

1. Store the database path
   The constructor accepts a path to the SQLite file and ensures the parent directory exists, which makes it safe to point at subfolders in larger projects.
2. Provide a context-managed connection
   get_connection wraps sqlite3.connect in a context manager that automatically commits on success and rolls back on error, then closes the connection.
3. Expose small helpers for reads and writes
   execute, fetch_one and fetch_all centralise boilerplate so your CRUD methods stay short and easy to test.

from contextlib import contextmanager
from dataclasses import dataclass
from datetime import datetime
from pathlib import Path
import sqlite3

@dataclass
class Record:
    id: int
    name: str
    value: float
    created_at: str

    @classmethod
    def from_row(cls, row: sqlite3.Row) -> "Record":
        return cls(
            id=row["id"],
            name=row["name"],
            value=row["value"],
            created_at=row["created_at"],
        )

class DatabaseInterface:
    def __init__(self, db_path: Path | str) -> None:
        self.db_path = Path(db_path)
        if self.db_path.parent != Path("."):
            self.db_path.parent.mkdir(parents=True, exist_ok=True)

    @contextmanager
    def get_connection(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()
    

Step 4: Implement CRUD methods cleanly

With the skeleton in place, the interface adds one method for each CRUD operation on the records table: create, read, update and delete.

def create_record(
    self,
    name: str,
    value: float,
    created_at: str | None = None,
    *,
    id_override: int | None = None,
) -> int:
    if created_at is None:
        created_at = datetime.utcnow().date().isoformat()

    if id_override is None:
        sql = """
        INSERT INTO records (name, value, created_at)
        VALUES (?, ?, ?)
        """
        params = (name, value, created_at)
    else:
        sql = """
        INSERT INTO records (id, name, value, created_at)
        VALUES (?, ?, ?, ?)
        """
        params = (id_override, name, value, created_at)

    return self.execute(sql, params)

def get_record(self, record_id: int) -> Record | None:
    row = self.fetch_one(
        "SELECT id, name, value, created_at FROM records WHERE id = ?",
        (record_id,),
    )
    return Record.from_row(row) if row else None

def delete_record(self, record_id: int) -> int:
    with self.get_connection() as conn:
        cur = conn.execute("DELETE FROM records WHERE id = ?", (record_id,))
        return cur.rowcount or 0
    

Each method delegates the low-level work to execute or fetch_one and keeps the SQL statements short and explicit.

Step 5: Seed the database from CSV

The next piece is src/setup_db.py, which loads your CSV file and inserts each row into the database using DatabaseInterface.create_record.

from db_interface import DatabaseInterface
from utils import DB_PATH, DATA_DIR

def main() -> None:
    db = DatabaseInterface(DB_PATH)
    db.create_table_if_not_exists()

    csv_path = DATA_DIR / "python_db_records.csv"
    records = load_csv_records(csv_path)

    deleted = db.delete_all_records()
    print(f"Cleared {deleted} existing records.")

    for rec in records:
        db.create_record(
            name=rec["name"],
            value=rec["value"],
            created_at=rec["created_at"],
            id_override=rec["id"],
        )

    print(f"Inserted {len(records)} records from CSV into {DB_PATH}.")
    

Keeping CSV loading in a separate script lets you reseed the database quickly when you change the sample data or reset the project during development.

Step 6: Drive it from a small CLI demo

A top-level script makes it easy to prove the interface works and to share the behaviour with teammates. The demo in src/run_demo.py exercises the core CRUD operations and prints results in a readable format.

from db_interface import DatabaseInterface
from utils import DB_PATH, pretty_print_records

def main() -> None:
    db = DatabaseInterface(DB_PATH)
    db.create_table_if_not_exists()

    print(f"Using database at: {DB_PATH}")

    print("\n--- Existing records ---")
    pretty_print_records(db.get_all_records())

    print("\n--- Creating a new record ---")
    new_id = db.create_record(name="Zeta", value=400.0)

    print("\n--- Updating the new record ---")
    db.update_record(new_id, value=425.0)

    print("\n--- Deleting the new record ---")
    db.delete_record(new_id)

    print("\n--- Final records ---")
    pretty_print_records(db.get_all_records())

if __name__ == "__main__":
    main()
    

This pattern scales nicely: you can add command-line arguments, support different tables or move the logic behind an HTTP API without changing the underlying interface.

Step 7: Add confidence with unit tests

The repository includes tests/test_db_interface.py, a small unittest-based test suite that validates create, read, update and delete operations against a temporary database file.

import sys
import tempfile
import unittest
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
SRC_DIR = ROOT / "src"
if str(SRC_DIR) not in sys.path:
    sys.path.insert(0, str(SRC_DIR))

from db_interface import DatabaseInterface

class TestDatabaseInterface(unittest.TestCase):
    def setUp(self) -> None:
        tmp_dir = tempfile.TemporaryDirectory()
        self.addCleanup(tmp_dir.cleanup)
        self.db_path = Path(tmp_dir.name) / "test.db"
        self.db = DatabaseInterface(self.db_path)
        self.db.create_table_if_not_exists()
    

Even for a small project, tests like these catch regressions when you refactor or extend the interface, and they document the expected behaviour for future you.

Illustrative chart: record values at a glance

To make the data a bit more tangible, you can visualise the value field from the sample records as a simple line chart. This is an illustrative chart based on the same sample dataset.

This chart is based on the illustrative sample dataset from python_db_records.csv, with the value column plotted for each record.

Where to go next

The pattern in this tutorial is intentionally small but realistic. Once you are comfortable with it, you can:

• Add more tables and write matching CRUD methods for each.
• Swap SQLite for PostgreSQL using a driver like psycopg.
• Expose your interface through a Flask API instead of a CLI script.
• Introduce migrations or an ORM if the schema grows complex.

The key takeaway is the architecture: keep database access in a dedicated module, give it a clear, testable interface and build the rest of your application on top of that contract.

Related PLEX reading

References & further reading

• Python PEP 249 — Database API Specification v2.0
  The formal standard that most Python database drivers follow, including the parameterised query style used here.
• Python Documentation — sqlite3 module
  Official reference for using SQLite from Python's standard library.
• SQLite — Official Documentation
  Comprehensive documentation for the SQLite database engine, including SQL features and file format details.
• Real Python — Python Databases Tutorial
  A broader overview of working with different databases and drivers from Python.
• Psycopg 3 Documentation — Basics
  How to connect Python to PostgreSQL using a DB-API compatible driver.
• GitHub — python-database-interface-tutorial repository
  Full source code for the project used in this tutorial.