Build SQLite Contacts App in Python: A Hands-On Guide

What we’re building

A small command-line contacts manager written in Python, backed by a single SQLite file. The sqlite3 module is part of the standard library, so there is nothing to install. You end up with one script that lets you add, list, search, update, and delete contacts from your terminal, with all data stored in a real SQL table that persists between runs.

This guide is project-focused: we build SQLite contacts first and pick up the API along the way. If you want a deeper tour of the sqlite3 API itself before starting, see the official sqlite3 module reference or our SQLite guide.

TL;DR

You will write a single-file Python program (roughly 150 lines) that opens a SQLite file with sqlite3.connect, sets row_factory = sqlite3.Row, creates a contacts table with email TEXT UNIQUE, and exposes seven CLI subcommands (add, list, show, search, update, delete, count) through argparse. Every value is bound through ? placeholders, duplicate emails are caught with sqlite3.IntegrityError, and every database call goes through contextlib.closing because with conn: only manages transactions, not the file handle. The full script is at the bottom of this article.

Setting up the project

Create a folder, drop a single Python file in it, and you are done. The SQLite database file gets created on the first run, so there is nothing else to ship. Keeping the layout flat also makes the script easy to copy into a different project later if you decide you want to reuse the contacts table.

contacts_app/
├── contacts.py
└── contacts.db      # appears after the first run

Put these imports at the top of contacts.py. The argparse import drives the CLI, sqlite3 is the database driver, closing from contextlib lets the connection actually close when a with block exits, and Path keeps the database file next to the script regardless of the working directory you invoke it from:

import argparse
import sqlite3
from contextlib import closing
from pathlib import Path

DB_PATH = Path(__file__).with_name("contacts.db")

Path(__file__).with_name("contacts.db") is more reliable than a hard-coded "contacts.db" because it keeps the database file next to the script no matter where you invoke it from, which avoids surprises when you cd into the folder and run the script from there.

Designing the contacts table

A contacts app only needs one table. Stick the schema in a constant so it is easy to tweak later, and let SQLite handle the duplicate-email check for you instead of writing the comparison by hand in Python:

CREATE TABLE IF NOT EXISTS contacts (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT    NOT NULL,
    email      TEXT    UNIQUE,
    phone      TEXT,
    notes      TEXT,
    created_at TEXT    NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_contacts_name ON contacts(name);

A few choices worth understanding before you start typing INSERT:

• INTEGER PRIMARY KEY AUTOINCREMENT makes id an alias for the SQLite rowid and guarantees ids never get reused after a delete. For most apps the plain rowid would be fine, but in a contacts list you do not want a recycled id pointing at a different person.
• email TEXT UNIQUE lets SQLite reject duplicates with an IntegrityError. Catching one exception is much cleaner than a separate SELECT followed by a Python-level compare on the result.
• datetime('now') is a SQLite function that returns the current UTC time as a string in YYYY-MM-DD HH:MM:SS form. No Python call needed; SQLite stores it as text and you can sort by it directly.
• The index on name keeps LIKE 'ada%' searches cheap once the table grows. Without it SQLite still works. It just falls back to a full scan, which gets slow after a few thousand rows.

If you want the full grammar for every option on this table (column constraints, default expressions, index types), the official CREATE TABLE reference is the most complete source.

How do you open a connection safely?

Most sqlite3 bugs come from how the connection is opened or closed, not from the SQL itself. Hide that in one helper, and every other function in the program becomes a one-liner that grabs a connection, runs a query, and lets the with block tear it down. The helper below is reused by every other function in this guide:

SCHEMA = """
CREATE TABLE IF NOT EXISTS contacts (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT    NOT NULL,
    email      TEXT    UNIQUE,
    phone      TEXT,
    notes      TEXT,
    created_at TEXT    NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_contacts_name ON contacts(name);
"""

def get_connection() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn

def init_db() -> None:
    with closing(get_connection()) as conn:
        conn.executescript(SCHEMA)

Two non-obvious things are happening here, and both will bite you if you skip them.

First, conn.row_factory = sqlite3.Row lets you read columns by name later (row["email"]) while still treating each row as a tuple. Row is per-connection, which is why it lives inside get_connection() rather than at module top level. A fresh connection from sqlite3.connect() resets it back to the default tuple-of-columns behaviour.

Second, with sqlite3.connect(...) as conn: does not close the file when the block exits. It commits on success and rolls back on exception, so it manages the transaction, not the file handle. To actually close the connection, you either call conn.close() yourself or wrap the connection in contextlib.closing(...). On Windows the file stays locked otherwise, which trips up anyone who tries to reopen it from another process. For a deeper look at the with statement and the context manager protocol, the context managers guide walks through it step by step.

How to add a contact

The first real CRUD function. Always bind values through placeholders, never build SQL with f-strings, even for things that “feel safe” like ids. The standard sqlite3 placeholder is ?:

def add_contact(name, email, phone, notes):
    with closing(get_connection()) as conn:
        cur = conn.execute(
            "INSERT INTO contacts (name, email, phone, notes) VALUES (?, ?, ?, ?)",
            (name, email, phone, notes),
        )
        return cur.lastrowid

The ? is the qmark placeholder, which is the standard style for sqlite3 (sqlite3.paramstyle == "qmark"). cur.lastrowid is the new row’s id, which is handy for printing confirmation back to the user when the command finishes:

new_id = add_contact("Ada Lovelace", "ada@example.com", None, None)
print(f"Inserted contact {new_id}")
# output: Inserted contact 1

If the email is already taken, SQLite raises sqlite3.IntegrityError. Catch it where it makes sense for your CLI (typically in the command handler rather than in the database layer), so the database functions stay reusable from a different entry point later. The error handling guide covers patterns like “wrap the call, print the message, exit cleanly” in more depth.

How do you list and search contacts?

For a list view, the Row factory pays off. Set it once in get_connection() and every query that follows gives you column-name access for free, which is what makes the pretty-printed output below possible without a separate mapping step:

def list_contacts():
    with closing(get_connection()) as conn:
        return conn.execute(
            "SELECT id, name, email, phone FROM contacts ORDER BY name"
        ).fetchall()

for row in list_contacts():
    print(f"{row['id']:>3}  {row['name']:<20}  {row['email']}")
# output:
#   1  Ada Lovelace         ada@example.com
#   2  Grace Hopper         grace@example.com

Search is just LIKE with wildcards, but keep the wildcards in Python and the value as a placeholder. SQLite still treats the bound value as a literal string when it interprets LIKE, so this stays safe and avoids any chance of injection through the search term:

def search_contacts(term):
    pattern = f"%{term}%"
    with closing(get_connection()) as conn:
        return conn.execute(
            "SELECT id, name, email, phone "
            "FROM contacts "
            "WHERE name LIKE ? OR email LIKE ? "
            "ORDER BY name",
            (pattern, pattern),
        ).fetchall()

The % wrapping happens in Python, not in the SQL string. If you move the wildcards into the SQL itself ("WHERE name LIKE '%' || ? || '%'") the value still binds safely, but the pattern is harder to read and easier to break later if someone refactors the query.

A quick note on case sensitivity: SQLite’s default LIKE is case-insensitive for ASCII characters only. For real Unicode case-insensitivity, use LOWER(name) LIKE LOWER(?) or compile SQLite with the ICU extension. For a personal contacts list with English names the default is fine. The SQLite LIKE docs spell out the exact rules if you need them.

Updating and deleting contacts

A partial update, where the user can change just the phone or just the email and leave everything else alone, is a common pattern in CRUD apps. Build the SET clause from a dict of fields the caller actually passed, and skip the round trip entirely if nothing was supplied:

def update_contact(contact_id, **fields):
    if not fields:
        return 0
    columns = ", ".join(f"{key} = ?" for key in fields)
    params = list(fields.values()) + [contact_id]
    with closing(get_connection()) as conn:
        cur = conn.execute(
            f"UPDATE contacts SET {columns} WHERE id = ?",
            params,
        )
        return cur.rowcount

The column names are interpolated into the SQL string, so they must come from a hard-coded allowlist, in practice from your argparse dest names or a fixed mapping. Never pass **request.args straight from a web request into this function; treat it as a SQL-injection hole the moment you do.

Delete is shorter and tells you whether anything actually happened, instead of raising for a missing row:

def delete_contact(contact_id):
    with closing(get_connection()) as conn:
        cur = conn.execute("DELETE FROM contacts WHERE id = ?", (contact_id,))
        return cur.rowcount > 0

cur.rowcount is the number of rows the last DML statement touched. If you call delete_contact(999) against an empty database you get False back instead of an exception, and your CLI can print a friendly “no such contact” message instead of crashing.

Build SQLite contacts CLI with argparse

The full script pairs each database function with an argparse subcommand. The argparse side is the boring part, so here is just the wiring; the database functions above stay exactly as written. Splitting the code into two pieces also makes it easier to test, since you can build the parser and inspect args without touching sys.argv:

def get_contact(contact_id):
    with closing(get_connection()) as conn:
        return conn.execute(
            "SELECT id, name, email, phone, notes FROM contacts WHERE id = ?",
            (contact_id,),
        ).fetchone()

def build_parser() -> argparse.ArgumentParser:
    parser = argparse.ArgumentParser(description="Manage contacts in SQLite.")
    sub = parser.add_subparsers(dest="command", required=True)

    add = sub.add_parser("add")
    add.add_argument("--name", required=True)
    add.add_argument("--email")
    add.add_argument("--phone")
    add.add_argument("--notes")

    sub.add_parser("list")
    show = sub.add_parser("show")
    show.add_argument("contact_id", type=int)

    search = sub.add_parser("search")
    search.add_argument("term")

    update = sub.add_parser("update")
    update.add_argument("contact_id", type=int)
    update.add_argument("--name")
    update.add_argument("--email")
    update.add_argument("--phone")
    update.add_argument("--notes")

    delete = sub.add_parser("delete")
    delete.add_argument("contact_id", type=int)
    return parser

The dispatch block is a flat if/elif chain because the seven commands share no real structure beyond “match a string and call a function”. A dict of command-to-handler mappings is the only refactor that pays off once the program grows beyond roughly a dozen commands, which is well past the size of this contacts app. Keeping the dispatch readable matters more than DRY-ing it up at this scale:

def main(argv=None):
    parser = build_parser()
    args = parser.parse_args(argv)
    init_db()

    if args.command == "add":
        new_id = add_contact(args.name, args.email, args.phone, args.notes)
        print(f"Inserted contact {new_id}")
    elif args.command == "list":
        for row in list_contacts():
            print(row["id"], row["name"], row["email"])
    elif args.command == "show":
        row = get_contact(args.contact_id)
        if row is None:
            print("No such contact")
        else:
            print(dict(row))
    elif args.command == "search":
        for row in search_contacts(args.term):
            print(row["id"], row["name"], row["email"])
    elif args.command == "update":
        fields = {k: v for k, v in vars(args).items()
                  if k in {"name", "email", "phone", "notes"} and v is not None}
        changed = update_contact(args.contact_id, **fields)
        print(f"Updated {changed} row(s)")
    elif args.command == "delete":
        print("Deleted" if delete_contact(args.contact_id) else "No such contact")

if __name__ == "__main__":
    main()

A quick smoke test confirms everything wires up correctly: insert two rows, list them, search for one, update its phone, then delete the other. Each call goes through the same with closing(get_connection()) pattern, so there are no resource leaks even if any single command raises:

$ python contacts.py add --name "Ada Lovelace" --email ada@example.com
Inserted contact 1
$ python contacts.py add --name "Grace Hopper" --email grace@example.com
Inserted contact 2
$ python contacts.py list
1 Ada Lovelace ada@example.com
2 Grace Hopper grace@example.com
$ python contacts.py search ada
1 Ada Lovelace ada@example.com
$ python contacts.py update 1 --phone 555-0199
Updated 1 row(s)
$ python contacts.py delete 2
Deleted

If you want a fuller argparse walkthrough, the argparse guide covers subparsers, choices, and BooleanOptionalAction in detail. Once the basics feel small, the python-click-guide and typer-cli libraries cut the boilerplate further by replacing the whole build_parser block with decorators.

Common mistakes

Five pitfalls that catch almost everyone on their first sqlite3 project, with the fix next to each one:

1. Expecting with conn: to close the file. It does not. Use closing(...) or call conn.close() explicitly. Without one of those, on Windows the file stays locked and you cannot reopen it from another process.
2. Forgetting that DML outside a transaction context can be lost. With the default LEGACY_TRANSACTION_CONTROL mode, the with block commits on exit, so writes survive; if you skip the with and forget commit(), the row vanishes on disconnect.
3. Building SQL with f-strings for values. Always use ? or :name placeholders, even for ids. SQLite has no native parameter type for column names, so identifiers still need an allowlist, but values never do.
4. Setting row_factory once and expecting it to persist. It is per-connection, so set it inside your get_connection() helper rather than at module top level.
5. Comparing Python datetime objects directly to SQLite date strings. SQLite stores dates as text; convert with datetime.fromisoformat(row["created_at"]) if you need real datetime math.

For a deeper look at the sqlite3 module (including exception types, the autocommit flag, and the new PEP 249 transaction model), see the sqlite3 module reference and our sqlite3 module page.

Frequently asked questions

Do I need to install anything to use sqlite3 in Python?

No. sqlite3 ships with the Python standard library, so any CPython 3.x install already has it. On most Linux distributions it is built without the optional SQLITE_ENABLE_FTS5 and ICU extensions, which is fine for a contacts app. You only notice when you reach for full-text search or Unicode-aware LIKE.

Why does my connection stay open after the with block exits?

The connection context manager (with sqlite3.connect(...) as conn:) only manages the transaction, not the file handle. It commits on a clean exit and rolls back on exception, but the file stays open until the Python process closes the connection. Wrap with contextlib.closing(sqlite3.connect(path)) to close the file on exit, or call conn.close() explicitly at the end of the block.

How do I handle duplicate emails in sqlite3?

Add UNIQUE to the column constraint (email TEXT UNIQUE) and let SQLite reject the duplicate with an IntegrityError. Catch it at the boundary that makes sense for your CLI (typically the command handler) and turn it into a user-friendly message. The check happens in the database itself, which is faster and more correct than a separate SELECT ... WHERE email = ? from Python.

Can I store Python datetime objects directly in a column?

Not usefully. SQLite has no native date type and stores everything as text, integer, real, blob, or null. The common pattern is to store ISO 8601 strings (datetime.isoformat() to write, datetime.fromisoformat() to read) or unix timestamps as integers. Sorting and range queries both work on those representations without conversion at query time.

Should I use SQLAlchemy or stick with raw sqlite3?

Stick with raw sqlite3 until the schema or the join complexity starts to hurt. The whole point of the stdlib module is that you avoid a dependency, the schema is small enough to read in one screen, and there is no migration tooling to set up. Once you add a second table with a foreign key or find yourself hand-writing the same boilerplate, SQLAlchemy or SQLModel earn their keep.

Conclusion

You now have a working CLI contacts app built with SQLite and the sqlite3 stdlib module. Every query uses parameterized ? placeholders, the UNIQUE email constraint is handled by catching sqlite3.IntegrityError, and every database call goes through one with closing(get_connection()) as conn: pattern. The full script is roughly 150 lines, runs with no third-party dependencies, and persists its data in a real SQL table.

A few places to take this build SQLite contacts app next:

• Add a groups table with a foreign key to contacts, plus a join for tagging contacts by team or project.
• Switch to the PEP 249 transaction model with autocommit=False and explicit BEGIN / COMMIT once you need finer control over long-running writes.
• Move from raw sqlite3 to SQLAlchemy or SQLModel once the schema starts to grow past a couple of tables.
• Add an FTS5 virtual table for full-text search across notes, which becomes useful once you stop remembering names and start searching on context.

If you would rather start with a smaller CLI project to warm up, the cli-argparse-basics guide walks through a similar argparse pattern in less code. For a pure file-based alternative, the how to write to a file cookbook walks through the JSON path end to end.

See also

• SQLite guide: the API reference tour that pairs with this build SQLite contacts app.
• sqlite3 module reference: the official Python docs for connect, Connection, and Cursor.
• argparse guide: the subparser patterns used in the CLI section.