Python-导出MySql所有表的表结构

发表于 | 分类于
Python-导出MySql所有表的表结构

前言

表结构文档目前没有好的导出工具，这里就用Python实现了。

我使用的是Typora来打开Markdown文档，文档可以转成html、pdf和doc等格式。

Typora需要导出doc的话需要安装pandoc。

查询表结构SQL

MySQL获取数据库的所有表

1
2
3
4
5
6
7
SELECT 
    table_name,
    table_comment
FROM 
    information_schema.TABLES
WHERE 
    table_schema = 'xhkjedu_school';

表的字段

1
2
3
4
5
6
7
8
9
10
11
SELECT 
    COLUMN_NAME, 
    COLUMN_TYPE, 
    IS_NULLABLE, 
    COLUMN_DEFAULT, 
    COLUMN_COMMENT
FROM 
    information_schema.COLUMNS
WHERE 
    TABLE_SCHEMA = 'xhkjedu_school'  -- 替换为实际的数据库名
    AND TABLE_NAME = 'e_base';  -- 替换为实际的表名

设置别名

1
2
3
4
5
6
7
8
9
10
11
SELECT 
    COLUMN_NAME AS '字段名', 
    COLUMN_TYPE AS '字段类型', 
    IS_NULLABLE AS '是否可为空', 
    COLUMN_DEFAULT AS '默认值', 
    COLUMN_COMMENT AS '字段备注'
FROM 
    information_schema.COLUMNS
WHERE 
    TABLE_SCHEMA = 'xhkjedu_school'  -- 替换为实际的数据库名
    AND TABLE_NAME = 'e_base';  -- 替换为实际的表名

设置环境

venv

创建虚拟环境

1
python -m venv .zvenv

安装依赖

1
pip install mysql-connector-python

pipenv

创建虚拟环境

1
pipenv install

华为云镜像源

1
2
3
4
[[source]]
url = "https://mirrors.huaweicloud.com/repository/pypi/simple"
verify_ssl = false
name = "huaweicloud"

安装依赖

1
pipenv install mysql-connector-python

工具类

MySql查询

myql_utils.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
import os
from typing import List

import mysql.connector


def excute_sql(sql) -> List[tuple]:

    result = []
    # 建立数据库连接
    mydb = mysql.connector.connect(
        host=os.environ["DB_HOST"],
        port=os.environ["DB_PORT"],
        user=os.environ["DB_USER"],
        password=os.environ["DB_PWD"],
        database=os.environ["DB_NAME"],
    )

    # 创建游标对象
    mycursor = mydb.cursor()

    # 执行 SQL 查询
    mycursor.execute(sql)

    # 获取查询结果
    results = mycursor.fetchall()

    # 打印查询结果
    for row in results:
        result.append(row)

    # 关闭游标和数据库连接
    mycursor.close()
    mydb.close()
    return result

导出md表格

md_export_utils.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
def list_to_markdown_table(tablename, comm, headers, data):
    all_arr = []
    all_arr.append(f"## {tablename} ({comm})")
    # 生成表头行
    header_row = "| " + " | ".join(headers) + " |"
    # 生成分隔行
    separator_row = "| " + " | ".join(["---"] * len(headers)) + " |"
    # 生成数据行
    data_rows = []
    for row in data:
        data_row = "| " + " | ".join(str(cell) for cell in row) + " |"
        data_rows.append(data_row)

    # 组合表头、分隔行和数据行
    table = "\n".join([header_row, separator_row] + data_rows)
    all_arr.append(table)
    return "\n\n".join(all_arr) + "\n\n"


def test():
    # 示例数据
    headers = ["姓名", "年龄", "职业"]
    data = [["张三", 25, "工程师"], ["李四", 30, "教师"], ["王五", 22, "学生"]]

    markdown_table = list_to_markdown_table("t_user", "用户表", headers, data)
    print(markdown_table)

    # 将 Markdown 表格保存到文件
    with open("table.md", "a", encoding="utf-8") as f:
        f.write(markdown_table)

执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
import os

from utils.md_export_utils import list_to_markdown_table
from utils.myql_utils import excute_sql


def all_table_name(dbname):
    sql = f"""
        SELECT 
            table_name,
            table_comment
        FROM 
            information_schema.TABLES
        WHERE 
            table_schema = '{dbname}';
            """
    result = excute_sql(sql)
    return result


def table_detail(dbname, tablename):
    sql = f"""
    SELECT 
        COLUMN_NAME AS '字段名', 
        COLUMN_TYPE AS '字段类型', 
        IS_NULLABLE AS '是否可为空', 
        COLUMN_DEFAULT AS '默认值', 
        COLUMN_COMMENT AS '字段备注'
    FROM 
        information_schema.COLUMNS
    WHERE 
        TABLE_SCHEMA = '{dbname}'
        AND TABLE_NAME = '{tablename}';
    """
    result = excute_sql(sql)
    return result


if __name__ == "__main__":
    os.environ["DB_HOST"] = "110.110.110.110"
    os.environ["DB_PORT"] = "3306"
    os.environ["DB_USER"] = "root"
    os.environ["DB_PWD"] = "10086"
    os.environ["DB_NAME"] = "xhkjedu_school"
    dbname = os.environ["DB_NAME"]
    table_list = all_table_name(dbname)

    for table in table_list:
        print(table)
        tablename = table[0]
        tablecomm = table[1]
        tableheader = ["字段名", "字段类型", "是否可为空", "默认值", "字段备注"]
        tableinfo = table_detail(dbname, table[0])

        markdown_table = list_to_markdown_table(
            tablename, tablecomm, tableheader, tableinfo
        )
        with open("table_structure.md", "a", encoding="utf-8") as f:
            f.write(markdown_table)