RMIS Database Direct Script

A minimalistic, menu-driven interface for PostgreSQL database operations without Django ORM. This tool provides safe and user-friendly access to common database tasks including data export, import, backup, and bulk operations.

Quick Start

1. Setup & Install:

   python setup.py

2. Test Connection:

   python test_connection.py

   Note: To setup database sudo -u postgres psql. Then CREATE USER username WITH PASSWORD 'pasword';

3. Run Application:

   python main.py
   # or with environment check
   python start.py

That's it! The setup script will guide you through configuration and dependency installation.

Features

📊 Data Operations

• Data Statistics: Get comprehensive database and table statistics
• Metadata Generation: Export detailed database schema information
• Database Backup: Create full or partial database backups

📤 Export Operations

• Plain CSV Export: Export tables with ID references only
• Joined CSV Export: Export with related table data joined
• Selected Columns Export: Export specific columns only
• Filtered Exports: Export with category, date range, or combined filters

📥 Import Operations

• Bulk Import: Import data from CSV with column mapping
• Data Validation: Validate data before import
• Update Existing: Option to update existing records

🔧 Maintenance Operations

• Bulk Update: Update multiple records with filters
• Bulk Delete: Delete records with mandatory safety filters
• Transaction Safety: All operations use database transactions

Safety Features

• ⚠️ Critical Operation Warnings: Clear alerts for destructive operations
• 🔒 Mandatory Confirmations: Required user confirmation for critical operations
• 📝 Operation Summaries: Detailed preview before execution
• 🔄 Transaction Rollback: Automatic rollback on errors
• 📊 Impact Estimation: Preview of affected rows before execution

Installation

Prerequisites

• Python 3.7 or higher
• PostgreSQL database
• PostgreSQL client tools (pg_dump, pg_restore) for backup operations

Automated Setup (Recommended)

Run the setup script for guided installation:

python setup.py

This will:

• Check Python version and install dependencies
• Create required directories
• Set up configuration with your database settings
• Test the database connection
• Check for PostgreSQL tools

Manual Setup

1. Clone or download the project:

   git clone <repository-url>
   cd rmis-database-direct-script

2. Install Python dependencies:

   pip install -r requirements.txt

3. Configure database connection:

   # Copy and edit the configuration file
   cp config.json.example config.json

4. Update config.json with your database settings:

   {
     "database": {
       "host": "localhost",
       "port": 5432,
       "database": "your_database_name",
       "username": "your_username",
       "password": "your_password"
     }
   }

5. Create required directories (auto-created on first run):

   mkdir -p logs backups exports imports

Usage

Starting the Application

Choose one of these options:

# Simple launcher
python main.py

# With environment checks and setup assistance
python start.py

# Minimal launcher
python run.py

Main Menu

The application presents a numbered menu of available services:

📋 Available Services:
----------------------------------------
 1.   Get Current Data Stats
 2.   Generate Database Metadata
 3.   Create Database Backup
 4.   Export Table to CSV (Plain)
 5.   Export Table to CSV (With Joins)
 6.   Export Table to CSV (Selected Columns)
 7.   Export Table to CSV (Category Filter)
 8.   Export Table to CSV (Date Range)
 9.   Export Table to CSV (Combined Filters)
10. ⚠️ Bulk Update Table Values
11. ⚠️ Bulk Import Data from CSV
12. ⚠️ Bulk Delete Records
13.   🚪 Exit
----------------------------------------

Operation Flow

1. Select Service: Choose from the numbered menu
2. Configure Parameters: Follow prompts to configure the operation
3. Review Summary: Review the operation details and impact
4. Confirm Execution: Approve the operation (required for critical operations)
5. View Results: See the operation results and any output files

Example Workflows

Exporting Table Data

1. Select "Export Table to CSV (Plain)"
2. Choose the table to export
3. Select columns (optional)
4. Configure CSV format options
5. Review and confirm
6. Find exported file in exports/ directory

Bulk Import from CSV

1. Place CSV file in imports/ directory
2. Select "Bulk Import Data from CSV"
3. Choose target table
4. Map CSV columns to database columns
5. Configure import options
6. Review impact and confirm
7. Monitor import progress

Creating Database Backup

1. Select "Create Database Backup"
2. Choose backup type (full, schema-only, etc.)
3. Select backup location
4. Configure compression options
5. Review and confirm
6. Find backup file in backups/ directory

Configuration

Database Settings

{
  "database": {
    "host": "localhost",
    "port": 5432,
    "database": "database_name",
    "username": "username",
    "password": "password",
    "connection_timeout": 30,
    "max_connections": 5
  }
}

Application Settings

{
  "application": {
    "log_level": "INFO",
    "log_file": "logs/rmis_db_script.log",
    "backup_directory": "backups",
    "export_directory": "exports",
    "import_directory": "imports"
  }
}

Safety Settings

{
  "safety": {
    "require_confirmation_for_critical_ops": true,
    "max_bulk_operation_size": 10000,
    "enable_transaction_rollback": true,
    "backup_before_critical_ops": true
  }
}

Directory Structure

rmis-database-direct-script/
├── main.py                          # Main application entry point
├── config.json                      # Database and application configuration
├── requirements.txt                 # Python dependencies
├── README.md                        # This file
├── utils/                          # Utility modules
│   ├── __init__.py
│   ├── config.py                   # Configuration management
│   ├── db_connection.py            # Database connection handling
│   └── logger.py                   # Logging utilities
├── services/                       # Service modules
│   ├── __init__.py                 # Service manager
│   ├── data_stats_service.py       # Database statistics
│   ├── metadata_service.py         # Metadata generation
│   ├── backup_service.py           # Database backup
│   ├── csv_export_service.py       # CSV export operations
│   └── bulk_operations_service.py  # Bulk operations
├── logs/                           # Application logs
├── backups/                        # Database backups
├── exports/                        # Exported CSV files
└── imports/                        # CSV files for import

Error Handling

• Database Connection Errors: Clear error messages with connection troubleshooting
• SQL Errors: Detailed error reporting with query context
• File Operations: Automatic directory creation and permission checks
• Data Validation: Pre-execution validation with clear error messages
• Transaction Failures: Automatic rollback with error reporting

Logging

All operations are logged to logs/rmis_db_script.log with:

• Operation start/end times
• User actions and parameter selections
• Query execution details
• Error messages and stack traces
• File operations (exports, imports, backups)

Security Considerations

• No Hardcoded Credentials: All credentials stored in config files
• Parameter Sanitization: SQL injection protection through parameterized queries
• Transaction Safety: All operations wrapped in transactions
• Confirmation Requirements: Critical operations require explicit confirmation
• Audit Trail: Complete logging of all operations

Troubleshooting

Common Issues

1. Database Connection Failed

   • Check database server is running
   • Verify host, port, database name in config.json
   • Check username/password credentials
   • Test network connectivity

2. Permission Denied Errors

   • Ensure database user has required permissions
   • Check file system permissions for backup/export directories
   • Verify PostgreSQL client tools are installed

3. Import/Export Errors

   • Check CSV file format and encoding
   • Verify column mappings are correct
   • Ensure target table exists and has correct structure

4. Memory Issues with Large Operations

   • Reduce batch sizes for bulk operations
   • Use row limits for large exports
   • Monitor available memory during operations

Getting Help

1. Check the log files in logs/ directory
2. Review error messages for specific guidance
3. Verify configuration settings
4. Test database connectivity independently

Contributing

This tool is designed to be minimal and focused. When extending:

1. Follow the existing service pattern in services/ directory
2. Implement proper error handling and logging
3. Add appropriate safety checks for destructive operations
4. Update this README with new features

License

[Add your license information here]

Version History

• v1.0.0: Initial release with core functionality
  • Database statistics and metadata generation
  • CSV export with various filtering options
  • Database backup functionality
  • Bulk operations (update, import, delete) with safety checks