Command-Line CRUD Project¶

This assignment guides you through building a simple command-line application that manages records in a PostgreSQL database. By the end, you'll be able to create, read, update, and delete entries from a terminal interface.

Prerequisites¶

Python 3.8+ installed on your system.
PostgreSQL installed locally with a user that has permission to create databases and tables.
Basic familiarity with the terminal and running Python scripts.

Project Setup¶

Create a new directory for your project and initialize a Git repository.

Set up a Python virtual environment:

python3 -m venv venv
source venv/bin/activate

Install dependencies using pip:
```
pip install psycopg2-binary tabulate
```
psycopg2-binary provides the PostgreSQL driver.
tabulate will help format table output in the CLI.
Create a database for the project:
```
createdb crud_app
```

Git Workflow¶

Use feature branches to keep main stable. Create a new branch for each feature or fix and merge back with a pull request when the work is complete.

# create and switch to a branch for adding list functionality
git checkout -b feature/list-contacts
# commit changes with clear messages
git commit -am "Add list command"
# push and open a pull request when ready
git push -u origin feature/list-contacts

Merging should happen only after code review or testing to maintain a clean history.

Database Schema¶

For this exercise you'll build a contacts table. Use the SQL below to create it:

CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name  VARCHAR(50) NOT NULL,
    email      VARCHAR(100) UNIQUE NOT NULL,
    phone      VARCHAR(20)
);

Schema Diagram¶

classDiagram
    class contacts {
        int id
        string first_name
        string last_name
        string email
        string phone
    }

Application Structure¶

Your project should contain the following files:

crud_app/
├── db.py           # Database connection helpers
├── models.py       # Functions for CRUD operations
├── main.py         # CLI entry point
└── README.md       # Setup and usage instructions

1. Database Connection (`db.py`)¶

Write a helper that returns a connection object using credentials from environment variables:

import os
import psycopg2

DB_NAME = os.getenv("DB_NAME", "crud_app")
DB_USER = os.getenv("DB_USER", "postgres")
DB_PASSWORD = os.getenv("DB_PASSWORD", "")
DB_HOST = os.getenv("DB_HOST", "localhost")

def get_connection():
    return psycopg2.connect(
        dbname=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD,
        host=DB_HOST,
    )

2. CRUD Functions (`models.py`)¶

Implement a function for each database operation. Example for creating a contact:

from db import get_connection

def create_contact(first_name: str, last_name: str, email: str, phone: str = None):
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute(
                """INSERT INTO contacts (first_name, last_name, email, phone)
                VALUES (%s, %s, %s, %s) RETURNING id""",
                (first_name, last_name, email, phone),
            )
            new_id = cur.fetchone()[0]
    return new_id

Repeat similar functions for reading, updating, and deleting contacts.

3. Command-Line Interface (`main.py`)¶

Use a loop to display a menu and route to the appropriate function:

from models import create_contact, list_contacts, update_contact, delete_contact
from tabulate import tabulate

MENU = """
1. Create Contact
2. List Contacts
3. Update Contact
4. Delete Contact
5. Quit
"""

def main():
    while True:
        print(MENU)
        choice = input("Select an option: ")
        if choice == "1":
            first = input("First name: ")
            last = input("Last name: ")
            email = input("Email: ")
            phone = input("Phone (optional): ")
            new_id = create_contact(first, last, email, phone)
            print(f"Created contact with id {new_id}")
        elif choice == "2":
            contacts = list_contacts()
            print(tabulate(contacts, headers=["ID", "First", "Last", "Email", "Phone"]))
        elif choice == "3":
            contact_id = input("ID to update: ")
            email = input("New email: ")
            phone = input("New phone: ")
            update_contact(contact_id, email, phone)
        elif choice == "4":
            contact_id = input("ID to delete: ")
            delete_contact(contact_id)
        elif choice == "5":
            break
        else:
            print("Invalid option")

if __name__ == "__main__":
    main()

Running the App¶

Ensure PostgreSQL is running and the crud_app database exists.
Activate your virtual environment: source venv/bin/activate.
Run database migrations or the SQL script to create the contacts table.
Execute the CLI: python main.py.

Testing¶

Manually test each menu option to verify that records are created, listed, updated, and deleted as expected.
Optionally write automated tests using unittest or pytest for each function in models.py.

Deliverables¶

Source code in a Git repository with a clear commit history.
A README.md explaining how to set up the environment and run the application.
(Optional) Screenshot or short screen recording demonstrating the CRUD workflow.

Sample Project Ideas¶

Task Tracker – Manage to‑do items with deadlines and status fields.
Bird Log – Record bird sightings with date, location, and notes.
Recipe Catalog – Store ingredients and instructions for favorite meals.
Book Library – Track books read with ratings and short reviews.

Stretch Goals¶

Add input validation and error handling for edge cases.
Use environment variables or a .env file to configure database credentials.
Explore using SQLAlchemy or another ORM for database access.
Package the project so it can be installed with pip.