remove_compound.py

import sqlite3
import os
import argparse

# Configuration
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DATABASE_PATH = os.path.join(BASE_DIR, 'database.db')

def remove_compound(name: str):
    """
    Removes a compound from the database by its common or IUPAC name.
    The search is case-insensitive.
    """
    if not os.path.exists(DATABASE_PATH):
        print(f"Error: Database file not found at {DATABASE_PATH}")
        return

    conn = None
    try:
        conn = sqlite3.connect(DATABASE_PATH)
        cursor = conn.cursor()

        # Find matching compounds before deleting to provide feedback
        # Using COLLATE NOCASE for case-insensitive comparison, as used elsewhere in the app
        find_sql = "SELECT compound_id, common_name, iupac_name FROM compounds WHERE common_name = ? COLLATE NOCASE OR iupac_name = ? COLLATE NOCASE"
        cursor.execute(find_sql, (name, name))
        matches = cursor.fetchall()

        if not matches:
            print(f"No compound matching '{name}' found in the database.")
            return

        print(f"Found {len(matches)} record(s) matching '{name}':")
        for match in matches:
            print(f"  - ID: {match[0]}, Common Name: {match[1]}, IUPAC Name: {match[2]}")

        # Delete the records
        delete_sql = "DELETE FROM compounds WHERE common_name = ? COLLATE NOCASE OR iupac_name = ? COLLATE NOCASE"
        cursor.execute(delete_sql, (name, name))
        deleted_count = cursor.rowcount
        conn.commit()

        print(f"\nSuccessfully deleted {deleted_count} record(s) for '{name}'.")

    except sqlite3.Error as e:
        print(f"A database error occurred: {e}")
        if conn:
            conn.rollback()
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    parser = argparse.ArgumentParser(
        description="Remove a compound from the database by name.",
        epilog="Example usage:\n  python remove_compound.py \"Polymyxin E\""
    )
    parser.add_argument("name", type=str, help="The common or IUPAC name of the compound to remove.")
    args = parser.parse_args()

    remove_compound(args.name)