本文目录导读：
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
使用建议
-
选择合适格式：
- Markdown：便于版本控制
- Excel：便于查看和分享
- HTML：可直接在浏览器查看
-
定期更新：可以设置定时任务自动更新
-
添加额外信息：
- 索引信息
- 外键关系
- 数据示例
-
权限安全：确保数据库用户有足够的查询权限
需要我帮你针对特定的数据库或格式进行优化吗？
