How to Build a Peptide Research Database Using Python and SQLite

Faiz Rasool — Tue, 03 Nov 2026 12:08:05 +0000

If you work with research compounds, you quickly end up with a mess of spreadsheets: product names, CAS numbers, molecular weights, batch numbers, purity results from Certificates of Analysis (COAs), and storage notes. Spreadsheets break the moment two people edit them or you need to ask a question like "which batches are below 98% purity?"

In this tutorial we'll build a small, fast, zero-setup peptide research database with Python's built-in sqlite3 module. No server, no ORM, no extra installs.

By the end you will have:

A clean relational schema (peptides → batches → test results)
A script to import data from CSV
Useful queries (purity filters, latest batch per peptide, averages)
A tiny command-line search tool

Why SQLite?

It ships with Python — import sqlite3 and you're done.
The whole database is one file you can back up or commit.
It handles millions of rows easily, which is far more than a research catalog needs.
Real SQL means real queries, joins and constraints.

1. Design the schema

A peptide can have many batches, and each batch can have many test results (HPLC purity, mass spec, endotoxin, etc.). That gives us three tables:

# schema.py
import sqlite3

SCHEMA = """
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS peptides (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            TEXT NOT NULL UNIQUE,
    sequence        TEXT,
    cas_number      TEXT,
    molecular_weight REAL,
    storage_temp_c  REAL,
    notes           TEXT
);

CREATE TABLE IF NOT EXISTS batches (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    peptide_id   INTEGER NOT NULL REFERENCES peptides(id) ON DELETE CASCADE,
    batch_number TEXT NOT NULL UNIQUE,
    quantity_mg  REAL,
    received_on  TEXT  -- ISO date: YYYY-MM-DD
);

CREATE TABLE IF NOT EXISTS test_results (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    batch_id   INTEGER NOT NULL REFERENCES batches(id) ON DELETE CASCADE,
    method     TEXT NOT NULL,      -- e.g. 'HPLC', 'MS'
    purity_pct REAL,
    tested_on  TEXT,
    lab        TEXT
);

CREATE INDEX IF NOT EXISTS idx_batches_peptide ON batches(peptide_id);
CREATE INDEX IF NOT EXISTS idx_results_batch  ON test_results(batch_id);
"""

def get_connection(path="peptides.db"):
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row      # rows behave like dicts
    conn.execute("PRAGMA foreign_keys = ON")
    return conn

def init_db(path="peptides.db"):
    with get_connection(path) as conn:
        conn.executescript(SCHEMA)

if __name__ == "__main__":
    init_db()
    print("Database ready: peptides.db")

Two details worth noting:

PRAGMA foreign_keys = ON must be set per connection — SQLite has foreign keys off by default.
sqlite3.Row lets you access columns by name (row["name"]), which keeps the rest of the code readable.

2. Insert data safely

Always use parameterized queries (? placeholders). Never build SQL with f-strings — that's how SQL injection happens.

# add_data.py
from schema import get_connection

def add_peptide(conn, name, sequence=None, cas=None, mw=None, storage=None, notes=None):
    cur = conn.execute(
        """INSERT INTO peptides (name, sequence, cas_number, molecular_weight, storage_temp_c, notes)
           VALUES (?, ?, ?, ?, ?, ?)
           ON CONFLICT(name) DO UPDATE SET
               sequence = excluded.sequence,
               cas_number = excluded.cas_number,
               molecular_weight = excluded.molecular_weight""",
        (name, sequence, cas, mw, storage, notes),
    )
    return conn.execute("SELECT id FROM peptides WHERE name = ?", (name,)).fetchone()["id"]

def add_batch(conn, peptide_id, batch_number, quantity_mg, received_on):
    cur = conn.execute(
        "INSERT INTO batches (peptide_id, batch_number, quantity_mg, received_on) VALUES (?, ?, ?, ?)",
        (peptide_id, batch_number, quantity_mg, received_on),
    )
    return cur.lastrowid

def add_result(conn, batch_id, method, purity_pct, tested_on, lab):
    conn.execute(
        "INSERT INTO test_results (batch_id, method, purity_pct, tested_on, lab) VALUES (?, ?, ?, ?, ?)",
        (batch_id, method, purity_pct, tested_on, lab),
    )

if __name__ == "__main__":
    with get_connection() as conn:
        pid = add_peptide(conn, "BPC-157", cas="137525-51-0", mw=1419.5, storage=-20)
        bid = add_batch(conn, pid, "BPC-2026-001", 500, "2026-10-01")
        add_result(conn, bid, "HPLC", 99.1, "2026-10-05", "Third-party lab A")

Using with get_connection() as conn: wraps everything in a transaction — it commits on success and rolls back if an exception is raised.

3. Bulk import from CSV

Most catalogs already live in a spreadsheet. Export it to CSV with columns like:

name,cas_number,molecular_weight,batch_number,quantity_mg,received_on,method,purity_pct,tested_on,lab

Then import it in one pass:

# import_csv.py
import csv, sys
from schema import get_connection, init_db
from add_data import add_peptide, add_batch, add_result

def to_float(value):
    return float(value) if value not in (None, "") else None

def import_csv(path):
    init_db()
    imported = 0
    with get_connection() as conn, open(path, newline="", encoding="utf-8") as f:
        for row in csv.DictReader(f):
            pid = add_peptide(conn, row["name"].strip(),
                              cas=row.get("cas_number"),
                              mw=to_float(row.get("molecular_weight")))
            existing = conn.execute(
                "SELECT id FROM batches WHERE batch_number = ?", (row["batch_number"],)
            ).fetchone()
            bid = existing["id"] if existing else add_batch(
                conn, pid, row["batch_number"], to_float(row["quantity_mg"]), row["received_on"]
            )
            if row.get("method"):
                add_result(conn, bid, row["method"], to_float(row["purity_pct"]),
                           row["tested_on"], row["lab"])
            imported += 1
    print(f"Imported {imported} rows")

if __name__ == "__main__":
    import_csv(sys.argv[1])

Run it with python import_csv.py catalog.csv. Because batch numbers are checked before inserting, you can re-run the import without creating duplicate batches.

4. Ask real questions with SQL

This is where a database beats a spreadsheet.

Batches below a purity threshold:

def low_purity(conn, threshold=98.0):
    return conn.execute("""
        SELECT p.name, b.batch_number, r.method, r.purity_pct
        FROM test_results r
        JOIN batches b  ON b.id = r.batch_id
        JOIN peptides p ON p.id = b.peptide_id
        WHERE r.purity_pct < ?
        ORDER BY r.purity_pct
    """, (threshold,)).fetchall()

Latest batch for every peptide:

def latest_batches(conn):
    return conn.execute("""
        SELECT p.name, b.batch_number, MAX(b.received_on) AS received_on
        FROM peptides p
        JOIN batches b ON b.peptide_id = p.id
        GROUP BY p.id
        ORDER BY p.name
    """).fetchall()

(SQLite has a handy rule: when you use MAX() in a grouped query, the other bare columns come from the same row as the max value.)

Average HPLC purity per peptide:

def avg_purity(conn):
    return conn.execute("""
        SELECT p.name, ROUND(AVG(r.purity_pct), 2) AS avg_purity, COUNT(*) AS tests
        FROM test_results r
        JOIN batches b  ON b.id = r.batch_id
        JOIN peptides p ON p.id = b.peptide_id
        WHERE r.method = 'HPLC'
        GROUP BY p.id
        ORDER BY avg_purity DESC
    """).fetchall()

5. A tiny search CLI

# search.py
import argparse
from schema import get_connection

def main():
    parser = argparse.ArgumentParser(description="Search the peptide database")
    parser.add_argument("term", help="Part of a peptide name or batch number")
    args = parser.parse_args()

    like = f"%{args.term}%"
    with get_connection() as conn:
        rows = conn.execute("""
            SELECT p.name, b.batch_number, b.received_on, r.method, r.purity_pct
            FROM peptides p
            LEFT JOIN batches b      ON b.peptide_id = p.id
            LEFT JOIN test_results r ON r.batch_id = b.id
            WHERE p.name LIKE ? OR b.batch_number LIKE ?
            ORDER BY p.name, b.received_on DESC
        """, (like, like)).fetchall()

    for r in rows:
        print(f"{r['name']:<15} {r['batch_number'] or '-':<15} "
              f"{r['received_on'] or '-':<12} {r['method'] or '-':<6} {r['purity_pct'] or '-'}")

if __name__ == "__main__":
    main()

$ python search.py bpc
BPC-157         BPC-2026-001    2026-10-01   HPLC   99.1

6. Where to get real data from

The quality of this database depends entirely on the data you put in. The most reliable source is the supplier's own documentation: product pages with sequence, molecular weight and storage details, plus batch-specific COAs with HPLC/MS results. Suppliers such as Orion Peptides and Neuro Peptides publish product specifications you can record alongside your own batch numbers and lab results.

A good habit: store the COA file path or URL in a column on test_results, so every purity number can be traced back to its source document.

Next steps

Add a coa_url column and a small script that flags batches without a COA.
Wrap the queries in a FastAPI or Flask endpoint to make an internal API.
Use pandas.read_sql_query() to pull results straight into a DataFrame for charts.
Schedule a nightly backup with conn.backup() — SQLite supports online backups natively.

SQLite is one of those tools that is easy to underestimate. For a research catalog with a few thousand rows, it's fast, reliable, and needs zero infrastructure.

All compounds referenced are intended for laboratory research use only.

DEV Community: Faiz Rasool