monitor-app 0.1.9

A simple Flask-based database viewer and manager

Monitor App 🚀

Monitor App is a high-performance data monitoring and management application that automatically generates SQLite databases from CSV data and manages data through both Web UI and REST API.

🎯 Main Use Cases:

• Rapid CSV Data Visualization: Instantly create web apps and APIs by simply placing CSV files
• Quick Frontend for Existing DB Systems: Add Web UI and REST API to existing MySQL/PostgreSQL systems with just configuration changes
• Accelerated Prototype Development: Complete full-featured CRUD applications by just defining data structures

🔧 Key Features:

• Comprehensive REST API: Full CRUD operations (Create, Read, Update, Delete) support
• Automatic API Documentation: FastAPI-like automatic documentation generation with Swagger UI (/docs)
• Separated Architecture: Clear separation between CRUD operation tables and display views
• Flexible View Display: Custom query-based data display (JOIN, aggregation, filtering, etc.), real-time updates (2-second intervals)
• Advanced Styling Features: Conditional cell styling based on values (color coding, font size, alignment)
• CORS Support: Easy integration with frontend applications
• Comprehensive Testing: Automated tests for REST API, Web UI, and all configurations

This project is inspired by Flask and Django, specifically built on Flask as the foundation.
Designed to be accessible for Python beginners and aimed at rapid web app development for manufacturing and data analysis tasks.

📌 Features

• Create new projects with monitor-app startproject
• Easy conversion of CSV data to SQLite
• Display and edit data through Web UI
• Support for SQLite / MySQL / PostgreSQL using Flask-SQLAlchemy
• Stylish UI using Bootstrap
• Customizable through config.py
• Automatic generation of full-featured REST API
• Real-time data update functionality

---

🎯 User Benefits

⚡ Outstanding Development Speed

• Instant Web App from CSV: Complete full-featured web applications by simply placing CSV files
• Zero-Coding REST API: Automatically generate CRUD-capable REST APIs without programming
• Instant Integration with Existing Systems: Add web interfaces to existing MySQL/PostgreSQL databases with just configuration changes

🔧 High Flexibility and Extensibility

• Multi-Database Support: Switch between SQLite (development/prototype) and MySQL/PostgreSQL (production) through configuration
• Customizable UI: Visual representation of data importance and status through conditional styling features
• JOIN Functionality: Integrated display of related data from multiple tables, intuitive understanding of complex data structures

👥 Enhanced Team Collaboration

• Non-Engineer Friendly: Anyone can manipulate data through the Web UI
• API Integration: Frontend teams can freely build UIs using REST APIs
• Real-time Updates: Always display the latest data even when multiple users work simultaneously

🛡️ Safe and Reliable Operation

• Comprehensive Test Coverage: Automated tests for all API, UI, and configuration features
• Error Handling: Proper error handling for invalid data and operations
• CORS Support: Support for secure cross-origin communication

💰 Cost Reduction Benefits

• Reduced Development Hours: Shorten development work from days/weeks to minutes
• Reduced Maintenance Costs: Long-term operation assured with Flask-based simple architecture
• Minimized Learning Costs: Designed to be accessible for Python beginners

---

🚀 Installation Method

Easy installation with pip install.

pip install monitor-app

---

🔧 Usage

1️⃣ Create a New Project

monitor-app startproject <project_name>

📌 Example: Create template with the name my_project

monitor-app startproject my_project

➡ Creates a Monitor-app application template in the my_project folder.

2️⃣ Register CSV to Database

cd my_project
python <project_name>/app.py import-csv

➡ Converts CSV files in the csv/ folder to SQLite database.

3️⃣ Start Web Application

python <project_name>/app.py runserver

➡ Access http://127.0.0.1:9990!

📌 runserver Options

Option	Description
--csv	Register CSV before starting
--debug	Start in debug mode
--port <PORT>	Specify port (default: 9990)

📌 Example: Start after registering CSV

python <project_name>/app.py runserver --csv

📌 Example: Start in debug mode on port 8000

python <project_name>/app.py runserver --debug --port 8000

---

📂 Folder Structure

my_project/
│── monitor_app/
│   ├── app.py           # Main Flask application file
│   ├── cli.py           # Command file for template creation
│   ├── csv_to_db.py     # Script to import CSV to database
│   ├── config/
│   │    ├── config.py   # Configuration file
│   ├── templates/       # HTML templates
│   ├── static/          # CSS / JavaScript / Images
│   ├── csv/             # Folder to store CSV data
│   ├── instances/       # SQLite database storage location
│── pyproject.toml       # Poetry configuration file
│── README.md            # This file

---

🔧 config/config.py Configuration

All project settings can be changed in config/config.py. The following are details of all configurable items.

📌 Database Settings

Database Type Selection

# Specify the type of database to use
DB_TYPE = "sqlite"  # "sqlite" | "mysql" | "postgresql"

SQLite Settings

# Custom path settings for SQLite
CUSTOM_SQLITE_DB_PATH = None  # Uses instances/database.db if None
# CUSTOM_SQLITE_DB_PATH = "/path/to/custom/database.db"  # Custom path specification

MySQL Settings

# Used when DB_TYPE = "mysql"
MYSQL_USER = "root"
MYSQL_PASSWORD = "password"
MYSQL_HOST = "localhost"
MYSQL_PORT = "3306"
MYSQL_DB = "monitor_app"

PostgreSQL Settings

# Used when DB_TYPE = "postgresql"
POSTGRES_USER = "postgres"
POSTGRES_PASSWORD = "password"
POSTGRES_HOST = "localhost"
POSTGRES_PORT = "5432"
POSTGRES_DB = "monitor_app"

📌 Separated Configuration Design

Monitor App clearly separates settings for CRUD operations and display purposes:

• ALLOWED_TABLES: Table definitions for CRUD API operations
• VIEW_TABLES: View definitions for screen display
• TABLE_CELL_STYLES: Styling settings for view display

This separation maintains data operation security while achieving display flexibility.

📌 CRUD Operation Settings (ALLOWED_TABLES)

ALLOWED_TABLES defines tables that can be CRUD operated via REST API. For security, tables not defined here cannot be accessed at all.

Role of ALLOWED_TABLES

• Security: Prevent unauthorized table access
• Automatic REST API Generation: Automatically create CRUD APIs only for defined tables
• Input Validation: Column validation for POST/PUT requests
• Data Structure Definition: Management of primary key and foreign key information

Basic CRUD Configuration

ALLOWED_TABLES = {
    "users": {
        "columns": ["id", "name", "email"],  # Columns for CRUD operations
        "primary_key": "id"                  # Primary key
    },
    "products": {
        "columns": ["id", "name", "price"], 
        "primary_key": "id"
    },
    "orders": {
        "columns": ["id", "user_id", "product_id", "amount"],
        "primary_key": "id",
        "foreign_keys": {"user_id": "users.id", "product_id": "products.id"}
    }
}

📌 View Display Settings (VIEW_TABLES)

VIEW_TABLES defines views exclusively for display on web screens. Complex JOINs, aggregations, filtering, etc. are possible.

Features of VIEW_TABLES

• Display Only: No CRUD operations, only display
• Flexible Queries: Free description of JOINs, aggregations, subqueries, etc.
• Metadata Support: Setting titles and descriptions
• Column Name Control: Control display names through AS clauses

Basic View Configuration

VIEW_TABLES = {
    "users_view": {
        "query": "SELECT id, name, email FROM users",
        "title": "User List",
        "description": "List of users registered in the system"
    },
    "products_view": {
        "query": "SELECT id, name, price FROM products", 
        "title": "Product List",
        "description": "List of products registered in the system"
    },
    "orders_summary": {
        "query": """
            SELECT 
                orders.id, 
                users.name AS user_name, 
                products.name AS product_name, 
                orders.amount
            FROM orders
            JOIN users ON orders.user_id = users.id
            JOIN products ON orders.product_id = products.id
        """,
        "title": "Order Summary",
        "description": "Detailed order list including user names and product names"
    }
}

Advanced View Examples (Aggregation & Subqueries)

VIEW_TABLES = {
    "sales_summary": {
        "query": """
            SELECT 
                users.name as user_name,
                COUNT(orders.id) as order_count,
                SUM(orders.amount * products.price) as total_sales,
                AVG(orders.amount * products.price) as avg_order_value
            FROM users
            LEFT JOIN orders ON users.id = orders.user_id
            LEFT JOIN products ON orders.product_id = products.id
            GROUP BY users.id, users.name
            HAVING order_count > 0
            ORDER BY total_sales DESC
        """,
        "title": "Sales Summary",
        "description": "Order statistics and sales aggregation by user"
    }
}

📌 View Styling Settings (TABLE_CELL_STYLES)

TABLE_CELL_STYLES is an advanced feature that dynamically changes cell appearance based on values during view display. Applied only to views defined in VIEW_TABLES, using actual column names (names specified with AS clauses) as keys.

Important Design Principles

• View Only: Applied only to views defined in VIEW_TABLES
• Column Name Based: Use actual output column names from queries
• Stability: Design less affected by database column name changes

Basic Structure

TABLE_CELL_STYLES = {
    "view_name": {
        "actual_column_name": {  # Name specified with AS clause or original column name
            # Conditional branching by value
            "greater_than": {"value": number, "class": "CSS_class"},
            "less_than": {"value": number, "class": "CSS_class"}, 
            "equal_to": {"value": value, "class": "CSS_class"},
            
            # Display style settings
            "width": "width%",
            "font_size": "sizepx",
            "align": "alignment",
            "bold": True/False
        }
    }
}

Implementation Example: Column Name Based Styling

TABLE_CELL_STYLES = {
    "products_view": {
        "price": {  # Use actual column name
            # Blue background for 1000 and above (high-price products)
            "greater_than": {"value": 1000, "class": "bg-primary text-white"},
            # Light blue background for under 500 (low-price products)
            "less_than": {"value": 500, "class": "bg-info text-dark"},
            # Gray background for exactly 750 (standard price)
            "equal_to": {"value": 750, "class": "bg-secondary text-white"},
            "width": "20%",
            "align": "right",
            "bold": False
        }
    },
    "orders_summary": {
        "amount": {  # Original column name without AS clause
            "greater_than": {"value": 10, "class": "bg-danger text-white"},
            "less_than": {"value": 5, "class": "bg-warning text-dark"},
            "equal_to": {"value": 7, "class": "bg-success text-white"},
            "width": "15%",
            "font_size": "32px",
            "align": "center",
            "bold": True
        }
    }
}

Exact Match Conditions for Strings

TABLE_CELL_STYLES = {
    "orders": {
        "status": {
            "equal_to": [
                {"value": "Completed", "class": "bg-success text-white"},
                {"value": "In Progress", "class": "bg-warning text-dark"},
                {"value": "Cancelled", "class": "bg-danger text-white"},
                {"value": "On Hold", "class": "bg-secondary text-white"}
            ]
        }
    }
}

Detailed Display Style Settings

TABLE_CELL_STYLES = {
    "sales": {
        "amount": {
            # Color coding by conditions
            "greater_than": {"value": 1000000, "class": "bg-success text-white"},
            "less_than": {"value": 100000, "class": "bg-danger text-white"},
            
            # Display styles
            "width": "20%",           # Column width (CSS width)
            "font_size": "24px",      # Font size
            "align": "right",         # Text alignment
            "bold": True              # Make bold
        }
    }
}

Available Bootstrap CSS Classes

# Background color classes
"bg-primary"      # Blue (important)
"bg-secondary"    # Gray (normal)
"bg-success"      # Green (success/complete)
"bg-danger"       # Red (danger/error)
"bg-warning"      # Yellow (warning/caution)
"bg-info"         # Light blue (information)
"bg-light"        # Light gray
"bg-dark"         # Black

# Text color classes  
"text-white"      # White text (for dark backgrounds)
"text-dark"       # Black text (for light backgrounds)
"text-primary"    # Blue text
"text-success"    # Green text
"text-danger"     # Red text
"text-warning"    # Yellow text
"text-info"       # Light blue text

# Combination examples
"bg-success text-white"     # Green background with white text
"bg-warning text-dark"      # Yellow background with black text
"bg-danger text-white"      # Red background with white text

Align Setting Options

"align": "left"     # Left align (default)
"align": "center"   # Center align
"align": "right"    # Right align (recommended for numbers)

📌 Application Settings

# Basic information
APP_TITLE = "Monitor App"                                    # Browser title
HEADER_TEXT = "📊 Monitor Dashboard"                         # Page header
FOOTER_TEXT = "© 2025 Monitor App - Powered by Flask & Bootstrap"  # Footer
FAVICON_PATH = "favicon.ico"                                 # Favicon

# Operation settings
TABLE_REFRESH_INTERVAL = 2000           # Table auto-refresh interval (milliseconds)
SQLALCHEMY_TRACK_MODIFICATIONS = False  # SQLAlchemy change tracking (usually False)

📌 CSV Settings

# CSV file directory
CUSTOM_CSV_DIR = None  # Uses project/csv/ if None
# CUSTOM_CSV_DIR = "/data/csv_files"  # Custom directory specification

🌐 REST API Endpoints

Monitor App provides REST APIs that support both database CRUD operations and view display.

📌 API Documentation (Swagger UI)

FastAPI-like automatically generated documentation is available:

• GET /docs - Interactive API documentation with Swagger UI
• GET /apispec_1.json - OpenAPI specification (JSON format)

📌 Available Endpoints

🔹 Table Information

• GET /api/tables - Get schema information for all tables

🔹 Table Operations (CRUD API)

• GET /api/<table_name> - Get all records from table
• GET /api/<table_name>/<id> - Get record with specified ID
• POST /api/<table_name> - Create new record
• PUT /api/<table_name>/<id> - Update record with specified ID
• DELETE /api/<table_name>/<id> - Delete record with specified ID

🔹 View Display (Display API)

• GET /api/table/<table_name> - Get raw table data (no style information)
• GET /api/view/<view_name> - Get view data (with style information)

📌 API Usage Examples

🔹 Basic Operations

1. Check API Documentation

# Check API specifications with Swagger UI
curl -X GET http://localhost:9990/docs

# Get API specifications in JSON format
curl -X GET http://localhost:9990/apispec_1.json

2. Get Table List

curl -X GET http://localhost:9990/api/tables

🔹 CRUD Operations

3. Get User List (CRUD)

curl -X GET http://localhost:9990/api/users

4. Create New User

curl -X POST http://localhost:9990/api/users \
  -H "Content-Type: application/json" \
  -d '{"name": "John Doe", "email": "[e-mail]"}'

5. Update User Information

curl -X PUT http://localhost:9990/api/users/1 \
  -H "Content-Type: application/json" \
  -d '{"name": "John Smith", "email": "[e-mail]"}'

6. Delete User

curl -X DELETE http://localhost:9990/api/users/1

🔹 Display-Only API

7. Get Raw Table Data

# Raw data without style information
curl -X GET http://localhost:9990/api/table/users

8. Get View Data

# View data with style information
curl -X GET http://localhost:9990/api/view/users_view

# Get order summary view
curl -X GET http://localhost:9990/api/view/orders_summary

📌 Response Formats

🔹 CRUD API Response Examples

Success (Create/Update)

{
  "success": true,
  "message": "Record created successfully",
  "data": {
    "id": 1,
    "name": "John Doe",
    "email": "[e-mail]"
  }
}

Error

{
  "error": "Record not found"
}

🔹 View API Response Example

View Data Retrieval

{
  "view_name": "orders_summary",
  "title": "Order Summary",
  "description": "Detailed order list including user names and product names",
  "columns": ["id", "user_name", "product_name", "amount"],
  "data": [
    {
      "id": 1,
      "user_name": "John Doe",
      "product_name": "Apple",
      "amount": 5
    }
  ],
  "cell_styles": {
    "amount": {
      "greater_than": {"value": 10, "class": "bg-danger text-white"},
      "less_than": {"value": 5, "class": "bg-warning text-dark"},
      "width": "15%",
      "font_size": "32px",
      "align": "center",
      "bold": true
    }
  }
}

📌 Important Notes

🔹 CRUD API

• Only tables defined in ALLOWED_TABLES can be operated
• Primary keys (usually id) are set automatically, so no need to send in POST requests
• For tables with foreign key constraints, verify the existence of related records before operations

🔹 View API

• Only views defined in VIEW_TABLES are accessible
• View data is display-only (CRUD operations not possible)
• Style settings apply only to views defined in TABLE_CELL_STYLES

🔹 Configuration Separation

• CRUD operations: /api/<table_name> → ALLOWED_TABLES
• Display data: /api/view/<view_name> → VIEW_TABLES + TABLE_CELL_STYLES
• Raw data: /api/table/<table_name> → ALLOWED_TABLES (no styling)

🧪 Running Tests

Monitor App includes a comprehensive test suite that verifies REST API operations.

📌 Test Environment Setup

Tests require pytest (already installed in Poetry environments).

# Using Poetry
poetry install

# Using pip
pip install pytest

📌 How to Run Tests

Run All Tests

python -m pytest tests/test_api.py -v

Run Specific Test Classes Only

# User API tests only
python -m pytest tests/test_api.py::TestUsersAPI -v

# Product API tests only
python -m pytest tests/test_api.py::TestProductsAPI -v

# Error handling tests only
python -m pytest tests/test_api.py::TestErrorHandling -v

Run Specific Test Methods Only

python -m pytest tests/test_api.py::TestUsersAPI::test_create_user -v

📌 Test Contents

🔹 Table Information API (TestTableAPI)

• GET /api/tables - Table schema retrieval

🔹 User API (TestUsersAPI)

• GET /api/users - Get all users
• GET /api/users/<id> - Get specific user
• POST /api/users - Create user
• PUT /api/users/<id> - Update user
• DELETE /api/users/<id> - Delete user
• Error cases (non-existent user, invalid data)

🔹 Product API (TestProductsAPI)

• GET /api/products - Get all products
• POST /api/products - Create product
• PUT /api/products/<id> - Update product

🔹 Order API (TestOrdersAPI)

• GET /api/orders - Get all orders
• POST /api/orders - Create order (with foreign key constraints)

🔹 Error Handling (TestErrorHandling)

• Access to non-existent tables
• Invalid JSON format requests
• Requests without Content-Type header

🔹 Data Validation (TestDataValidation)

• Automatic removal of extra fields
• Foreign key constraint validation

📌 Test Execution Example

$ python -m pytest tests/test_api.py -v

============================= test session starts ==============================
platform darwin -- Python 3.13.1, pytest-8.3.5, pluggy-1.5.0
collecting ... collected 20 items

tests/test_api.py::TestTableAPI::test_get_tables PASSED                  [  5%]
tests/test_api.py::TestUsersAPI::test_get_all_users PASSED               [ 10%]
tests/test_api.py::TestUsersAPI::test_get_user_by_id PASSED              [ 15%]
tests/test_api.py::TestUsersAPI::test_get_user_not_found PASSED          [ 20%]
tests/test_api.py::TestUsersAPI::test_create_user PASSED                 [ 25%]
...
============================== 20 passed in 0.95s ===========================

📌 Continuous Integration

If setting up CI/CD for your project, add the following commands to your build scripts:

# Run tests
python -m pytest tests/test_api.py

# Run tests with coverage report (optional)
pip install pytest-cov
python -m pytest tests/test_api.py --cov=monitor_app --cov-report=html

📌 Test File Locations

• tests/test_api.py - REST API tests
• tests/test_app.py - Web application tests
• tests/test_config.py - Configuration tests

📌 Details of Each Test File

🔹 tests/test_api.py

Comprehensive testing of REST APIs. Main contents:

• TestTableAPI: Database table schema information retrieval API

  • GET /api/tables testing (table list, column info, primary key verification)

• TestUsersAPI: User management API

  • Get all users, get specific user, create user, update, delete
  • Error handling (non-existent user, invalid data)

• TestProductsAPI: Product management API

  • Product retrieval, creation, update functionality tests

• TestOrdersAPI: Order management API

  • Order retrieval, creation functionality (including foreign key constraint verification)

• TestErrorHandling: Error handling

  • Access to non-existent tables, invalid JSON, requests without Content-Type

• TestDataValidation: Data validation

  • Extra field removal, foreign key constraint tests

🔹 tests/test_app.py

Tests basic web application functionality:

• test_index_page: Root page (/) display verification

  • Status code 200, "Monitor Dashboard" display verification

• test_table_page: Table display page (/table/users) operation verification

  • Allowed table display verification

🔹 tests/test_config.py

Configuration file validation:

• test_database_uri: Database connection setting verification

  • SQLite/MySQL/PostgreSQL URI format checks

• test_allowed_tables: Allowed table setting verification

  • users, orders, products table setting verification

• test_app_metadata: Application setting verification

  • App title, header, footer text setting verification

---

📌 monitor-app CLI Command List

Command	Description
monitor-app startproject <name>	Create new project
monitor-app import-csv	Register CSV to database
python <project_name>/app.py	Start web application
python <project_name>/app.py --csv	Start after CSV registration
python <project_name>/app.py --port <PORT>	Start on specified port

---

📌 Required Environment

• Python 3.10+
• Flask, Flask-SQLAlchemy, pandas, click
• Poetry (development environment)

---

📌 License

Provided under MIT License.

---

📌 Contributing

Pull Requests are welcome! 🚀
Bug reports and improvement suggestions are also welcome!

🔗 GitHub: Monitor App Repository

---

📖 Documentation:

• English: README.md
• 日本語: README_ja.md

---

✅ Now you can easily install & use monitor-app! 🚀