如何辨别官方网站,绍兴网络科技有限公司,电商网站建设去迅法网,旅游网站建设公司哪家好将指定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 m…将指定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端口,databasedb)
def con_mysql():mycursor mydb.cursor()return mycursordef clo_mysql():mydb.close()def read_xlsx():df pd.read_excel(rE:\下载\XXXXXX.xlsx)mylist df.values.tolist()return mylistdef creat_info(mylist):table 表名info CHARSET utf8mb4COMMENT 中文表名-注释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 {} ({})ENGINEInnoDB AUTO_INCREMENT0 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)
仅作为笔记记录如有问题请各位大佬来指导
