python 选择不同数据库环境执行SQL文件

在有很多SQL文件，或者很多SQL语句的时候，如果手动每个文件执行会很麻烦；另外有的时候SQL语句的执行会影响用户的使用效果，需要在晚上没有用户使用网站的情况下执行SQL语句，也就是晚上定时执行SQL文件；

需要实现的功能：

1.弹框选择数据库环境

2.展示展示SQL文件列表

3.可以选择SQL文件，选中后，替换默认展示的SQL文件，并选中当前SQL文件

4.确定执行SQL文件，并提示是否执行

5.执行完SQL文件后，弹框提示执行成功，和失败条数

6.并且记录错误日志，错误日志包含时间，SQL文件地址，SQL语句，SQL错误详情

源码（包含窗体部分，以及改动后的可以做定时任务的脚本，pip需要安装的包含[有些包没有用到，可以删除]）：

SQL文件执行工具

实现效果图：

当前存在一个问题，就是这个窗体没有用到多线程，导致在执行SQL文件的时候，窗体控件无法操作；

 

另外在此基础上进行一些改动，改动后的python脚本可以定时执行SQL文件；

关于定时执行任务设置可以参考这篇文章：https://blog.csdn.net/wwy11/article/details/51100432

 

代码分为两部分，也可以下载源码：

第一部分：初始化窗体，以及各种需要用到的空间

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254

#自定义一个窗口类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文件

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82

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 = []