Features

• Dynamic MySQL Configuration: Configure MySQL database settings (host, port, user, password, database) at runtime via a web interface.

• Intelligent SQL Feedback: Every write statement — INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, GRANT, COMMIT and more — returns a structured report with a headline, stats, notes, schema diffs, and MySQL warnings.

• Enhanced Error Diagnostics: Common MySQL errors (1045 access denied, 1049 unknown database, 2003 can’t connect, …) are translated into friendly cards with a likely cause and a fix hint.

• Template Rendering: Built-in support for rendering templates stored in the templates folder.

• Query Execution API: Execute MySQL queries dynamically through POST requests.

• CRUD Operations: Perform create, read, update, and delete operations programmatically.

• RESTful Design: Leverage Flask to expose endpoints for database interactions.

• Environment Configuration: Load sensitive credentials securely using environment variables.

Endpoints

1. `/`: Displays the MySQL configuration page.

2. `/home`: Displays the home page.

3. `/config_mysql`: Accepts a POST request to configure MySQL connection details dynamically.

4. `/execute_query`: Accepts a POST request to execute MySQL queries or perform operations (e.g., insert, delete, update).

Installation

1. Install the package:

   pip install PyAwaish

   • Before installing, ensure you have Python 3.10 or higher installed on your system.

   • Ensure you have MySQL installed and running on your system.

2. Create a .env file in the root directory of your project and add your credentials:

   MYSQL_HOST="localhost"
   MYSQL_PORT="3306"
   MYSQL_USER="root"
   MYSQL_PASSWORD="your_password"
   MYSQL_DB="mydatabase"
   SECRET_KEY="YOUR_SECRET_KEY"

Usage

Running the Application

To start the MySQL web application server, instantiate the MysqlApplication class and call its execute method:

from PyAwaish.MysqlApplication import MysqlApplication

if __name__ == "__main__":
    app = MysqlApplication(secret_key=".env")
    app.execute(debug_mode=True, port_number=8080, host_address="127.0.0.1")

This runs the application in debug mode on https://127.0.0.1:8080.

API Reference

Function Signature

def execute(self, debug_mode: bool = False, port_number: int = 5001,
            host_address: str = "0.0.0.0", use_ssl: bool = True,
            ssl_cert: str | None = None, ssl_key: str | None = None) -> None:

Parameters

• debug_mode (bool, optional): Enables or disables Flask’s debug mode. Default is False.

• port_number (int, optional): The port number on which the application runs. Default is 5001.

• host_address (str, optional): The host address to bind the server. Default is "0.0.0.0" (accessible on all network interfaces).

• use_ssl (bool, optional): Serve over HTTPS. Default is True — the app opens as https://. Pass use_ssl=False for plain HTTP.

• ssl_cert / ssl_key (str, optional): Paths to your own certificate and private key. When omitted, the app prefers mkcert (if installed) to issue a locally-trusted certificate with zero browser warnings; otherwise a persistent self-signed certificate is generated automatically on first run and stored in ~/.pyawaish/ssl/ (browsers will show a one-time warning for self-signed certificates).

Initializing MysqlApplication with a Secret Key

The MysqlApplication class requires a secret key for initialization. You can provide it in different ways:

1. Using a ``.env`` file (full path)

   app = MysqlApplication(secret_key=r"C:\path\to\.env")

2. Using a ``.env`` file (relative path)

   app = MysqlApplication(secret_key=".env")

3. Without a secret key

   app = MysqlApplication()

   If no secret key is provided, the application uses default settings.

4. Using a custom secret key string

   app = MysqlApplication(secret_key="your_secret_key")

Configuring MySQL

1. Navigate to the root endpoint (https://localhost:5001/) to access the configuration page.

2. Enter the database details (host, port, username, password, database name) and click Establish Connection.

3. Upon successful configuration, you will be redirected to the home page.

Executing Queries

Use the /execute_query endpoint to run SQL queries or perform operations. Example request:

• POST Request Example:

  {
      "operation": "insert",
      "table_name": "users",
      "columns": "name, email",
      "values": "'John Doe', 'john@example.com'"
  }

• Supported Operations:

  • insert: Insert data into a table.

  • delete: Delete data from a table with a condition.

  • update: Update data in a table with a condition.

  • fetch_data: Fetch all data from a table.

  • show_tables: List all tables in the database.

  You can also send raw SQL via the query field (the custom query box on the home page) — the statement type is detected automatically, and write statements return the structured feedback report.

Dependencies

The application requires the following dependencies (listed in requirements.txt):

• Flask: Web framework.

• Flask-MySQLdb: MySQL connector for Flask.

• python-dotenv: Loads environment variables from a .env file into os.environ.

To install them, run:

pip install -r requirements.txt

Environment Variables

• MYSQL_HOST: MySQL server hostname (default: localhost).

• MYSQL_PORT: MySQL server port (default: 3306).

• MYSQL_USER: MySQL username (default: root).

• MYSQL_PASSWORD: MySQL password.

• MYSQL_DB: Default MySQL database name.

• SECRET_KEY: Flask secret key for session security.

Changelog

Refer to CHANGELOG.txt for the complete version history of the project.

License

This project is licensed under the MIT License. See LICENSE for full details.

Contact

For questions or feedback, contact:

• Email: abuawaish7@gmail.com

• GitHub: https://github.com/abuawaish

Added

• Rich structured feedback for every write statement: each response now carries severity (success/notice), category (DML/DDL/DCL/TCL), a headline, labelled stats, explanatory notes, execution duration, and any MySQL warnings (SHOW WARNINGS, captured before COMMIT).

• DML intelligence: INSERT … ON DUPLICATE KEY UPDATE reports rows processed vs updated in place; REPLACE reports replaced rows; UPDATE distinguishes rows matched vs rows actually changed (“matched, nothing changed” is a notice, not a success); missing-WHERE warnings on UPDATE/DELETE; LOAD DATA support with records/deleted/skipped counters.

• Schema-change reporting: ALTER produces a real before/after diff (added, removed, modified columns and index changes) plus the resulting table structure; RENAME lists every renamed table; TRUNCATE confirms the structure is intact; CREATE TABLE shows the new structure.

• DCL (GRANT/REVOKE) and TCL (COMMIT/ROLLBACK/SAVEPOINT/START/BEGIN) get dedicated human-readable messages.

• Query keyword detection now strips leading comments/whitespace and handles tabs, newlines and leading parentheses; DESC alias recognised as a result-set query (previously fell through with a fake success message).

• CALL now drains all result sets from stored procedures.

• Structured insert/update/delete operations report real row counts; a 0-row UPDATE/DELETE now returns a warning (“no rows matched”) instead of a false success.

• Config page: new Port field (default 3306, validated 1-65535), categorized flash messages with a pretty error card (title + detail + fix hint) that translates common MySQL error codes (1045, 1049, 2002, 2003, 2013, 1130) into plain language, plus a connection-tips panel.

• HTTPS by default: execute() now serves https:// out of the box using an auto-generated persistent self-signed certificate (stored in ~/.pyawaish/ssl/, covers localhost, hostname and LAN IPs, regenerated if IPs change); when mkcert is installed it is preferred and issues a locally-trusted certificate with zero browser warnings; pass use_ssl=False for plain HTTP, or supply your own certificate via ssl_cert/ssl_key.

Changed

• Home page renders the structured feedback as a rich card: per-statement icon, severity-colored headline, verb/category/target chips, stats grid, explanatory notes, schema-change list (“What changed”), resulting table structure and MySQL warnings.