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()
结果