PyMySQL Metadata Manager

Description

PyMySQL Metadata Manager is a desktop application designed to abstract and manage relational database structures. Built with Python and Tkinter, it acts as a metadata repository that allows users to "Import" (reverse engineer) existing MySQL databases into a generalized schema format and "Export" (forward engineer) that metadata to create new database clones.

The core of the project is a custom "Meta-Database" structure (DATABASE_COURSE) that stores information about schemas, tables, columns, indexes, and constraints. This allows for high-level manipulation of database architecture directly through a user-friendly GUI.

Features

• Meta-Schema Storage: Automatically initializes a host database (DATABASE_COURSE) to store structural metadata (Schemas, Tables, Columns, Indexes, Constraints).
• Database Import (Reverse Engineering): Connects to an external MySQL database, extracts its structure (including foreign keys and indexes), and populates the local metadata repository.
• Database Export (Forward Engineering): Generates DDL (Data Definition Language) SQL scripts from the stored metadata to create a full clone of the database with a new name.
• GUI Management: Provides a Tkinter-based interface to view, add, update, and remove metadata rows manually.
• Constraint Handling: Robust support for Primary Keys, Unique Keys, and Foreign Key relationships during import/export.

Installation

Prerequisites

• Python 3.8 or higher
• MySQL Server running locally or remotely

Dependencies

Install the required Python MySQL connector:

pip install mysql-connector-python

Note: tkinter is included with standard Python installations.

Usage

1. Clone the Repository:

   git clone https://github.com/your-username/PyMySQL-Metadata-Manager.git
   cd PyMySQL-Metadata-Manager

2. Configure Credentials: Open project.ipynb and locate the connection setup cells (Step 1 and Step 3). Update the password field with your MySQL root password:

   mydb = mysql.connector.connect(
     host="127.0.0.1",
     user="root",
     password="YOUR_PASSWORD" # <--- Update this
   )

   Note: There are two places in the notebook where the connection is initialized; ensure both are updated.

3. Run the Application: You can run the project using Jupyter Notebook or by converting it to a script.

   • Using Jupyter: Open project.ipynb and run all cells sequentially. The GUI window will appear after the final cell executes.
   • As a Script: Convert the notebook to a Python script:

     jupyter nbconvert --to python project.ipynb
     python project.py

4. Using the GUI:

   • Main Menu: Use the buttons to view specific metadata tables (e.g., "Tables", "Columns").
   • File > Import database: Prompts for connection details of an existing database to scrape its structure.
   • File > Export database: Select a schema from the list and follow the prompts to create a clone on the server.

Contributing

Contributions are welcome!

1. Fork the project.
2. Create your feature branch (git checkout -b feature/AmazingFeature).
3. Commit your changes (git commit -m 'Add some AmazingFeature').
4. Push to the branch (git push origin feature/AmazingFeature).
5. Open a Pull Request.

License

Distributed under the MIT License. See LICENSE for more information.

Contact

Project Link: https://github.com/your-username/PyMySQL-Metadata-Manager

---

5. Detailed File Descriptions

project.ipynb (Main Application)

This Jupyter Notebook contains the complete source code for the application. It is structured into 7 logical steps:

1. Setup (Steps 0-3): Imports libraries (mysql.connector, tkinter) and establishes the initial connection to the MySQL server. It creates a dedicated database named DATABASE_COURSE which serves as the repository for all metadata.
2. Schema Initialization (Step 4): Defines Python functions (create_schemas_table, create_tables_table, etc.) to create the internal tables required to store database metadata. It creates relationships between these tables using Foreign Keys (e.g., Columns_table references Tables_table).
3. Database Manager Class (Step 5): A helper class DatabaseManager that encapsulates raw SQL operations. It handles connection management, query execution, data fetching, and dynamic row insertion/updating.
4. Application Logic (Step 6): The DatabaseApp class builds the GUI.
   • import_db method: Connects to a target database, queries information_schema and SHOW commands, and parses the results into the local metadata tables.
   • export_database method: Reads the local metadata and reconstructs the SQL CREATE and ALTER statements to build a new database instance.
5. Execution (Step 7): Instantiates the app and starts the Tkinter main loop.

Running Instructions: Ensure your MySQL server is running. Open the notebook, update the password field in the connection blocks, and run all cells. The GUI will launch as a separate window.

DATABASE_PROJECT_comments.pdf (Design Documentation)

This PDF provides the architectural blueprint for the project.

• ER Diagram: Page 1 contains a hand-drawn Entity-Relationship diagram visualizing the "Meta-Schema." It defines the entities Schema, Table, Columns, Index, and Constraint and their relationships (e.g., A Table has many Columns, A Table has many Indexes).
• Attribute Mapping: The diagram details specific attributes for each entity, such as is_nullable, data_type, and key_value for columns, which directly correspond to the fields created in the SQL tables in project.ipynb.
• Process Notes: Contains notes (in Persian) regarding the project workflow, mentioning that the design was based on course slides and refined during the Import/Export implementation phases.

Usage: Refer to this document to understand the underlying data model if you intend to modify the database structure or add new metadata fields to the application.