python调用sql文件_python 选择不同数据库环境执行SQL文件

在有很多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 = []