一键导出MySQL数据字典表

Reikoko 发表于 2023-11-19 21:32

```
import pymysql
import importlib,sys

header = {
   1:"TABLE_CATALOG",
   2:"TABLE_SCHEMA",
   3:"TABLE_NAME",
   4:"COLUMN_NAME",
   5:"ORDINAL_POSITION",
   6:"COLUMN_DEFAULT",
   7:"IS_NULLABLE",
   8:"DATA_TYPE",
   9:"CHARACTER_MAXIMUM_LENGTH",
   10:"CHARACTER_OCTET_LENGTH",
   11:"NUMERIC_PRECISION",
   12:"NUMERIC_SCALE",
   13:"DATETIME_PRECISION",
   14:"CHARACTER_SET_NAME",
   15:"COLLATION_NAME",
   16:"COLUMN_TYPE",
   17:"COLUMN_KEY",
   18:"EXTRA",
   19:"PRIVILEGES",
   20:"COLUMN_COMMENT",
   21:"GENERATION_EXPRESSION",
   22:"SRS_ID"
}

def generate(database_name):

importlib.reload(sys)

conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', db='database')
cursor = conn.cursor()
query = f"SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.TABLES WHERE table_type='BASE TABLE' AND TABLE_SCHEMA='{database_name}'"

cursor.execute(query)
tables = cursor.fetchall()

markdown_table_header = """\n\n\n### {} ({}) \n| 序号 | 字段名称 | 数据类型 | 是否为空 | 是否为主键 | 字段说明 |\n| :--: |----| ---- | ---- | ---- | ---- |\n"""

mysql_table_header=f"{header.get(5)},{header.get(4)},{header.get(16)},{header.get(7)},{header.get(17)},{header.get(20)}"

markdown_table_row = "| " + " | ".join(["{}"] * len(mysql_table_header.split(','))) + " |"

with open(f'{database_name}.md', 'w') as f:

   for table in tables:

         query = f"SELECT {mysql_table_header} FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='{database_name}' AND TABLE_NAME='{table}'ORDER BY ORDINAL_POSITION"

         cursor.execute(query)

         tmp_table = cursor.fetchall()

         p = markdown_table_header.format(table, remove_newline(table))

         for col in tmp_table:
            p += (remove_newline(markdown_table_row.format(*col)) + "\n")

         f.writelines(p)

def remove_newline(s):
return s.replace('\n', '')

generate("database")
```

参考代码链接：
生成并导出MySQL数据字典-风静花犹落
风静花犹落GitHub

生成的MD文件编辑工具
Typora 理论多版本兼容破解方案
https://www.52pojie.cn/thread-1710146-1-1.html
(出处: 吾爱破解论坛)

最终版本的代码由于mysql_table_header的自定义的全面性的需要，对于自定义的便捷性做了一定的取舍，希望大家多多包涵

tl;dr 发表于 2023-11-20 06:25

blindcat 发表于 2023-11-20 07:17

感谢分享

sxwa059587 发表于 2023-11-20 08:18

感谢分享

yu520 发表于 2023-11-20 08:42

具体如何使用呀

hushxh 发表于 2023-11-20 09:14

不知道杂用啊

Carnival 发表于 2023-11-20 09:34

感谢分享，学习到了，确实很使用

NAXLCQ 发表于 2023-11-20 09:48

感谢分享实用

ziaogiao 发表于 2023-11-20 09:49

感谢分享

flyclass 发表于 2023-11-20 09:54

谢谢分享

页: [1] 2

吾爱破解 - 52pojie.cn's Archiver

一键导出MySQL数据字典表