SQLite Database Manager (python, written by Gemini Code Assist)

Gemini Code Assist wrote this python project from the task below. It does not run 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

[]

# SQLite Database Manager (Python)

A CLI tool for managing SQLite databases, supporting CRUD, CSV import/export, and schema inspection.

## Setup Instructions

1. Ensure Python 3.10+ is installed.
2. Create a virtual environment:
   ```bash
   python3 -m venv venv
   source venv/bin/activate
   ```
3. Install dependencies:
   ```bash
   pip install -r requirements.txt
   ```

## Run Commands

- Execute a custom query:
  ```bash
  python sqlite_manager.py mydata.db --query "SELECT * FROM users WHERE id > 1"
  ```
- List tables:
  ```bash
  python sqlite_manager.py mydata.db --list
  ```
- Inspect a table:
  ```bash
  python sqlite_manager.py mydata.db --inspect users
  ```
- Import CSV:
  ```bash
  python sqlite_manager.py mydata.db --import-csv data.csv target_table
  ```
- Export to CSV:
  ```bash
  python sqlite_manager.py mydata.db --export users users_backup.csv
  ```
- Run with generated sample data:
  ```bash
  python sqlite_manager.py
  ```

## Output
- Results are displayed as formatted tables in the console.
- Export results are saved to CSV.

pandas==2.2.1
tabulate==0.9.0

import sqlite3
import pandas as pd
import sys
import argparse
import os
from typing import List, Optional

def get_connection(db_path: str):
    return sqlite3.connect(db_path)

def execute_query(db_path: str, query: str):
    try:
        with get_connection(db_path) as conn:
            df = pd.read_sql_query(query, conn)
            print(df.to_markdown(index=False))
    except Exception as e:
        print(f"Query Error: {e}")

def list_tables(db_path: str):
    query = "SELECT name FROM sqlite_master WHERE type='table';"
    try:
        with get_connection(db_path) as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            tables = cursor.fetchall()
            print("
Tables:")
            for t in tables:
                print(f" - {t[0]}")
    except Exception as e:
        print(f"Error: {e}")

def inspect_table(db_path: str, table_name: str):
    try:
        with get_connection(db_path) as conn:
            print(f"
Schema for table: {table_name}")
            df_schema = pd.read_sql_query(f"PRAGMA table_info({table_name})", conn)
            print(df_schema.to_markdown(index=False))
            
            count = pd.read_sql_query(f"SELECT COUNT(*) as row_count FROM {table_name}", conn)
            print(f"
Total rows: {count['row_count'][0]}")
    except Exception as e:
        print(f"Error: {e}")

def import_csv(db_path: str, csv_path: str, table_name: str):
    try:
        df = pd.read_csv(csv_path)
        with get_connection(db_path) as conn:
            df.to_sql(table_name, conn, if_exists='append', index=False)
        print(f"Successfully imported {len(df)} rows from {csv_path} into {table_name}")
    except Exception as e:
        print(f"Import Error: {e}")

def export_to_csv(db_path: str, table_or_query: str, output_path: str):
    try:
        with get_connection(db_path) as conn:
            if "SELECT" in table_or_query.upper():
                df = pd.read_sql_query(table_or_query, conn)
            else:
                df = pd.read_sql_query(f"SELECT * FROM {table_or_query}", conn)
            df.to_csv(output_path, index=False)
        print(f"Successfully exported data to {output_path}")
    except Exception as e:
        print(f"Export Error: {e}")

def generate_sample_db(db_path: str):
    print(f"Generating sample database: {db_path}")
    with get_connection(db_path) as conn:
        cursor = conn.cursor()
        conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)")
        conn.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)")
        conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, product_id INTEGER, quantity INTEGER)")
        
        conn.execute("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com')")
        conn.execute("INSERT INTO products (name, price) VALUES ('Laptop', 1200.0), ('Mouse', 25.0)")
        conn.execute("INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 1, 1), (1, 2, 2), (2, 2, 5)")
        conn.commit()
    
    print("Running demonstration query (Joins and Aggregation):")
    execute_query(db_path, """
        SELECT u.name, SUM(p.price * o.quantity) as total_spent
        FROM users u
        JOIN orders o ON u.id = o.user_id
        JOIN products p ON o.product_id = p.id
        GROUP BY u.name
    """)

def main():
    parser = argparse.ArgumentParser(description="SQLite Database Manager")
    parser.add_argument("db", nargs="?", help="Database file path")
    parser.add_argument("--query", help="Execute arbitrary SQL")
    parser.add_argument("--list", action="store_true", help="List all tables")
    parser.add_argument("--inspect", help="Show table details")
    parser.add_argument("--import-csv", nargs=2, metavar=('CSV', 'TABLE'), help="Import CSV into table")
    parser.add_argument("--export", nargs=2, metavar=('TABLE_OR_QUERY', 'OUTPUT'), help="Export to CSV")
    
    args = parser.parse_args()
    
    db_path = args.db or "sample.db"
    if not os.path.exists(db_path) and not args.db:
        generate_sample_db(db_path)
    
    if args.query:
        execute_query(db_path, args.query)
    elif args.list:
        list_tables(db_path)
    elif args.inspect:
        inspect_table(db_path, args.inspect)
    elif args.import_csv:
        import_csv(db_path, args.import_csv[0], args.import_csv[1])
    elif args.export:
        export_to_csv(db_path, args.export[0], args.export[1])
    else:
        if args.db:
            list_tables(db_path)

if __name__ == "__main__":
    main()