SQLite Database Manager (python, written by Claude Code)

Claude Code wrote this python project from the task below. It installed and ran on a clean Ubuntu 22.04 machine as written.

Task given to the agent:

TASK: SQLite Database Manager

Write a program that provides a command-line interface for managing SQLite databases, supporting table creation, CRUD operations, schema inspection, data import/export, and query execution.

FUNCTIONAL REQUIREMENTS:
- Accept a database file path as a command-line argument (create it if it does not exist)
- Support creating tables with typed columns (TEXT, INTEGER, REAL, BLOB) and constraints (PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT)
- Support INSERT, SELECT, UPDATE, and DELETE operations via command-line subcommands
- Support executing arbitrary SQL queries via a --query flag
- Implement schema inspection: list all tables, show column details for a given table, show row counts
- Import data from a CSV file into a specified table via --import flag, auto-detecting column types
- Export a table or query result to CSV via --export flag
- Support basic transactions: --begin, --commit, --rollback flags for grouping operations
- Print query results as formatted tables to console with column headers and aligned values
- If no database path is given, create a sample database with at least three related tables (e.g., users, orders, products), populate with sample data, and run demonstration queries showing joins, aggregations, and filtering
- Handle database errors gracefully with clear messages distinguishing connection, schema, constraint, and query errors

Create a complete Python project for a clean Ubuntu 22.04 machine with only Python 3.10+ installed. Include:
- Source code
- requirements.txt with all dependencies (direct and transitive) pinned to exact versions
- README.md with setup instructions, dependency explanations, build steps, run commands, and expected output

[]

#!/usr/bin/env python3
"""SQLite Database Manager CLI - Trial 1 (sqlite3 + tabulate + click)."""

import csv
import io
import os
import sqlite3
import sys

import click
from tabulate import tabulate


def get_connection(db_path):
    """Open a connection to the SQLite database."""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    return conn


def ensure_demo_db(db_path):
    """If the database does not exist, create it with demo data."""
    if os.path.exists(db_path):
        return
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS employees (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            department TEXT NOT NULL,
            salary REAL NOT NULL,
            hire_date TEXT NOT NULL
        )
        """
    )
    demo_rows = [
        ("Alice Johnson", "Engineering", 95000.00, "2021-03-15"),
        ("Bob Smith", "Marketing", 72000.00, "2020-07-01"),
        ("Carol White", "Engineering", 102000.00, "2019-11-20"),
        ("David Brown", "Sales", 68000.00, "2022-01-10"),
        ("Eve Davis", "Marketing", 75000.00, "2021-09-05"),
    ]
    cur.executemany(
        "INSERT INTO employees (name, department, salary, hire_date) VALUES (?, ?, ?, ?)",
        demo_rows,
    )
    conn.commit()
    conn.close()
    click.echo(f"Created demo database at {db_path}")


def print_rows(rows, headers):
    """Pretty-print rows using tabulate."""
    if not rows:
        click.echo("No rows returned.")
        return
    click.echo(tabulate(rows, headers=headers, tablefmt="grid"))
    click.echo(f"\n({len(rows)} row(s))")


@click.group()
@click.option(
    "--db",
    default="sample.db",
    help="Path to the SQLite database file.",
    type=click.Path(),
)
@click.pass_context
def cli(ctx, db):
    """SQLite Database Manager - manage tables, data, and queries."""
    ctx.ensure_object(dict)
    ctx.obj["db"] = db
    ensure_demo_db(db)


@cli.command("create-table")
@click.argument("table_name")
@click.argument("columns", nargs=-1, required=True)
@click.pass_context
def create_table(ctx, table_name, columns):
    """Create a new table. COLUMNS format: name:type, e.g. id:INTEGER name:TEXT."""
    col_defs = []
    for col in columns:
        parts = col.split(":")
        if len(parts) != 2:
            click.echo(f"Error: Invalid column definition '{col}'. Use name:TYPE format.", err=True)
            sys.exit(1)
        col_defs.append(f"{parts[0]} {parts[1]}")
    sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({', '.join(col_defs)})"
    try:
        conn = get_connection(ctx.obj["db"])
        conn.execute(sql)
        conn.commit()
        conn.close()
        click.echo(f"Table '{table_name}' created successfully.")
    except sqlite3.Error as e:
        click.echo(f"SQL Error: {e}", err=True)
        sys.exit(1)


@cli.command("insert")
@click.argument("table_name")
@click.option("--values", "-v", required=True, help="Comma-separated values to insert.")
@click.option("--columns", "-c", default=None, help="Comma-separated column names.")
@click.pass_context
def insert(ctx, table_name, values, columns):
    """Insert a row into a table."""
    val_list = [v.strip() for v in values.split(",")]
    placeholders = ", ".join(["?"] * len(val_list))

    if columns:
        col_list = [c.strip() for c in columns.split(",")]
        sql = f"INSERT INTO {table_name} ({', '.join(col_list)}) VALUES ({placeholders})"
    else:
        sql = f"INSERT INTO {table_name} VALUES ({placeholders})"

    try:
        conn = get_connection(ctx.obj["db"])
        conn.execute(sql, val_list)
        conn.commit()
        conn.close()
        click.echo(f"Row inserted into '{table_name}'.")
    except sqlite3.Error as e:
        click.echo(f"SQL Error: {e}", err=True)
        sys.exit(1)


@cli.command("select")
@click.argument("table_name")
@click.option("--columns", "-c", default="*", help="Comma-separated columns to select.")
@click.option("--where", "-w", default=None, help="WHERE clause (without WHERE keyword).")
@click.option("--order-by", "-o", default=None, help="ORDER BY clause.")
@click.option("--limit", "-l", default=None, type=int, help="Limit number of rows.")
@click.pass_context
def select(ctx, table_name, columns, where, order_by, limit):
    """Select rows from a table."""
    sql = f"SELECT {columns} FROM {table_name}"
    if where:
        sql += f" WHERE {where}"
    if order_by:
        sql += f" ORDER BY {order_by}"
    if limit:
        sql += f" LIMIT {limit}"

    try:
        conn = get_connection(ctx.obj["db"])
        cur = conn.execute(sql)
        rows = cur.fetchall()
        if rows:
            headers = rows[0].keys()
            data = [list(row) for row in rows]
            print_rows(data, headers)
        else:
            click.echo("No rows returned.")
        conn.close()
    except sqlite3.Error as e:
        click.echo(f"SQL Error: {e}", err=True)
        sys.exit(1)


@cli.command("update")
@click.argument("table_name")
@click.option("--set", "set_clause", required=True, help="SET clause, e.g. 'name=Alice,salary=100000'.")
@click.option("--where", "-w", required=True, help="WHERE clause (without WHERE keyword).")
@click.pass_context
def update(ctx, table_name, set_clause, where):
    """Update rows in a table."""
    sql = f"UPDATE {table_name} SET {set_clause} WHERE {where}"
    try:
        conn = get_connection(ctx.obj["db"])
        cur = conn.execute(sql)
        conn.commit()
        click.echo(f"{cur.rowcount} row(s) updated in '{table_name}'.")
        conn.close()
    except sqlite3.Error as e:
        click.echo(f"SQL Error: {e}", err=True)
        sys.exit(1)


@cli.command("delete")
@click.argument("table_name")
@click.option("--where", "-w", required=True, help="WHERE clause (without WHERE keyword).")
@click.pass_context
def delete(ctx, table_name, where):
    """Delete rows from a table."""
    sql = f"DELETE FROM {table_name} WHERE {where}"
    try:
        conn = get_connection(ctx.obj["db"])
        cur = conn.execute(sql)
        conn.commit()
        click.echo(f"{cur.rowcount} row(s) deleted from '{table_name}'.")
        conn.close()
    except sqlite3.Error as e:
        click.echo(f"SQL Error: {e}", err=True)
        sys.exit(1)


@cli.command("schema")
@click.argument("table_name", required=False)
@click.pass_context
def schema(ctx, table_name):
    """Inspect database schema. Optionally specify a table name."""
    try:
        conn = get_connection(ctx.obj["db"])
        if table_name:
            cur = conn.execute(f"PRAGMA table_info({table_name})")
            rows = cur.fetchall()
            if not rows:
                click.echo(f"Table '{table_name}' not found.")
                conn.close()
                return
            headers = ["cid", "name", "type", "notnull", "dflt_value", "pk"]
            data = [list(row) for row in rows]
            click.echo(f"\nSchema for table '{table_name}':")
            click.echo(tabulate(data, headers=headers, tablefmt="grid"))
        else:
            cur = conn.execute(
                "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
            )
            tables = cur.fetchall()
            if not tables:
                click.echo("No tables found in the database.")
                conn.close()
                return
            click.echo("\nTables in database:")
            for t in tables:
                click.echo(f"  - {t['name']}")
                info_cur = conn.execute(f"PRAGMA table_info({t['name']})")
                for col in info_cur.fetchall():
                    nullable = "NOT NULL" if col["notnull"] else "NULLABLE"
                    pk = " PRIMARY KEY" if col["pk"] else ""
                    click.echo(f"      {col['name']} {col['type']} {nullable}{pk}")
        conn.close()
    except sqlite3.Error as e:
        click.echo(f"SQL Error: {e}", err=True)
        sys.exit(1)


@cli.command("import-csv")
@click.argument("table_name")
@click.argument("csv_file", type=click.Path(exists=True))
@click.option("--create/--no-create", default=True, help="Auto-create table from CSV headers.")
@click.pass_context
def import_csv(ctx, table_name, csv_file, create):
    """Import data from a CSV file into a table."""
    try:
        with open(csv_file, "r", newline="", encoding="utf-8") as f:
            reader = csv.reader(f)
            headers = next(reader)
            rows = list(reader)

        conn = get_connection(ctx.obj["db"])

        if create:
            col_defs = ", ".join([f"{h.strip()} TEXT" for h in headers])
            conn.execute(f"CREATE TABLE IF NOT EXISTS {table_name} ({col_defs})")

        placeholders = ", ".join(["?"] * len(headers))
        col_names = ", ".join([h.strip() for h in headers])
        sql = f"INSERT INTO {table_name} ({col_names}) VALUES ({placeholders})"

        conn.executemany(sql, rows)
        conn.commit()
        click.echo(f"Imported {len(rows)} row(s) into '{table_name}' from '{csv_file}'.")
        conn.close()
    except (sqlite3.Error, csv.Error, IOError) as e:
        click.echo(f"Error: {e}", err=True)
        sys.exit(1)


@cli.command("export-csv")
@click.argument("table_name")
@click.argument("csv_file", type=click.Path())
@click.pass_context
def export_csv(ctx, table_name, csv_file):
    """Export a table to a CSV file."""
    try:
        conn = get_connection(ctx.obj["db"])
        cur = conn.execute(f"SELECT * FROM {table_name}")
        rows = cur.fetchall()
        if not rows:
            click.echo(f"No data found in table '{table_name}'.")
            conn.close()
            return

        headers = rows[0].keys()
        with open(csv_file, "w", newline="", encoding="utf-8") as f:
            writer = csv.writer(f)
            writer.writerow(headers)
            for row in rows:
                writer.writerow(list(row))

        click.echo(f"Exported {len(rows)} row(s) from '{table_name}' to '{csv_file}'.")
        conn.close()
    except (sqlite3.Error, IOError) as e:
        click.echo(f"Error: {e}", err=True)
        sys.exit(1)


@cli.command("query")
@click.argument("sql")
@click.option("--params", "-p", default=None, help="Comma-separated parameters for parameterized query.")
@click.pass_context
def query(ctx, sql, params):
    """Execute a raw SQL query."""
    param_list = []
    if params:
        param_list = [p.strip() for p in params.split(",")]

    try:
        conn = get_connection(ctx.obj["db"])
        cur = conn.execute(sql, param_list)

        if sql.strip().upper().startswith("SELECT") or sql.strip().upper().startswith("PRAGMA"):
            rows = cur.fetchall()
            if rows:
                headers = rows[0].keys()
                data = [list(row) for row in rows]
                print_rows(data, headers)
            else:
                click.echo("No rows returned.")
        else:
            conn.commit()
            click.echo(f"Query executed successfully. {cur.rowcount} row(s) affected.")
        conn.close()
    except sqlite3.Error as e:
        click.echo(f"SQL Error: {e}", err=True)
        sys.exit(1)


if __name__ == "__main__":
    cli()

# SQLite Database Manager - Python Trial 1

CLI for managing SQLite databases using sqlite3 (stdlib), tabulate, and click.

## Dependencies

- Python 3.8+
- click 8.1.7
- tabulate 0.9.0

## Installation

```bash
pip install -r requirements.txt
```

## Usage

```bash
# Show help
python db_manager.py --help

# Create a table
python db_manager.py create-table users id:INTEGER name:TEXT email:TEXT

# Insert a row
python db_manager.py insert users -c "name,email" -v "Alice,alice@example.com"

# Select rows
python db_manager.py select users
python db_manager.py select users -w "name='Alice'" -o "name ASC" -l 10

# Update rows
python db_manager.py update users --set "email='new@example.com'" -w "name='Alice'"

# Delete rows
python db_manager.py delete users -w "name='Alice'"

# Inspect schema
python db_manager.py schema
python db_manager.py schema users

# Import CSV
python db_manager.py import-csv users data.csv

# Export CSV
python db_manager.py export-csv users output.csv

# Execute raw SQL
python db_manager.py query "SELECT * FROM employees WHERE salary > ?" -p "80000"

# Use a specific database
python db_manager.py --db mydata.db select employees
```

If no database is specified, a `sample.db` file with demo employee data is created automatically.

click==8.1.7
tabulate==0.9.0