在有很多SQL文件,或者很多SQL语句的时候,如果手动每个文件执行会很麻烦;另外有的时候SQL语句的执行会影响用户的使用效果,需要在晚上没有用户使用网站的情况下执行SQL语句,也就是晚上定时执行SQL文件;
需要实现的功能:
1.弹框选择数据库环境
2.展示展示SQL文件列表
3.可以选择SQL文件,选中后,替换默认展示的SQL文件,并选中当前SQL文件
4.确定执行SQL文件,并提示是否执行
5.执行完SQL文件后,弹框提示执行成功,和失败条数
6.并且记录错误日志,错误日志包含时间,SQL文件地址,SQL语句,SQL错误详情
源码(包含窗体部分,以及改动后的可以做定时任务的脚本,pip需要安装的包含[有些包没有用到,可以删除]):
实现效果图:
当前存在一个问题,就是这个窗体没有用到多线程,导致在执行SQL文件的时候,窗体控件无法操作;
另外在此基础上进行一些改动,改动后的python脚本可以定时执行SQL文件;
代码分为两部分,也可以下载源码:
第一部分:初始化窗体,以及各种需要用到的空间
#自定义一个窗口类MyFrame
class MyFrame(wx.Frame):
path = os.path.split(os.path.realpath(__file__))[0] + "/SQL"
def __init__(self):
super().__init__(parent=None, title="选择服务器环境执行SQL语句", size=(800, 600))
self.SetBackgroundColour(wx.Colour(224, 224, 224))
self.Center() #设置窗口居中
#放一个面板,用于布局其他控件
panel = wx.Panel(parent=self)
hbox1 = wx.BoxSizer(wx.HORIZONTAL)
#创建静态文本
statictext = wx.StaticText(
panel,
label='选择执行环境:',
# size=(150, 111),
)
list1 = [
'开发环境', '外网测试环境', "外网正式环境"
]
self.ch1 = wx.ComboBox(
panel,
# -1,
value='开发环境',
pos=(50, 170),
size=(150, -1),
choices=list1,
style=wx.CB_READONLY)
#添加事件处理
self.Bind(wx.EVT_COMBOBOX, self.on_combobox, self.ch1)
hbox1.Add(
statictext,
# 1,
flag=wx.LEFT | wx.RIGHT | wx.FIXED_MINSIZE,
border=5)
hbox1.Add(self.ch1,
1,
flag=wx.LEFT | wx.RIGHT | wx.FIXED_MINSIZE,
border=5)
but1 = wx.Button(panel, 1, '确 定')
self.Bind(wx.EVT_BUTTON, self.OnSubmit, but1, id=1)
hbox1.Add(but1,
1,
flag=wx.LEFT | wx.RIGHT | wx.FIXED_MINSIZE,
border=5)
hbox13 = wx.BoxSizer(wx.HORIZONTAL)
text11 = wx.StaticText(
panel,
label='选择SQL文件:',
# size=(100, 50),
)
hbox13.Add(text11, 1, flag=wx.ALIGN_LEFT, border=3)
butFile = wx.Button(panel, 1, u"选择要执行的SQL文件")
self.Bind(wx.EVT_BUTTON, self.OnSubmit, butFile, id=2)
hbox13.Add(butFile, 9, flag=wx.ALIGN_LEFT, border=3)
hbox3 = wx.BoxSizer(wx.HORIZONTAL)
text1 = wx.StaticText(
panel,
label='SQL文件:',
# size=(150, 111),
)
hbox3.Add(
text1,
# 1,
flag=wx.LEFT | wx.RIGHT | wx.FIXED_MINSIZE,
border=5)
text2 = wx.StaticText(
panel,
label='(',
# size=(150, 111),
)
hbox3.Add(
text2,
# 1,
flag=wx.LEFT | wx.RIGHT | wx.FIXED_MINSIZE,
border=5)
#复选框(是否全选)
self.cb3 = wx.CheckBox(panel, label='全选', pos=(10, 70))
self.Bind(wx.EVT_CHECKBOX, self.on_chkboxall, self.cb3)
hbox3.Add(
self.cb3,
# 1,
flag=wx.LEFT | wx.RIGHT | wx.FIXED_MINSIZE,
border=5)
text3 = wx.StaticText(
panel,
label=')',
# size=(150, 111),
)
hbox3.Add(
text3,
# 1,
flag=wx.LEFT | wx.RIGHT | wx.FIXED_MINSIZE,
border=0)
# sql文件列表
files = os.listdir(self.path)
fileList = []
for f in files:
# 添加文件
if os.path.splitext(f)[1].lower() == ".sql":
fileList.append(self.path + "\\" + f)
self.listBox = wx.CheckListBox(panel, -1, (20, 20), (180, 1520),
fileList, wx.LB_MULTIPLE)
self.Bind(wx.EVT_LISTBOX, self.One_Play, self.listBox)
hbox2 = wx.BoxSizer(wx.HORIZONTAL)
hbox2.Add(self.listBox,
1,
flag=wx.LEFT | wx.RIGHT | wx.FIXED_MINSIZE,
border=5)
#添加事件处理
# self.Bind(wx.EVT_CHOICE,self.on_choice,ch2)
vbox = wx.BoxSizer(wx.VERTICAL)
vbox.Add(hbox1, proportion=0, flag=wx.ALL | wx.EXPAND, border=5)
vbox.Add(hbox13, proportion=0, flag=wx.ALL | wx.EXPAND, border=5)
vbox.Add(hbox3, proportion=0, flag=wx.ALL | wx.EXPAND, border=5)
vbox.Add(hbox2, proportion=0, flag=wx.ALL | wx.EXPAND, border=3)
panel.SetSizer(vbox)
# 选择SQL文件
def OnButton1(self, event):
# filesFilter = "Dicom (*.sql)|*.sql|" "All files (*.*)|*.*"
filesFilter = "sqlserver (*.sql)|*.sql"
fileDialog = wx.FileDialog(self,
message="多文件选择",
wildcard=filesFilter,
style=wx.FD_OPEN | wx.FD_MULTIPLE)
dialogResult = fileDialog.ShowModal()
if dialogResult != wx.ID_OK:
return
paths = fileDialog.GetPaths()
self.listBox.Clear()
for path in paths:
self.listBox.Append(path)
# 选择的文件默认全部选中
self.cb3.SetValue(True)
itemsLen = self.listBox.GetCount()
for i in range(itemsLen):
self.listBox.Check(i, check=True)
# 全选
def on_chkboxall(self, event):
checkBoxSelected = event.GetEventObject()
ischk = checkBoxSelected.IsChecked()
itemsLen = self.listBox.GetCount()
for i in range(itemsLen):
self.listBox.Check(i, check=ischk)
def One_Play(self, event):
listbox = event.GetEventObject()
seleIndex = event.Selection
ischk = self.listBox.IsChecked(seleIndex)
if ischk == True:
self.listBox.Check(seleIndex, check=False)
else:
self.listBox.Check(seleIndex, check=True)
def on_combobox(self, event):
print("选择{0}".format(event.GetString()))
def OnSubmit(self, event):
btn = event.GetEventObject()
txt = btn.LabelText
if txt == "选择要执行的SQL文件":
filesFilter = "sqlserver (*.sql)|*.sql"
fileDialog = wx.FileDialog(self,
message="多文件选择",
wildcard=filesFilter,
style=wx.FD_OPEN | wx.FD_MULTIPLE)
dialogResult = fileDialog.ShowModal()
if dialogResult != wx.ID_OK:
return
paths = fileDialog.GetPaths()
self.listBox.Clear()
for path in paths:
self.listBox.Append(path)
# 选择的文件默认全部选中
self.cb3.SetValue(True)
itemsLen = self.listBox.GetCount()
for i in range(itemsLen):
self.listBox.Check(i, check=True)
elif txt == "确 定":
chvalue = self.ch1.GetValue()
if chvalue == "开发环境":
self.msg = SQLServer(server="127.0.0.1",
port=0,
user="test",
password="Test321",
database="test")
elif chvalue == "外网测试环境":
self.msg = SQLServer(server="127.0.0.1",
port=0,
user="test",
password="Test321",
database="test")
elif chvalue == "外网正式环境":
self.msg = SQLServer(server="127.0.0.1",
port=0,
user="test",
password="Test321",
database="test")
# selectCount = len(self.checkListBox.GetChecked())
# sqlfiles = self.listBox.GetSelections()
chkItems = self.listBox.CheckedItems
# loop = asyncio.get_event_loop()
for item in chkItems:
# 获取SQL文件路径
seleStr = self.listBox.Items[item]
self.msg.GetSqlInfo(seleStr)
# loop.run_until_complete(self.msg.GetSqlInfo(seleStr))
# t = multiprocessing.Process(target=self.msg.GetSqlInfo,
# args=(seleStr,))
# t.daemon=True
# t.start()
# th = threading.Thread(target=self.msg.GetSqlInfo,
# args=(seleStr, ))
# th.start()
# wx.CallAfter(self.msg.GetSqlInfo, seleStr)
time.sleep(1) #每次执行一个文件后暂停1秒
# print(seleStr)
#打印错误日志
# print(self.msg.errMsg)
if len(self.msg.errMsg)>0:
box = wx.MessageDialog(
None, '有%s条SQL语句执行错误,具体情况请查看错误日志' % len(self.msg.errMsg),
'提示', wx.OK | wx.STAY_ON_TOP)
answer=box.ShowModal()
box.SetWindowStyle(wx.OK | wx.STAY_ON_TOP)
box.Destroy()
else:
box = wx.MessageDialog(None, '所有SQL语句执行成功', '提示',
wx.OK | wx.STAY_ON_TOP)
answer = box.ShowModal()
box.SetWindowStyle(wx.OK | wx.STAY_ON_TOP)
box.Destroy()
# time.sleep(2)
wx.Exit()
# def on_choice(self,event):
# print("选择{0}".format(event.GetString()))
第二部分:读取SQL文件,并且执行SQL文件
class SQLServer:
errMsg = []
# filename 指定日志存放文件,level 指定logging级别
logging.basicConfig(filename=os.path.split(os.path.realpath(__file__))[0] +"/sqlErrorInfo.log", level=logging.INFO)
def __init__(self, server,port, user, password, database):
# 类的构造函数,初始化DBC连接信息
self.server = server
self.port = port
self.user = user
self.password = password
self.database = database
def __GetConnect(self):
# 得到数据库连接信息,返回conn.cursor()
if not self.database:
raise (NameError, "没有设置数据库信息")
self.conn = pymssql.connect(server=self.server,
port=self.port,
user=self.user,
password=self.password,
database=self.database)
cur = self.conn.cursor()
if not cur:
raise (NameError, "连接数据库失败") # 将DBC信息赋值给cur
else:
return cur
def ExecQuery(self, sql):
'''
执行查询语句
返回一个包含tuple的list,list是元素的记录行,tuple记录每行的字段数值
'''
cur = self.__GetConnect()
cur.execute(sql) # 执行查询语句
result = cur.fetchall() # fetchall()获取查询结果
# 查询完毕关闭数据库连接
self.conn.close()
return result
def Exec(self, sql,filename):
try:
cur = self.__GetConnect()
cur.execute(
sql) # I have experienced problems when executing utf-8
self.conn.close()
except Exception as e:
errorMsg = '[当前时间:%s]SQL文件地址:%s \nSQL语句:%s \n错误信息:%s \n' % (time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),filename, sql, e)
self.errMsg.append(errorMsg)
logging.info(errorMsg)
logging.info("------------------------------------------------------------------\n")
# 获取SQL文件中的SQL语句
def GetSqlInfo(self, filename):
with open(filename, 'r', encoding='gbk', errors='ignore') as f:
script = f.read() # or whatever its encoding is
script = re.sub(r'\/\*.*?\*\/', '', script,
flags=re.DOTALL) # remove multiline comment
script = re.sub(r'--.*$', '', script,
flags=re.MULTILINE) # remove single line comment
sql = []
do_execute = False
for line in script.split(u'\n'):
line = line.strip()
if not line:
continue
elif line.upper() == u'GO':
do_execute = True
else:
sql.append(line)
do_execute = line.endswith(u';')
if do_execute and filter(None,
sql): # ignore if only blank lines
sqlInfo = u'\n'.join(sql) #.encode("cp1252")
# sql.append(sqlInfo)
self.Exec(
sqlInfo, filename
) # I have experienced problems when executing utf-8
do_execute = False
sql = []