将指定Excel文档转为create table 建表语句。该脚本适用于单一且简单的建表语句
呈现效果
代码
文章来源:https://www.toymoban.com/news/detail-631012.html
# -*- coding:utf-8 -*-
# @Time : 2023/8/2 17:50
# @Author: 水兵没月
# @File : excel_2_mysql建表语句.py
import re
import pandas as pd
import mysql.connector
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 read_xlsx():
df = pd.read_excel(r'E:\下载\XXXXXX.xlsx')
mylist = df.values.tolist()
return mylist
def 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 iskong
iskong = "NOT NULL DEFAULT CURRENT_TIMESTAMP" if ziduan.upper() in ['CREATTIME'] else iskong
iskong = "NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" if ziduan.upper() in ['MODIFYTIME','MODIFYUPDATETIME'] else iskong
iskong = "NOT NULL AUTO_INCREMENT" if ziduan.upper() in ['ID'] else iskong
otherlist.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_seq
def creat_sel(sel):
mycursor = con_mysql()
mycursor.execute(sel)
mydb.commit()
if __name__ == '__main__':
mylist = read_xlsx()
sel = creat_info(mylist)
creat_sel(sel)
仅作为笔记记录,如有问题请各位大佬来指导 文章来源地址https://www.toymoban.com/news/detail-631012.html
到了这里,关于python-Excel数据模型文档转为MySQL数据库建表语句(需要连接数据库)-工作小记的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!