python-MySQL数据库建表语句(需要连接数据库)转存为Excel文档-工作小记
将create table XXXXXX 转为指定Excel文档。该脚本适用于数据库表结构本地文档记录
呈现效果
代码
# -*- coding:utf-8 -*-
# @Time : 2023/8/2 15:14
# @Author: 水兵没月
# @File : MySQL建表_2_excel.py
import reimport mysql.connector
import pandas as pd
db = '库名'
mydb = mysql.connector.connect(host="连接IP", user="用户名", password="密码",port='端口',database=db)
def con_mysql():mycursor = mydb.cursor()return mycursordef clo_mysql():mydb.close()def sel_mysql(table, db):mycursor = con_mysql()sel_info1 = "select * from information_schema.COLUMNS where TABLE_SCHEMA = '{}' and TABLE_NAME = '{}'".format(db, table)sel_info2 = "show create table {}".format(table)# sel_info = "desc {}".format(table)mycursor.execute(sel_info2)myresult = mycursor.fetchall()[0][-1]formnamezh = ''.join(re.findall(r"CHARSET=.*COMMENT='(.*)'", myresult))mycursor.execute(sel_info1)myresult = mycursor.fetchall()temp_list = []for ms in myresult:temp = []dbname = ms[1] # 库名formname = ms[2] # 表名nameeg = ms[3] # 字段英文名xuhao = ms[4] # 序号排序defaultzhi = ms[5] # 是否默认为为空 MULL or ''iskong = 'N' if ms[6] == 'NO' else 'Y'# 是否默认为为空 NOT or YESnamezh = ms[-2] # 字段中文名length = ''.join(re.findall(r'\((\d+)\)', ms[-6])) # 类型名+长度typename = re.findall(r'(.*)\(', ms[-6])[0] if re.findall(r'(.*)\(', ms[-6]) else ms[-6]PRI = 'Y' if 'PRI' == ms[-5] else 'N'remark = ms[-5] # 存放主键索引等标注temp = [formname,formnamezh,xuhao,nameeg, namezh, typename,length, '', remark,iskong,PRI]temp_list.append(temp)clo_mysql()return temp_listdef write_excel(data, table):# 要写入Excel的数据# 创建一个Pandas DataFramedf = pd.DataFrame(data, columns=['数据表英文名称', '数据表中文名称', '表内字段序号', '数据项英文名称', '数据项中文名称', '数据类型', '数据最大长度', '小数位长度', '数据格式说明', '是否可为空', '是否为主键'])# 创建一个新的Excel文件writer = pd.ExcelWriter('./{}.xlsx'.format(table), engine='openpyxl')# 在指定工作表中写入数据df.to_excel(writer, sheet_name='Sheet1', index=False)# 关闭Excel文件writer.save()if __name__ == '__main__':table = '表名'data = sel_mysql(table, db)write_excel(data, table)仅作为笔记记录，如有问题请各位大佬来指导