PySide開發MySql遠程備份工具

MySql數據庫安裝在機房，而工作人員日常辦公的地方距離機房有段距離，且不在同一樓層。出入機房不是很方便。就想著能否給這些人員開發一個圖形化的備份MySql數據庫的小工具？
使用組件如下：
(1)Python
(2)PySide
(3)mysqldump
其實mysql已經提供了一個mysqldump.exe來做mysql數據庫的備份，也支持遠程操作，但因為是命令行形式的，對于普通的工作人員來說使用起來就非常不方便了，這個小工具的原理就是使用PySide對mysqldump.exe做一封裝，提供GUI接口。
里面值得注意的是Thread和subprocess的結合，具體詳見如下代碼：

View Code

########################################################################
class BackupThread(QThread):
"""備份數據庫線程"""
dataReady = Signal(object)
config = ApplicationConfig()
#----------------------------------------------------------------------
def run(self):
"""具體執行部分"""
self.dataReady.emit(u"正在執行，請稍后......")
#加載配置信息
self.config.load()
#組織備份數據庫所需要的命令
filestamp= time.strftime("%Y-%m-%d")

filename = "%s-%s.sql" % (self.config.databasename, filestamp)
filename = os.path.join(self.config.backupdir, filename)
mysqldumpfile = os.path.join(os.getcwd(), "mysqldump.exe")
command= "%s -u %s -p%s -h %s -e --opt -c %s" % (mysqldumpfile, self.config.user, self.config.password, self.config.remoteIp, self.config.databasename)
print command
pipe = subprocess.Popen(command, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
#print pipe.stdout.readlines()
#promptMsg= "".join(pipe.stdout.readlines())
#print "promptMsg is:", promptMsg
fp= open(filename, "w")
item = None
for line in pipe.stdout:
fp.writelines(line)
self.dataReady.emit(line)
fp.close()
completeMsg= "";
#取出最后一個item，判斷里面的內容是否包含
if item is not None:
text= item.text()
if text.find("Dump completed") > 0:
print "Completed"
completeMsg = u"開始壓縮，請稍后......"
#計算壓縮后的文件名
compressedfilename= os.path.splittext(filename)[0] + ".zip"
if self.compress(filename, compressedfilename):
completeMsg = u"壓縮文件出錯，請檢查！"
else:
completeMsg = u"操作已完成，請檢查！"
else:
completeMsg = u"操作過程中出現錯誤，請檢查！"
else:
completeMsg = u"操作過程中出現錯誤，請檢查！"

self.dataReady.emit(completeMsg)

全部代碼如下所示:
(1)ApplicationConfig.py

View Code

#-*-coding:utf-8-*-

import ConfigParser

########################################################################
class ApplicationConfig:
"""
程序相關配置信息
"""

#----------------------------------------------------------------------
def __init__(self):
"""Constructor"""
self.remoteIp = "127.0.0.1"
self.remotePort= "3306"
self.user = "root"
self.password = ""
self.databasename= ""
self.backupdir = "c:\\"
self.config = ConfigParser.ConfigParser()

#----------------------------------------------------------------------
def load(self):
"""
加載配置信息
"""
try:
self.config.read(r".\ApplicationConfig.cfg")
self.remoteIp = self.config.get("mysql", "remoteip")
self.remotePort = self.config.get("mysql", "remoteport")
self.user = self.config.get("mysql", "user")
self.password = self.config.get("mysql", "password")
self.databasename = self.config.get("mysql", "databasename")
self.backupdir = self.config.get("mysql", "backupdir")
except Exception as ex:
print "Some exception occured when invoke load(), exception message is ", ex, " please check it !"

#----------------------------------------------------------------------
def save(self):
"""
保存配置信息
"""
self.config.read(r".\ApplicationConfig.cfg")
if "mysql" not in self.config.sections():
self.config.add_section("mysql")
self.config.set("mysql", "remoteip", self.remoteIp)
self.config.set("mysql", "remoteport", self.remotePort)
self.config.set("mysql", "user", self.user)
self.config.set("mysql", "password", self.password)
self.config.set("mysql", "databasename", self.databasename)
self.config.set("mysql", "backupdir", self.backupdir)
fp= open(r".\ApplicationConfig.cfg", "w")
self.config.write(fp)
#----------------------------------------------------------------------
def __str__(self):
"""
返回相應的字符串表示
"""
description = "remoteip : \t" + self.remoteIp + "\n" + \
"user : \t" + self.user + "\n" +\
"password : \t"+ self.password+ "\n"+ \
"databasename \t" + self.databasename + "\n" + \
"backupdir : \t" + self.backupdir + "\n"
return description

(2)MainForm.py

View Code

# -*- coding: utf-8 -*-

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

from PySide.QtCore import *
from PySide.QtGui import *
import os
import time
import subprocess
import zipfile

from ApplicationConfig import ApplicationConfig

class ConfigurationPage(QWidget):
def __init__(self, parent=None):
super(ConfigurationPage, self).__init__(parent)
self.configGroup = QGroupBox(u"相關配置信息")

self.remoteIPLabel = QLabel(u"數據庫所在IP地址:")
self.editRemoteIP = QLineEdit(u"數據庫所在IP地址")
self.remotePortLabel= QLabel(u"使用端口:")
self.editPort = QLineEdit(u"使用端口")
self.userLabel = QLabel(u"用戶名:")
self.editUser = QLineEdit(u"用戶名")
self.passwordLabel= QLabel(u"密碼:")
self.editPassword= QLineEdit(u"密碼")
self.dabasenameLabel= QLabel(u"數據庫名:")
self.editDatabaseName= QLineEdit(u"數據庫名")
self.backupdirLabel = QLabel(u"備份文件存放目錄:")
self.editBackupDir = QLineEdit(u"備份文件存放目錄")
self.browseButton = QPushButton(u"瀏覽")
self.browseButton.clicked.connect(self.onBrowse)

self.paremeterLayout= QGridLayout();
self.paremeterLayout.addWidget(self.remoteIPLabel, 0, 0)
self.paremeterLayout.addWidget(self.editRemoteIP, 0, 1, 1, 2)
self.paremeterLayout.addWidget(self.remotePortLabel, 1, 0)
self.paremeterLayout.addWidget(self.editPort, 1, 1, 1, 2)
self.paremeterLayout.addWidget(self.userLabel, 2, 0)
self.paremeterLayout.addWidget(self.editUser, 2, 1, 1, 2)
self.paremeterLayout.addWidget(self.passwordLabel, 3, 0)
self.paremeterLayout.addWidget(self.editPassword, 3, 1, 1, 2)
self.paremeterLayout.addWidget(self.dabasenameLabel, 4, 0)
self.paremeterLayout.addWidget(self.editDatabaseName, 4, 1, 1, 2)
self.paremeterLayout.addWidget(self.backupdirLabel, 5, 0)
self.paremeterLayout.addWidget(self.editBackupDir, 5, 1)
self.paremeterLayout.addWidget(self.browseButton, 5, 2)

self.btnSave = QPushButton(u"保存")
self.btnSave.setMinimumHeight(50)
self.btnSave.clicked.connect(self.onSave)

self.configGroup.setLayout(self.paremeterLayout)

self.mainLayout= QVBoxLayout()
self.mainLayout.addWidget(self.configGroup)
self.mainLayout.addSpacing(20)
self.mainLayout.addWidget(self.btnSave)
self.mainLayout.addStretch(1)

self.setLayout(self.mainLayout)

self.config = ApplicationConfig()
self.doInitialize()
#----------------------------------------------------------------------
def onSave(self):
"""保存處理"""
try:
self.config.remoteIp = self.editRemoteIP.text()
self.config.remotePort = self.editPort.text()
self.config.user = self.editUser.text()
self.config.password = self.editPassword.text()
self.config.databasename = self.editDatabaseName.text()
self.config.backupdir = self.editBackupDir.text()
self.config.save()
except:
print "some exceptions occured when invoke onSave() method, please check it!"
#print unicode(self.config.remoteIp)
#----------------------------------------------------------------------
def onBrowse(self):
"""選擇目錄"""
directory= QFileDialog.getExistingDirectory(self, u"查找備份目錄",
QDir.currentPath())
if directory:
self.editBackupDir.setText(directory)

#----------------------------------------------------------------------
def doInitialize(self):
"""初始化相關參數"""
self.config.load()
self.editRemoteIP.setText(unicode(self.config.remoteIp))
self.editPort.setText(unicode(self.config.remotePort))
self.editUser.setText(unicode(self.config.user))
self.editPassword.setText(unicode(self.config.password))
self.editDatabaseName.setText(unicode(self.config.databasename))
self.editBackupDir.setText(unicode(self.config.backupdir))

########################################################################
class BackupThread(QThread):
"""備份數據庫線程"""
dataReady = Signal(object)
config = ApplicationConfig()
#----------------------------------------------------------------------
def run(self):
"""具體執行部分"""
self.dataReady.emit(u"正在執行，請稍后......")
#加載配置信息
self.config.load()
#組織備份數據庫所需要的命令
filestamp= time.strftime("%Y-%m-%d")

filename = "%s-%s.sql" % (self.config.databasename, filestamp)
filename = os.path.join(self.config.backupdir, filename)
mysqldumpfile = os.path.join(os.getcwd(), "mysqldump.exe")
command= "%s -u %s -p%s -h %s -e --opt -c %s" % (mysqldumpfile, self.config.user, self.config.password, self.config.remoteIp, self.config.databasename)
print command
pipe = subprocess.Popen(command, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
#print pipe.stdout.readlines()
#promptMsg= "".join(pipe.stdout.readlines())
#print "promptMsg is:", promptMsg
fp= open(filename, "w")
item = None
for line in pipe.stdout:
fp.writelines(line)
self.dataReady.emit(line)
fp.close()
completeMsg= "";
#取出最后一個item，判斷里面的內容是否包含
if item is not None:
text= item.text()
if text.find("Dump completed") > 0:
print "Completed"
completeMsg = u"開始壓縮，請稍后......"
#計算壓縮后的文件名
compressedfilename= os.path.splittext(filename)[0] + ".zip"
if self.compress(filename, compressedfilename):
completeMsg = u"壓縮文件出錯，請檢查！"
else:
completeMsg = u"操作已完成，請檢查！"
else:
completeMsg = u"操作過程中出現錯誤，請檢查！"
else:
completeMsg = u"操作過程中出現錯誤，請檢查！"

self.dataReady.emit(completeMsg)

########################################################################
class BackupPage(QWidget):
""""""

#----------------------------------------------------------------------
def __init__(self, parent=None):
"""Constructor"""
super(BackupPage, self).__init__(parent)
self.backupButton = QPushButton(u"備份數據庫")
self.backupButton.setMinimumHeight(50)
self.backupButton.clicked.connect(self.doBackupOperation)
self.labelPrompt = QLabel(u"提示信息") #執行結果顯示
self.listPrompt = QListWidget()
self.labelPrompt.setWordWrap(True)
self.layout = QVBoxLayout()
self.layout.addStretch()
self.layout.addWidget(self.backupButton)
#self.layout.addSpacing(20)
self.layout.addWidget(self.labelPrompt)
self.layout.addWidget(self.listPrompt)
self.layout.addStretch()
self.config = ApplicationConfig()
self.thread = BackupThread() #備份線程
self.thread.dataReady.connect(self.updateUI, Qt.QueuedConnection)
self.setLayout(self.layout)
#----------------------------------------------------------------------
def doBackupOperation(self):
"""執行備份數據庫的任務"""
#self.labelPrompt.setText(u"正在執行，請稍后......")
##加載配置信息
#self.config.load()
##組織備份數據庫所需要的命令
#filestamp= time.strftime("%Y-%m-%d")

#filename = "%s-%s.sql" % (self.config.databasename, filestamp)
#filename = os.path.join(self.config.backupdir, filename)
#mysqldumpfile = os.path.join(os.getcwd(), "mysqldump.exe")
#command= "%s -u %s -p%s -h %s -e --opt -c %s" % (mysqldumpfile, self.config.user, self.config.password, self.config.remoteIp, self.config.databasename)
#print command
#pipe = subprocess.Popen(command, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
#fp= open(filename, "w")
#item = None
#for line in pipe.stdout:
#fp.writelines(line)
#item = QListWidgetItem(line)
#self.listPrompt.addItem(item)
#if self.listPrompt.count() > 0:
#self.listPrompt.setCurrentRow(self.listPrompt.count() - 1)
#pass
#fp.close()
#self.listPrompt.setFocus()
#completeMsg= "";
##取出最后一個item，判斷里面的內容是否包含
#if item is not None:
#text= item.text()
#if text.find("Dump completed") > 0:
#print "Completed"
#completeMsg = u"開始壓縮，請稍后......"
##計算壓縮后的文件名
#compressedfilename= os.path.splittext(filename)[0] + ".zip"
#if self.compress(filename, compressedfilename):
#completeMsg = u"壓縮文件出錯，請檢查！"
#else:
#completeMsg = u"操作已完成，請檢查！"
#else:
#completeMsg = u"操作過程中出現錯誤，請檢查！"
#else:
#completeMsg = u"操作過程中出現錯誤，請檢查！"
#self.labelPrompt.setText(completeMsg)

self.thread.start() #啟動線程
#----------------------------------------------------------------------
def updateUI(self, data):
"""更新UI部分處理"""
item = QListWidgetItem(data)
self.listPrompt.addItem(data)
self.listPrompt.setFocus()
#----------------------------------------------------------------------
def compress(self, infilename, dstfilename, level = 9):
"""壓縮"""
result= False;
try:
zfile = zipfile.ZipFile(dstfilename, "w")
zfile.write(infilename, os.path.split(infilename)[1])
zfile.close()
result = True
except:
print "some error occured when invoke (), please check it!"
result = False

return result;

########################################################################
class HelpPage(QWidget):
""""""

#----------------------------------------------------------------------
def __init__(self, parent=None):
"""Constructor"""
super(HelpPage, self).__init__(parent)
self.helpLabel = QLabel(u"使用說明:\n\t"
u"（1）備份數據前請檢查相關配置信息是否正確\n\t"
u"（2）點擊備份數據庫按鈕，等待本分操作完成\n\t"
u"（3）請留意備份過程中是否有錯誤信息\n\t")

self.layout = QVBoxLayout()
self.layout.addSpacing(10)
self.layout.addWidget(self.helpLabel)
self.layout.addStretch(1)

self.setLayout(self.layout)

class MainDialog(QDialog):
#----------------------------------------------------------------------
def __init__(self, parent=None):
"""初始化函數
"""
super(MainDialog, self).__init__(parent)

#Create Widgets

self.contentsWidget = QListWidget()
self.contentsWidget.setViewMode(QListView.IconMode)
self.contentsWidget.setIconSize(QSize(96, 84))
self.contentsWidget.setMovement(QListView.Static)
self.contentsWidget.setMaximumWidth(128 + 10)
self.contentsWidget.setMinimumHeight((84 + 12 ) * 4)
self.contentsWidget.setSpacing(12)

self.pagesWidget = QStackedWidget()
self.pagesWidget.addWidget(ConfigurationPage())
self.pagesWidget.addWidget(BackupPage())
self.pagesWidget.addWidget(HelpPage())

self.closeButton = QPushButton(u"退出")

self.createIcons()
self.contentsWidget.setCurrentRow(0)

self.closeButton.clicked.connect(self.close)

self.horizontalLayout = QHBoxLayout()
self.horizontalLayout.addWidget(self.contentsWidget)
self.horizontalLayout.addWidget(self.pagesWidget, 1)

self.buttonsLayout = QHBoxLayout()
self.buttonsLayout.addStretch(1)
self.buttonsLayout.addWidget(self.closeButton)

self.mainLayout = QVBoxLayout()
self.mainLayout.addLayout(self.horizontalLayout)
self.mainLayout.addSpacing(12)
self.mainLayout.addLayout(self.buttonsLayout)

self.setLayout(self.mainLayout)

#Add button
self.setWindowTitle(u"數據庫備份")

def changePage(self, current, previous):
if not current:
current = previous

self.pagesWidget.setCurrentIndex(self.contentsWidget.row(current))

def createIcons(self):
configButton = QListWidgetItem(self.contentsWidget)
configButton.setIcon(QIcon('./images/config.png'))
configButton.setText(u"相關配置信息")
configButton.setTextAlignment(Qt.AlignHCenter)
configButton.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled)

updateButton = QListWidgetItem(self.contentsWidget)
updateButton.setIcon(QIcon('./images/update.png'))
updateButton.setText(u"備份")
updateButton.setTextAlignment(Qt.AlignHCenter)
updateButton.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled)

queryButton = QListWidgetItem(self.contentsWidget)
queryButton.setIcon(QIcon('./images/query.png'))
queryButton.setText(u"使用說明")
queryButton.setTextAlignment(Qt.AlignHCenter)
queryButton.setFlags(Qt.ItemIsSelectable | Qt.ItemIsEnabled)

self.contentsWidget.currentItemChanged.connect(self.changePage)

if __name__ == "__main__":
app = QApplication(sys.argv)
form = MainDialog()
form.show()
sys.exit(app.exec_())

(3)配置文件ApplicationConfig.cfg

View Code