python-Excel数据模型文档转为MySQL数据库建表语句(需要连接数据库)-工作小记
将指定Excel文档转为create table 建表语句。该脚本适用于单一且简单的建表语句
呈现效果
代码
# -*- coding:utf-8 -*-
# @Time : 2023/8/2 17:50
# @Author: 水兵没月
# @File : excel_2_mysql建表语句.py
import reimport pandas as pd
import mysql.connectordb = '库名'mydb = mysql.connector.connect(host="连接IP", user="用户名", password="密码",port='端口',database=db)
def con_mysql():mycursor = mydb.cursor()return mycursordef clo_mysql():mydb.close()def read_xlsx():df = pd.read_excel(r'E:\下载\XXXXXX.xlsx')mylist = df.values.tolist()return mylistdef creat_info(mylist):table = '表名'info = ''CHARSET = 'utf8mb4'COMMENT = '中文表名-注释'otherlist = []for ml in mylist:ml = [_ if not isinstance(_, float) else _ if re.findall(r'\d+',str(_)) else '' for _ in ml]print(ml)table = ml[0] # 表名COMMENT = ml[1] # '中文表名-注释'ziduan = ml[3] # 字段名ziduan_zh = ml[4] # 注释leixing = ml[5] # 类型changdu = int(ml[6]) if ml[6] else '' # 长度leixing_changdu = leixing if not changdu else leixing+"("+str(changdu)+")"iskong = 'DEFAULT NULL' if ml[-2] == 'Y' or ml[-2] == '' else 'NOT NULL DEFAULT ""'iskong = "NOT NULL DEFAULT"+' 1' if ziduan.upper() in ['ISVALID','DATASTATUS','DATASTATE'] else iskongiskong = "NOT NULL DEFAULT CURRENT_TIMESTAMP" if ziduan.upper() in ['CREATTIME'] else iskongiskong = "NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" if ziduan.upper() in ['MODIFYTIME','MODIFYUPDATETIME'] else iskongiskong = "NOT NULL AUTO_INCREMENT" if ziduan.upper() in ['ID'] else iskongotherlist.append({ziduan:ml[-3]})info += '`'+ziduan+'`'+' '+leixing_changdu+' '+iskong+' COMMENT '+ "'"+ziduan_zh+"'" +','for ol_dict in otherlist:for k, v in ol_dict.items():if 'PRI' == v:info += "PRIMARY KEY (`{}`),".format(k)elif 'UNI' == v:info += "UNIQUE KEY `{}` (`{}`),".format(k,k)elif 'MUL' == v:info +="KEY `{}` (`{}`) USING BTREE,".format(k,k)info = info.strip(',')creat_table_seq = """CREATE TABLE `{}` ({})ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET={} COMMENT='{}';""".format(table, info, CHARSET, COMMENT)return creat_table_seqdef creat_sel(sel):mycursor = con_mysql()mycursor.execute(sel)mydb.commit()
if __name__ == '__main__':mylist = read_xlsx()sel = creat_info(mylist)creat_sel(sel)仅作为笔记记录，如有问题请各位大佬来指导