將指定Excel文檔轉(zhuǎn)為create table 建表語句。該腳本適用于單一且簡單的建表語句

呈現(xiàn)效果

?代碼

?

# -*- 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)

僅作為筆記記錄，如有問題請各位大佬來指導(dǎo)?文章來源地址http://www.zghlxwxcb.cn/news/detail-631012.html

到了這里，關(guān)于python-Excel數(shù)據(jù)模型文檔轉(zhuǎn)為MySQL數(shù)據(jù)庫建表語句(需要連接數(shù)據(jù)庫)-工作小記的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！