如何用脚本生成数据字典

本文目录导读：

1. MySQL 生成数据字典
2. PostgreSQL 生成数据字典
3. Shell 脚本（MySQL）
4. 一键安装依赖
5. 使用建议

MySQL 生成数据字典

Python 脚本

import pymysql
import pandas as pd
from datetime import datetime
def generate_mysql_dict(host, user, password, database):
    # 连接数据库
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        charset='utf8mb4'
    )
    # 查询所有表和字段信息
    sql = """
    SELECT 
        t.TABLE_NAME as '表名',
        t.TABLE_COMMENT as '表注释',
        c.COLUMN_NAME as '字段名',
        c.COLUMN_TYPE as '数据类型',
        c.IS_NULLABLE as '是否为空',
        c.COLUMN_DEFAULT as '默认值',
        c.COLUMN_COMMENT as '字段注释',
        c.CHARACTER_MAXIMUM_LENGTH as '长度',
        c.EXTRA as '额外信息',
        c.COLUMN_KEY as '键类型'
    FROM INFORMATION_SCHEMA.TABLES t
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS c 
        ON t.TABLE_NAME = c.TABLE_NAME 
        AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
    WHERE t.TABLE_SCHEMA = %s
    ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION
    """
    df = pd.read_sql(sql, conn, params=[database])
    conn.close()
    # 生成Markdown格式
    generate_markdown(df)
    # 生成Excel格式
    generate_excel(df)
def generate_markdown(df):
    with open(f'数据字典_{datetime.now().strftime("%Y%m%d")}.md', 'w', encoding='utf-8') as f:
        f.write(f'# 数据字典\n\n生成时间：{datetime.now()}\n\n')
        for table in df['表名'].unique():
            table_data = df[df['表名'] == table]
            comment = table_data['表注释'].iloc[0] or '无注释'
            f.write(f'## {table} - {comment}\n\n')
            f.write('| 字段名 | 数据类型 | 长度 | 是否为空 | 默认值 | 键类型 | 注释 |\n')
            f.write('|--------|----------|------|----------|--------|--------|------|\n')
            for _, row in table_data.iterrows():
                f.write(f"| {row['字段名']} | {row['数据类型']} | {row['长度']} | "
                       f"{row['是否为空']} | {row['默认值'] or ''} | {row['键类型'] or ''} | "
                       f"{row['字段注释'] or ''} |\n")
            f.write('\n')
def generate_excel(df):
    with pd.ExcelWriter(f'数据字典_{datetime.now().strftime("%Y%m%d")}.xlsx', engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='数据字典', index=False)
        # 按表拆分到不同sheet
        for table in df['表名'].unique():
            table_data = df[df['表名'] == table].drop('表名', axis=1)
            sheet_name = table[:31]  # Excel sheet名限制31字符
            table_data.to_excel(writer, sheet_name=sheet_name, index=False)
# 使用示例
generate_mysql_dict('localhost', 'root', 'password', 'your_database')

PostgreSQL 生成数据字典

import psycopg2
import pandas as pd
def generate_pgsql_dict(host, port, dbname, user, password):
    conn = psycopg2.connect(
        host=host,
        port=port,
        dbname=dbname,
        user=user,
        password=password
    )
    sql = """
    SELECT 
        t.table_schema,
        t.table_name,
        pg_catalog.obj_description(
            (quote_ident(t.table_schema) || '.' || quote_ident(t.table_name))::regclass, 
            'pg_class'
        ) as table_comment,
        c.column_name,
        c.data_type,
        c.character_maximum_length,
        c.is_nullable,
        c.column_default,
        col_description(
            (quote_ident(t.table_schema) || '.' || quote_ident(t.table_name))::regclass, 
            c.ordinal_position
        ) as column_comment
    FROM information_schema.tables t
    JOIN information_schema.columns c 
        ON t.table_name = c.table_name 
        AND t.table_schema = c.table_schema
    WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema')
    ORDER BY t.table_schema, t.table_name, c.ordinal_position
    """
    df = pd.read_sql(sql, conn)
    conn.close()
    # 生成HTML格式
    generate_html(df)
def generate_html(df):
    html_content = f"""
    <!DOCTYPE html>
    <html>
    <head>
        <title>数据字典</title>
        <style>
            table {{ border-collapse: collapse; width: 100%; }}
            th, td {{ border: 1px solid #ddd; padding: 8px; text-align: left; }}
            th {{ background-color: #4CAF50; color: white; }}
            tr:nth-child(even) {{ background-color: #f2f2f2; }}
        </style>
    </head>
    <body>
        <h1>数据字典</h1>
        <p>生成时间: {datetime.now()}</p>
    """
    for table in df['table_name'].unique():
        table_data = df[df['table_name'] == table]
        comment = table_data['table_comment'].iloc[0] or ''
        html_content += f"""
        <h2>{table} - {comment}</h2>
        <table>
            <tr>
                <th>字段名</th>
                <th>数据类型</th>
                <th>长度</th>
                <th>是否为空</th>
                <th>默认值</th>
                <th>注释</th>
            </tr>
        """
        for _, row in table_data.iterrows():
            html_content += f"""
            <tr>
                <td>{row['column_name']}</td>
                <td>{row['data_type']}</td>
                <td>{row['character_maximum_length'] or ''}</td>
                <td>{row['is_nullable']}</td>
                <td>{row['column_default'] or ''}</td>
                <td>{row['column_comment'] or ''}</td>
            </tr>
            """
        html_content += "</table><br>"
    html_content += "</body></html>"
    with open(f'数据字典_{datetime.now().strftime("%Y%m%d")}.html', 'w', encoding='utf-8') as f:
        f.write(html_content)

Shell 脚本（MySQL）

#!/bin/bash
# 数据库配置
DB_HOST="localhost"
DB_USER="root"
DB_PASS="password"
DB_NAME="your_database"
# 生成Markdown格式
generate_markdown() {
    echo "# 数据字典"
    echo ""
    # 获取所有表
    tables=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -D $DB_NAME -e "SHOW TABLE STATUS" | awk '{print $1}' | tail -n +2)
    for table in $tables; do
        echo "## $table"
        echo ""
        echo "| 字段名 | 类型 | 是否为空 | 默认值 | 注释 |"
        echo "|--------|------|----------|--------|------|"
        mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -D $DB_NAME -e "
        SELECT 
            COLUMN_NAME as '字段名',
            COLUMN_TYPE as '类型',
            IS_NULLABLE as '是否为空',
            COLUMN_DEFAULT as '默认值',
            COLUMN_COMMENT as '注释'
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = '$DB_NAME' 
            AND TABLE_NAME = '$table'
        ORDER BY ORDINAL_POSITION
        " | tail -n +2 | while read line; do
            echo "| $line |"
        done
        echo ""
    done
}
# 执行生成
generate_markdown > "data_dict_$(date +%Y%m%d).md"
echo "数据字典已生成: data_dict_$(date +%Y%m%d).md"

一键安装依赖

# 安装Python依赖
pip install pymysql psycopg2-binary pandas openpyxl
# 如果使用pip3
pip3 install pymysql psycopg2-binary pandas openpyxl

使用建议

1. 选择合适格式：

   • Markdown：便于版本控制
   • Excel：便于查看和分享
   • HTML：可直接在浏览器查看

2. 定期更新：可以设置定时任务自动更新

3. 添加额外信息：

   • 索引信息
   • 外键关系
   • 数据示例

4. 权限安全：确保数据库用户有足够的查询权限

需要我帮你针对特定的数据库或格式进行优化吗？