MAgento database structure Query

[Genaker]

To generate a JSON output containing only the table names and their structures without the schema, you can use the following SQL query:

SELECT JSON_OBJECTAGG(
    table_name, 
    JSON_OBJECT(
        'structure', CREATE_TABLE
    )
) AS tables_structure
FROM (
    SELECT 
        table_name,
        CONCAT(
            'CREATE TABLE ', table_name, ' (',
            GROUP_CONCAT(
                CONCAT(column_name, ' ', column_type, 
                       IF(is_nullable = 'NO', ' NOT NULL', ''),
                       IF(column_default IS NOT NULL, CONCAT(' DEFAULT ', column_default), ''),
                       IF(extra != '', CONCAT(' ', extra), '')
                ) SEPARATOR ', '
            ),
            ')'
        ) AS CREATE_TABLE
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
    GROUP BY table_name
) AS table_structures;

This query will produce a JSON object where each key is a table name, and the value is another JSON object containing the table structure. You can run this query in your MySQL database to get a JSON representation of all table structures without the schema.

[Genaker]

Using Python :

import pymysql
import json
import argparse

def get_table_structures(pattern):
    config = {
        'host': '127.0.0.1',
        'user': 'magento',
        'password': 'magento',
        'db': 'magento_luma',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }

    connection = None  # Initialize connection outside try block
    try:
        connection = pymysql.connect(**config)
        result = {}

        with connection.cursor() as cursor:
            # Get matching tables
            cursor.execute("""
                SELECT TABLE_NAME 
                FROM information_schema.tables
                WHERE table_schema = DATABASE()
                AND TABLE_NAME LIKE %s
            """, (f'{pattern}%',))
            
            tables = [row['TABLE_NAME'] for row in cursor.fetchall()]

            for table in tables:
                cursor.execute(f"SHOW CREATE TABLE `{table}`")
                create_table = cursor.fetchone()['Create Table']
                
                # Clean up formatting
                formatted = create_table.replace('\\n', '\n').replace('\\t', '  ')
                result[table] = formatted.replace('\\', '\\\\').replace('"', '\\"')

        return json.dumps(result, indent=2)

    except pymysql.Error as e:
        print(f"Database error: {e}")
        return None
    finally:
        # Proper connection cleanup
        if connection and connection.open:
            try:
                connection.close()
                print("Database connection closed")
            except Exception as e:
                print(f"Error closing connection: {e}")

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description='Export MySQL table structures')
    parser.add_argument('-p', '--pattern', required=True, 
                       help='Table name pattern (e.g., catalog)')
    parser.add_argument('-o', '--output', default='tables.json',
                       help='Output JSON filename')
    
    args = parser.parse_args()
    
    output = get_table_structures(args.pattern)
    if output:
        with open(args.output, 'w') as f:
            f.write(output)
        print(f"Exported {args.output} successfully")

[florinel-chis]

I would document that and put an mcp server on top of it so that the llm can call the mcp with the documentation.

[Genaker]

I has MCP as separate module

…

On Mon, Nov 24, 2025 at 1:55 AM Florinel Chis ***@***.***> wrote: *florinel-chis* left a comment (Genaker/AgentoAI#9) <#9 (comment)> I would document that and put an mcp server on top of it so that the llm can call the mcp with the documentation. — Reply to this email directly, view it on GitHub <#9 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACGJNZT4QO3LBWJVC33SORD36LIXRAVCNFSM6AAAAACNAGPQ36VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTKNRZHA2DINZVHE> . You are receiving this because you authored the thread.Message ID: ***@***.***>