首页 > 数据库频道-数据库维护 > MYSQL数据库 > 正文
Python&MySQL&PyQt
2016-04-13     我来说两句       作者:黑帽网
   我要投稿

环境:
Python2.7+MySQL5.6+PyQt4

 

//


简单的图形化界面操作数据库:模仿写一个类似于navicat完成可视化检索功能的界面,利用PyQt可视化快速完成界面设计,这点比Tkinter设计要简单;

1.利用PyQt进行界面设计:
打开PyQt,新建,选择Main Window,选择左端的控件拖动完成界面的设计,选中元素,可以从右侧查看元素的信息以及修改其属性值,下面贴上设计图:

/
在设计界面中选中元素双击,可以修改其属性名,添加text,改变显示字符格式等;

2.设计完成后,保存为.ui文件,接下来将其转换为.py文件:
打开cmd:进入PyQt安装目录:
Lib/site-packages/PyQt4/uic>
键入命令格式:
python pyuic.py -o .py文件目录+文件名 .ui文件目录+文件名

/
我保存的.ui文件放在D盘根目录中,生成的.py文件命名为nihao0.py

/

3.打开Python运行.py,查看界面
在原有的代码基础上添加下列代码:

import sysclass MyWindow(QtGui.QMainWindow,Ui_MainWindow):#PyQt生产的是一个叫做Ui_MainWindow的类,只需要放在一个框架下画出来即可;    def __init__(self):        super(MyWindow,self).__init__()        self.setupUi(self)if __name__=="__main__":    app=QtGui.QApplication(sys.argv)#建立一个app,把框架放在这个app中执行    app.aboutToQuit.connect(app.deleteLater)    myshow=MyWindow()    myshow.show()    sys.exit(app.exec_())#也可以写成app.exec_() sys.exit(0),前者是循环整个界面,后者是退出app
运行结果:
/
这时我们得到了一个我们想得到的界面,只需要往这个界面中添加各种事件即可;

 

4.动态生成SQL语句——准备
新建mysql,和表格students,表格的属性为
DDL为:

CREATE TABLE `students` (  `Sid` char(10) NOT NULL,  `Sname` char(10) DEFAULT NULL,  `Sage` int(3) DEFAULT NULL,  `Ssex` char(4) DEFAULT NULL,  `Sclass` char(7) DEFAULT NULL,  `Sdept` char(10) DEFAULT NULL,  `Saddr` char(40) DEFAULT NULL,  PRIMARY KEY (`Sid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
先写好句柄:
sqlsting="select * from students where "
然后写一个clicked()事件,点击查询的时候,根据用户输入动态构造SQL语句
使用到的方法主要有:
是否选择复选框:checked()
读入输入:text()

 

5.动态生成SQL语句——字符串构造
构造技巧:
是否需要加and:如果前面已经有了原子公式,需要增加and,否则不加,使用一个flag即可;
是否需要加引号:字符型需要加引号,数字不需要加;
数字类型需要先转换为字符类型:当为数字类型转换为字符型str(),判断数据类型type(),其实可以不写,因为从界面返回的都是QtString类型的数据;
判断什么时候使用Like(字符串匹配问题%):字符串方法.find("%"),返回值是第一个%出现的位置,如果%不存在,则返回-1;

6.界面显示问题
self.sql_out = QtGui.QTextBrowser(self.centralwidget):显示框使用方法:
.setText(str):清除原来数据,显示新的字符串
.append(str):在末尾添加新的字符串
self.result_out = QtGui.QTableWidget(self.centralwidget):表格框使用方法:
.setItem(i,j,newItem):将newItem放在第i行,第j列,注意ij都从0开始;
newItem的声明格式为newItem = QtGui.QTableWidgetItem(str),即在(i,j)位置显示str
注意QTableWidget只能显示字符串,不能显示数字,所有在显示数据前,需要判断是否为数字:
解决方法:type()以及str()

7.数据库连接问题:
详细在Python&MySQL中已经讲述清除,可以作参考

8.贴上Python代码及运行结果:

# -*- coding: utf-8 -*-# Form implementation generated from reading ui file 'D:/nihao.ui'## Created: Fri Apr 01 15:28:59 2016#      by: PyQt4 UI code generator 4.11.3## WARNING! All changes made in this file will be lost!from PyQt4 import QtCore, QtGuiimport sysimport MySQLdbtry:    _fromUtf8 = QtCore.QString.fromUtf8except AttributeError:    def _fromUtf8(s):        return stry:    _encoding = QtGui.QApplication.UnicodeUTF8    def _translate(context, text, disambig):        return QtGui.QApplication.translate(context, text, disambig, _encoding)except AttributeError:    def _translate(context, text, disambig):        return QtGui.QApplication.translate(context, text, disambig)class Ui_MainWindow(object):    def setupUi(self, MainWindow):                self.conn=MySQLdb.connect(            host='localhost',            user='root',            passwd='',            db='mysql',            #            charset='utf8',        )        self.cur=self.conn.cursor()                self.sqlstring="select * from students where "        MainWindow.setObjectName(_fromUtf8("MainWindow"))        MainWindow.resize(760, 440)        self.centralwidget = QtGui.QWidget(MainWindow)        self.centralwidget.setObjectName(_fromUtf8("centralwidget"))        self.frame = QtGui.QFrame(self.centralwidget)        self.frame.setGeometry(QtCore.QRect(10, 10, 491, 121))        self.frame.setFrameShape(QtGui.QFrame.StyledPanel)        self.frame.setFrameShadow(QtGui.QFrame.Raised)        self.frame.setObjectName(_fromUtf8("frame"))        self.check_Sid = QtGui.QCheckBox(self.frame)        self.check_Sid.setGeometry(QtCore.QRect(20, 10, 71, 16))        self.check_Sid.setObjectName(_fromUtf8("check_Sid"))        self.check_Sage = QtGui.QCheckBox(self.frame)        self.check_Sage.setGeometry(QtCore.QRect(20, 70, 71, 16))        self.check_Sage.setObjectName(_fromUtf8("check_Sage"))        self.check_Sname = QtGui.QCheckBox(self.frame)        self.check_Sname.setGeometry(QtCore.QRect(20, 40, 71, 16))        self.check_Sname.setObjectName(_fromUtf8("check_Sname"))        self.check_Ssex = QtGui.QCheckBox(self.frame)        self.check_Ssex.setGeometry(QtCore.QRect(20, 100, 71, 16))        self.check_Ssex.setObjectName(_fromUtf8("check_Ssex"))        self.Sid = QtGui.QLineEdit(self.frame)        self.Sid.setGeometry(QtCore.QRect(90, 10, 113, 16))        self.Sid.setObjectName(_fromUtf8("Sid"))        self.Sname = QtGui.QLineEdit(self.frame)        self.Sname.setGeometry(QtCore.QRect(90, 40, 113, 16))        self.Sname.setObjectName(_fromUtf8("Sname"))        self.first_Sage = QtGui.QLineEdit(self.frame)        self.first_Sage.setGeometry(QtCore.QRect(90, 70, 41, 16))        self.first_Sage.setObjectName(_fromUtf8("first_Sage"))        self.Ssex = QtGui.QLineEdit(self.frame)        self.Ssex.setGeometry(QtCore.QRect(90, 100, 113, 16))        self.Ssex.setObjectName(_fromUtf8("Ssex"))        self.label = QtGui.QLabel(self.frame)        self.label.setGeometry(QtCore.QRect(140, 70, 16, 16))        self.label.setObjectName(_fromUtf8("label"))        self.last_Sage = QtGui.QLineEdit(self.frame)        self.last_Sage.setGeometry(QtCore.QRect(160, 70, 41, 16))        self.last_Sage.setObjectName(_fromUtf8("last_Sage"))        self.check_Sdept = QtGui.QCheckBox(self.frame)        self.check_Sdept.setGeometry(QtCore.QRect(270, 40, 71, 16))        self.check_Sdept.setObjectName(_fromUtf8("check_Sdept"))        self.Sdept = QtGui.QLineEdit(self.frame)        self.Sdept.setGeometry(QtCore.QRect(340, 40, 113, 16))        self.Sdept.setObjectName(_fromUtf8("Sdept"))        self.Sclass = QtGui.QLineEdit(self.frame)        self.Sclass.setGeometry(QtCore.QRect(340, 10, 113, 16))        self.Sclass.setObjectName(_fromUtf8("Sclass"))        self.check_Sclass = QtGui.QCheckBox(self.frame)        self.check_Sclass.setGeometry(QtCore.QRect(270, 10, 71, 16))        self.check_Sclass.setObjectName(_fromUtf8("check_Sclass"))        self.Saddr = QtGui.QLineEdit(self.frame)        self.Saddr.setGeometry(QtCore.QRect(340, 70, 113, 16))        self.Saddr.setObjectName(_fromUtf8("Saddr"))        self.check_Saddr = QtGui.QCheckBox(self.frame)        self.check_Saddr.setGeometry(QtCore.QRect(270, 70, 71, 16))        self.check_Saddr.setObjectName(_fromUtf8("check_Saddr"))        self.find = QtGui.QPushButton(self.frame)        self.find.setGeometry(QtCore.QRect(380, 100, 75, 21))        self.find.setObjectName(_fromUtf8("find"))        self.sql_out = QtGui.QTextBrowser(self.centralwidget)        self.sql_out.setGeometry(QtCore.QRect(10, 140, 740, 61))        self.sql_out.setObjectName(_fromUtf8("sql_out"))        self.result_out = QtGui.QTableWidget(self.centralwidget)        self.result_out.setEditTriggers(QtGui.QAbstractItemView.NoEditTriggers)#不可编辑表格         self.result_out.setGeometry(QtCore.QRect(10, 210, 740, 171))        self.result_out.setObjectName(_fromUtf8("result_out"))        self.result_out.setColumnCount(7)        self.result_out.setRowCount(10)        self.result_out.resizeColumnsToContents()        self.result_out.resizeRowsToContents()        item = QtGui.QTableWidgetItem()        self.result_out.setHorizontalHeaderItem(0, item)        item = QtGui.QTableWidgetItem()        self.result_out.setHorizontalHeaderItem(1, item)        item = QtGui.QTableWidgetItem()        self.result_out.setHorizontalHeaderItem(2, item)        item = QtGui.QTableWidgetItem()        self.result_out.setHorizontalHeaderItem(3, item)        item = QtGui.QTableWidgetItem()        self.result_out.setHorizontalHeaderItem(4, item)        item = QtGui.QTableWidgetItem()        self.result_out.setHorizontalHeaderItem(5, item)        item = QtGui.QTableWidgetItem()        self.result_out.setHorizontalHeaderItem(6, item)        self.result_out.horizontalHeader().setDefaultSectionSize(100)        self.result_out.horizontalHeader().setMinimumSectionSize(25)        self.result_out.verticalHeader().setDefaultSectionSize(30)        self.pushButton_2 = QtGui.QPushButton(self.centralwidget)        self.pushButton_2.setGeometry(QtCore.QRect(675, 390, 75, 21))        self.pushButton_2.setObjectName(_fromUtf8("pushButton_2"))        MainWindow.setCentralWidget(self.centralwidget)        self.menubar = QtGui.QMenuBar(MainWindow)        self.menubar.setGeometry(QtCore.QRect(0, 0, 509, 23))        self.menubar.setObjectName(_fromUtf8("menubar"))        MainWindow.setMenuBar(self.menubar)        self.statusbar = QtGui.QStatusBar(MainWindow)        self.statusbar.setObjectName(_fromUtf8("statusbar"))        MainWindow.setStatusBar(self.statusbar)        self.retranslateUi(MainWindow)        QtCore.QMetaObject.connectSlotsByName(MainWindow)    def retranslateUi(self, MainWindow):        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow", None))        self.check_Sid.setText(_translate("MainWindow", "学号", None))        self.check_Sage.setText(_translate("MainWindow", "年龄自", None))        self.check_Sname.setText(_translate("MainWindow", "姓名", None))        self.check_Ssex.setText(_translate("MainWindow", "性别", None))        self.label.setText(_translate("MainWindow", "到", None))        self.check_Sdept.setText(_translate("MainWindow", "系", None))        self.check_Sclass.setText(_translate("MainWindow", "班级", None))        self.check_Saddr.setText(_translate("MainWindow", "地址", None))        self.find.setText(_translate("MainWindow", "查询", None))        self.sql_out.setText(self.sqlstring)        item = self.result_out.horizontalHeaderItem(0)        item.setText(_translate("MainWindow", "Sid", None))        item = self.result_out.horizontalHeaderItem(1)        item.setText(_translate("MainWindow", "Sname ", None))        item = self.result_out.horizontalHeaderItem(2)        item.setText(_translate("MainWindow", "Sage", None))        item = self.result_out.horizontalHeaderItem(3)        item.setText(_translate("MainWindow", "Ssex", None))        item = self.result_out.horizontalHeaderItem(4)        item.setText(_translate("MainWindow", "Sclass", None))        item = self.result_out.horizontalHeaderItem(5)        item.setText(_translate("MainWindow", "Sdept", None))        item = self.result_out.horizontalHeaderItem(6)        item.setText(_translate("MainWindow", "Saddr", None))        self.pushButton_2.setText(_translate("MainWindow", "退出", None))    def mousePressEvent(self,event):        if event.self.find()==QtCore.Qt.LeftButton:            print "nihao"        def buttonTest(self):        temp_sqlstring=self.sqlstring        is_first = True        if self.check_Sid.isChecked():            mystr = self.Sid.text()            if is_first:                is_first = False                if mystr.find("%")==-1:                    temp_sqlstring += "Sid = '" + self.Sid.text() + "'"                else:                    temp_sqlstring += "Sid like '" + self.Sid.text() + "'"            else:                if mystr.find("%")==-1:                    temp_sqlstring += " and Sid = '" + self.Sid.text() + "'"                else:                    temp_sqlstring += " and Sid like '" + self.Sid.text() + "'"                if self.check_Sname.isChecked():            if is_first:                mystr =self.Sname.text()                is_first = False                if mystr.find("%")==-1:                    temp_sqlstring += "Sname = '" + self.Sname.text() + "'"                else:                    temp_sqlstring += "Sname like '" + self.Sname.text() + "'"            else:                if mystr.find("%")==-1:                    temp_sqlstring += " and Sname = '" + self.Sname.text() + "'"                else:                    temp_sqlstring += " and Sname like '" + self.Sname.text() + "'"                if self.check_Sage.isChecked():            if is_first:                is_first = False                temp_sqlstring += "Sage >= " + self.first_Sage.text() +/                " and Sage <= " + self.last_Sage.text()            else:                temp_sqlstring += " and Sage >= " + self.first_Sage.text() +/                " and Sage <= " + self.last_Sage.text()                if self.check_Ssex.isChecked():            if is_first:                is_first = False                temp_sqlstring += "Ssex = '" + self.Ssex.text() + "'"            else:                temp_sqlstring += " and Ssex = '" + self.Ssex.text() + "'"                if self.check_Sclass.isChecked():            if is_first:                mystr = self.Sclass.text()                is_first = False                if mystr.find("%")==-1:                    temp_sqlstring += "Sclass = '" + self.Sclass.text() + "'"                else:                    temp_sqlstring += "Sclass like '" + self.Sclass.text() + "'"            else:                if mystr.find("%")==-1:                    temp_sqlstring += " and Sclass = '" + self.Sclass.text() + "'"                else:                    temp_sqlstring += " and Sclass like '" + self.Sclass.text() + "'"                            if self.check_Sdept.isChecked():            if is_first:                mystr = self.Sdept.text()                is_first = False                if mystr.find("%")==-1:                    temp_sqlstring += "Sdept = '" + self.Sdept.text() + "'"                else:                    temp_sqlstring += "Sdept like '" + self.Sdept.text() + "'"            else:                if mystr.find("%")==-1:                    temp_sqlstring += " and Sdept = '" + self.Sdept.text() + "'"                else:                    temp_sqlstring += " and Sdept like '" + self.Sdept.text() + "'"                if self.check_Saddr.isChecked():            if is_first:                mystr = self.Saddr.text()                is_first = False                if mystr.find("%")==-1:                    temp_sqlstring += "Saddr = '" + self.Saddr.text() + "'"                else:                    temp_sqlstring +=" and Saddr like '" + self.Saddr.text() + "'"            else:                if mystr.find("%")==-1:                    temp_sqlstring += " and Saddr = '" + self.Saddr.text() + "'"                else:                    temp_sqlstring +=" and Saddr like '" + self.Saddr.text() + "'"                self.result_out.clearContents()#每一次查询时清除表格中信息        if not(is_first):            self.cur.execute(temp_sqlstring)            k=0            for i in self.cur:                w=0                for j in i:                    if type(j)==long:                        newItem = QtGui.QTableWidgetItem(str(j))                    else:                        newItem = QtGui.QTableWidgetItem(j)                    self.result_out.setItem(k,w,newItem)                    w += 1                k +=1                    self.sql_out.setText("")        self.sql_out.append(temp_sqlstring)        print "find button pressed"        def buttonExit(self):        self.conn.commit()        self.cur.close()        self.conn.close()        self.close()        def keyPressEvent(self, e):        if e.key() == QtCore.Qt.Key_Escape:            self.buttonExit()        class MyWindow(QtGui.QMainWindow,Ui_MainWindow):    def __init__(self):        super(MyWindow,self).__init__()        self.setupUi(self)        self.connect(self.find, QtCore.SIGNAL('clicked()'), self.buttonTest)        self.connect(self.pushButton_2,QtCore.SIGNAL('clicked()'),self.buttonExit)if __name__=="__main__":    app=QtGui.QApplication(sys.argv)    app.aboutToQuit.connect(app.deleteLater)    myshow=MyWindow()    myshow.show()    sys.exit(app.exec_())    #app.exec_()    #sys.exit(0)
注:运行前首先确保已经在MySQL中存在my以及students表格;
运行结果:
/
由于原来设计的界面比较小,后来改变了各个控件的长宽;
这样就完成的简单的界面化动态构造SQL语句,并将结果返回界面显示功能;
点击收藏到自己的收藏夹!回本站首页
您对本文章有什么意见或着疑问吗?请到论坛讨论您的关注和建议是我们前行的参考和动力  
上一篇:Python&amp;MySQL
下一篇:最新用python来操作mysql完全解析
相关文章
图文推荐
排行
热门
oracle
Sysbase
其他
关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 中国黑帽网--致力于做最好的网络安全技术学习网站 。