赞
踩
将create table XXXXXX 转为指定Excel文档。该脚本适用于数据库表结构本地文档记录
呈现效果
代码
- # -*- coding:utf-8 -*-
- # @Time : 2023/8/2 15:14
- # @Author: 水兵没月
- # @File : MySQL建表_2_excel.py
- import re
-
- import 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 mycursor
-
- def 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 YES
- namezh = 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_list
-
- def write_excel(data, table):
-
- # 要写入Excel的数据
- # 创建一个Pandas DataFrame
- df = 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)
仅作为笔记记录,如有问题请各位大佬来指导
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。