Python获取数据库所有表字段信息，存入Excel

 pymysql链接数据库

db = pymysql.connect(host="192.168.1.xxx",user="xxx",password="xxx",port=3306,database="xxx")

执行 show tables 获取所有表

show database;

执行 show full columns from <数据表> from <数据库> 获取表的字段信息

show full columns from your_table from your_databse;

将暂存的字典转为DataFrame

df = pd.DataFrame(dic)

调用to_excel方法存入excel

df.to_excel("表数据结构.xlsx",sheet_name='Sheet1',index=False)

 完整代码

import pymysql
import pandas as pd

dic = {
    "库名":[],
    "表名":[],
    "字段名":[],
    "数据类型":[],
    "默认值":[],
    "是否允许为空":[],
    "字段说明":[]
}

mapper = {
    "id":"主键值",
    "create_time":"创建时间",
    "update_time":"更新时间",
    "create_user_id":"创建者id",
    "update_user_id":"更新者id"
}

db = pymysql.connect(host="192.168.1.xxx",user="xxx",password="xxx",port=3306,database="xxx")

cursor = db.cursor()

sql = """
    show tables;
"""

ts = cursor.execute(sql)
ts = cursor.fetchall()
print(ts)


for i,t in enumerate(ts):
    print(f"--------{i}/38------")
    sql = f"""
    SHOW FULL COLUMNS FROM {t[0]} from xq_plus
    """
    rows = cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        dic["库名"].append("xq_plus")
        dic["表名"].append(t[0])
        dic["字段名"].append(row[0])
        dic["数据类型"].append(row[1])
        dic["是否允许为空"].append("否" if row[3]=="NO" else "是")
        dic["字段说明"].append(row[-1] if row[-1]!="" else mapper.get(row[0]))
    # break
print(dic)

df = pd.DataFrame(dic)
df.to_excel("表数据结构.xlsx",sheet_name='Sheet1',index=False)
cursor.close()
db.close()

结果