word表格数据转数据库指令一键生成代码
import docx
from docx import Document
"""
author:henianli
该程序可一键生成建表以及插入指令
您只需将docx文件填入下方即可在控制台获取到对应word表格的插入指令
程序所需要的库文件可通过pip install python-docx 指令下载
"""
"""读入文档"""
docfile = "C:/Users/XX/Desktop/dept.docx"
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 = []
row_cells = tb_rows[j].cells
for g in range(len(row_cells)):
context_set.append("")
fl = '-'
for k in range(len(row_cells)):
temp = ""
temp = row_cells[k].text
temp2 = temp.split()
temp3 = "".join(temp2)
temp = temp3
if(len(temp)==0):
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])