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.