python-mysql根据数据库表生成数据字典

python脚本实现将数据库的表批量生成数据字典
import pymysql
import os

# 数据库连接信息
DB_HOST = '120.25.148.104'  # 例如：'localhost' 或 '127.0.0.1'
DB_USER = 'root'
DB_PASSWORD = 'Yunxi123$%^'
DB_NAME = 'yunxi_device'
DB_CHARSET = 'utf8mb4'
DB_PORT = 3311  # 数据库端口
# 输出目录
OUTPUT_DIR = 'html_output'

# 创建输出目录（如果不存在）
if not os.path.exists(OUTPUT_DIR):
    os.makedirs(OUTPUT_DIR)

# 连接数据库
connection = pymysql.connect(
    host=DB_HOST,
    user=DB_USER,
    password=DB_PASSWORD,
    db=DB_NAME,
    charset=DB_CHARSET,
    port=DB_PORT,
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with connection.cursor() as cursor:
        # 获取所有表名
        cursor.execute("SHOW TABLES;")
        tables = cursor.fetchall()

        for table in tables:
            table_name = table[f"Tables_in_{DB_NAME}"]
            print(f"处理表: {table_name}")

            # 获取表结构
            cursor.execute(f"SHOW FULL COLUMNS FROM  {table_name};")
            columns = cursor.fetchall()

            # 创建 HTML 文件内容
            html_content = f"""<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>{table_name} - 数据字典</title>
    <style>
        body {{
            font-family: Arial, sans-serif;
            margin: 20px;
            background-color: #f5f5f5;
        }}
        .container {{
            background-color: white;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
            max-width: 1000px;
            margin: 0 auto;
        }}
        h1 {{
            color: #333;
            text-align: center;
        }}
        table {{
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }}
        th, td {{
            border: 1px solid #ddd;
            padding: 12px;
            text-align: left;
        }}
        th {{
            background-color: #f2f2f2;
            font-weight: bold;
        }}
        tr:nth-child(even) {{
            background-color: #f9f9f9;
        }}
        tr:hover {{
            background-color: #f1f1f1;
        }}
    </style>
</head>
<body>
    <div class="container">
        <h1>{table_name} - 数据字典</h1>
        <table>
            <thead>
                <tr>
                    <th>字段名</th>
                    <th>数据类型</th>
                    <th>是否必填</th>
                    <th>默认值</th>
                    <th>描述</th>
                </tr>
            </thead>
            <tbody>
"""

            # 填充表格内容
            for column in columns:
                field = column['Field']
                type = column['Type']
                nullable = "否" if column['Null'] == "NO" else "是"
                default = column['Default'] if column['Default'] is not None else ""
                if 'Comment' in column:
                    comment = column['Comment'] if column['Comment'] else ""
                else:
                    comment = ""

                html_content += f"""                <tr>
                    <td>{field}</td>
                    <td>{type}</td>
                    <td>{nullable}</td>
                    <td>{default}</td>
                    <td>{comment}</td>
                </tr>
"""

            # 完成 HTML 文件内容
            html_content += """            </tbody>
        </table>
    </div>
</body>
</html>
"""

            # 保存为 HTML 文件
            file_path = os.path.join(OUTPUT_DIR, f"{table_name}.html")
            with open(file_path, 'w', encoding='utf-8') as html_file:
                html_file.write(html_content)

            print(f"已生成 HTML 文件: {file_path}")

finally:
    connection.close()
打赏赞