word表格数据转数据库指令一键生成代码

word表格数据转数据库指令一键生成代码

import docx

from docx import Document


"""
author：henianli
该程序可一键生成建表以及插入指令
您只需将docx文件填入下方即可在控制台获取到对应word表格的插入指令
程序所需要的库文件可通过pip install python-docx 指令下载
"""


"""读入文档"""
docfile = "C:/Users/XX/Desktop/dept.docx"
#docfile = input() #输入文件路径和名字

document = Document(docfile)

tables = document.tables #获取表格集


for i in range(len(tables)):

    tb = tables[i]

    tb_rows = tb.rows

    #是否要为表格自定义表名

    print("是否为表格自定义表名，是则输入1，否则为0")
    flag = input()


    first_row = tb_rows[0].cells #默认表名

    first_cont = []
    #获取第一行
    for s in range(len(first_row)):

        #去空格
        temps = ""
        temps = first_row[s].text
        temps2 = temps.split()
        temps3 = "".join(temps2)
        temps = temps3
        first_cont.append(temps)



    tablename = first_row[0].text

    #获取数据类型
    type_data = []


    #获取数据内容
    contexts_set = []
    for x in range(len(first_row)):

        type_data.append(" ")
    for j in range(1,len(tb_rows)):
        #插入命令
        context_set = []

        #第j行
        row_cells = tb_rows[j].cells

        for g in range(len(row_cells)):
            context_set.append("")
        # 读取每一行单元格内容获取数据类型并修改为数据库命令的标准格式

        fl = '-'
        for k in range(len(row_cells)):
            # 单元格内容
            #如果为空则置为null

            temp = ""
            temp = row_cells[k].text
            temp2 = temp.split()
            temp3 = "".join(temp2)
            temp = temp3

            #修改格式为sql命令的标准格式
            if(len(temp)==0):
                #row_cells[k].text = 'NULL'
                context_set[k] = 'null'
            elif(temp.isspace()):
                context_set[k] = 'null'

            elif(temp.isdigit()):
                type_data[k] = 'INT'
                context_set[k] = temp

            elif(temp.isalpha()):
                type_data[k] = 'TEXT'
                context_set[k] = "'"+temp+"'"
            elif(fl in temp):
                type_data[k] = 'DATE'
                context_set[k] = "'"+temp+"'"


        contexts_set.append(context_set)







    if (flag == '1'):
        print("请输入表名")
        tablename = input()

        # 添加表名语句

    addrow = "CREATE TABLE " + tablename + " ("
    for m in range(len(first_row)):

        addrow = addrow + first_cont[m] + " " + type_data[m] + ","
    addrow = addrow[:-1]
    addrow = addrow + ");"  #最终的建表命令

    insertset = [] #插入命令集合


    for l in range(len(contexts_set)):
        #获取插入命令
        insertsql = "INSERT INTO " + tablename + " VALUES("

        context = contexts_set[l]

        for n in range(len(context)):


            insertsql = insertsql +  context[n] + ","
        insertsql = insertsql[:-1]
        insertsql = insertsql + ");"

        insertset.append(insertsql)

    print(addrow)

    for y in range(len(insertset)):
        print(insertset[y])