描述:
大数据平台在数据采集时,针对excel或word等文件,且数据字段较多时,在数据库中建表比较浪费时间,开发一个工具,可在excel中写好字段英文名和中文名(或利用原始文档的标题),然后通过py生成脚本或直接在库中生成表
脚本:
import tkinter
from tkinter import *
import tkinter.messagebox
from tkinter import scrolledtext
import xlrd
import pymysql
import os
#从excel中获取字段项,组合建表语句
def get_create_sql(dir_path, file_name):
try:
# 打开xls文件
#excel_path = "%s%s" % (dir_path, file_name)
#print("excel_path:",excel_path)
wb = xlrd.open_workbook(filename=dir_path)
# 打开第一sheet
ws = wb.sheets()[0]
#获取行列数量
nrows = ws.nrows
#ncols = ws.ncols
#获取sheet1的名称
table_name = ws.name
#print("nrows:",nrows)
#print("ncols:",ncols)
#print("sheet1_name:",table_name)
#xlrd中行列是从0开始的
for i in range(nrows):
if i == 0: #字段英文,格式是列表
row0_data = ws.row_values(0)
#print(row0_data)
if i == 1: #字段中文,格式是列表
row1_data = ws.row_values(1)
#print(row1_data)
#将含有-字符的替换
header0 = [str(i).replace("-", "_").replace("-", "_").replace("(", "_").replace(")", "") for i in row0_data]
#print("header0:",header0)
header1 = [str(i).replace("-", "_").replace("-", "_").replace("(", "_").replace(")", "") for i in row1_data]
#print("header1:",header1)
#获取表名
table_commet = file_name.split(".")[0]
#print("table_name:",table_name)
create_sql = "CREAT