import tkinter
from tkinter import *
import tkinter.messagebox
from tkinter import scrolledtext
import xlrd
import pymysql
import os
#從excel中獲取字段項(xiàng),組合建表語句
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]
#獲取行列數(shù)量
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 = "CREATE TABLE `%s` (\n" % table_name
#print("create_sql:",create_sql)
header0_len = len(header0)
#print("header0_len:",header0_len)
for i in range(header0_len):
#print("i:", i,"value:",header0[i])
if i header0_len-1:
create_sql += "`%s` varchar(255) DEFAULT NULL COMMENT '%s',\n" % (header0[i], header1[i])
if i == header0_len-1:
create_sql += "`%s` varchar(255) DEFAULT NULL COMMENT '%s'\n" % (header0[i], header1[i])
create_sql += ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='%s';\n" % table_commet
#print("[INFO]:[create_sql]", create_sql)
except BaseException as e:
#print("[ERRO]:", str(e))
tkinter.messagebox.showerror('錯誤', str(e))
return create_sql
def to_db(create_sql,hostname,hostport,username,userpasswd,dbname):
#鏈接數(shù)據(jù)庫,執(zhí)行
#print(hostname,hostport,username,userpasswd,dbname)
try:
db = pymysql.connect(host=hostname, port=hostport, user=username, passwd=userpasswd, db=dbname)
cursor = db.cursor()
cursor.execute(create_sql)
cursor.fetchall()
db.close()
#print("表創(chuàng)建成功!")
tkinter.messagebox.showinfo('提示', '表創(chuàng)建成功 !')
except BaseException as e:
#print("表創(chuàng)建失敗!", str(e))
tkinter.messagebox.showerror('錯誤', str(e))
# get 變量內(nèi)容
def show_db():
if v1.get() == '' or v2.get() == '' or v3.get() == '' or v4.get() == '' or v5.get() == '' or v6.get() == '':
tkinter.messagebox.showinfo('提示', '請先輸入內(nèi)容 !')
return
if v1.get() == 'token**123':
tkinter.messagebox.showinfo('提示', 'author by lijiyan 2020-12-03,Thank you.')
return
#print("文件路徑名 :%s" % e1.get())
#print("數(shù)據(jù)庫IP :%s" % e2.get())
#print("數(shù)據(jù)庫端口 :%s" % e2.get())
#print("庫名 :%s" % e2.get())
#print("用戶名 :%s" % e2.get())
#print("密碼 :%s" % e2.get())
#easygui.msgbox('執(zhí)行完成 !')
dir_pathname = e1.get()
#dir_pathi = os.path.dirname(dir_pathname)
file_namei = os.path.basename(dir_pathname)
#print(dir_pathname,file_namei)
create_sql = get_create_sql(dir_path=dir_pathname,
file_name=file_namei
)
to_db(create_sql=create_sql,
hostname=e2.get(),
hostport=int(e3.get()),
dbname=e4.get(),
username=e5.get(),
userpasswd=e6.get()
)
def show_sql():
if v1.get() == '':
tkinter.messagebox.showinfo('提示', '請先輸入內(nèi)容 !')
return
#print("文件路徑名 :%s" % e1.get())
dir_pathname = e1.get()
# dir_pathi = os.path.dirname(dir_pathname)
file_namei = os.path.basename(dir_pathname)
# print(dir_pathname,file_namei)
create_sql = get_create_sql(dir_path=dir_pathname,
file_name=file_namei
)
#tkinter.messagebox.showinfo('提示', create_sql)
t.delete(1.0, END)
t.insert('insert', create_sql)
#e8.insert('insert',create_sql)
#這個可以,但樣式難看
#Label(top, text=create_sql,wraplength=200).grid(row=8,column=0,sticky=W+E+N+S,padx=5,pady=5,rowspan=7,columnspan=2)
#初始框的聲明
top = tkinter.Tk()
top.title("生成createsql工具v1.0")
top.geometry("400x300+300+200")
## 標(biāo)簽控件,對Label內(nèi)容進(jìn)行 表格式 布局
Label(top,text='文件路徑名 :').grid(row=0, column=0)
Label(top,text='數(shù)據(jù)庫IP :').grid(row=2,column=0)
Label(top,text='數(shù)據(jù)庫端口 :').grid(row=3,column=0)
Label(top,text='庫名 :').grid(row=4,column=0)
Label(top,text='用戶名 :').grid(row=5,column=0)
Label(top,text='密碼 :').grid(row=6,column=0)
#Label(top,text='sql :').grid(row=0,column=1)
#定義變量
v1 = StringVar()
v2 = StringVar()
v3 = StringVar()
v4 = StringVar()
v5 = StringVar()
v6 = StringVar()
#v8 = StringVar()
# 輸入控件,用于儲存 輸入的內(nèi)容
e1 = Entry(top, textvariable=v1)
e2 = Entry(top, textvariable=v2)
e3 = Entry(top, textvariable=v3)
e4 = Entry(top, textvariable=v4)
e5 = Entry(top, textvariable=v5)
e6 = Entry(top, textvariable=v6, show="*")
#e8 = Entry(top, textvariable=v8)
# 進(jìn)行表格式布局
e1.grid(row=0,column=1,padx=10,pady=5)
e2.grid(row=2,column=1,padx=10,pady=5)
e3.grid(row=3,column=1,padx=10,pady=5)
e4.grid(row=4,column=1,padx=10,pady=5)
e5.grid(row=5,column=1,padx=10,pady=5)
e6.grid(row=6,column=1,padx=10,pady=5)
#e8.grid(row=0,column=2,sticky=W+E+N+S,padx=5,pady=5,rowspan=7,columnspan=2)
#文本內(nèi)容較大,滾動文本框,展示文本內(nèi)容
t = scrolledtext.ScrolledText(top,width = 20,height = 20,wrap=WORD)
t.grid(row=0,column=2,rowspan=7,columnspan=2)
# 設(shè)置 button 指定 寬度 , 并且 關(guān)聯(lián) 函數(shù) , 使用表格式布局 .
Button(top,text='生成sql',width=10,command=show_sql).grid(row=1,column=1,sticky=E,padx=10,pady=5)
Button(top,text='入庫',width=10,command=show_db).grid(row=7,column=1,sticky=W,padx=10,pady=5)
Button(top,text='退出',width=10,command=top.quit).grid(row=7,column=1,sticky=E,padx=10,pady=5)
mainloop()
到此這篇關(guān)于python自動生成sql語句的腳本的文章就介紹到這了,更多相關(guān)python自動生成sql語句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!