软件世界网 购物 网址 三丰软件 | 小说 美女秀 图库大全 游戏 笑话 | 下载 开发知识库 新闻 开发 图片素材
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
移动开发 架构设计 编程语言 Web前端 互联网
开发杂谈 系统运维 研发管理 数据库 云计算 Android开发资料
  软件世界网 -> 数据库 -> Python&MySQL&PyQt -> 正文阅读

[数据库]Python&MySQL&PyQt


环境:
Python2.7+MySQL5.6+PyQt4
[img]http://img.blog.csdn.net/20160402170949781?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast[img]http://img.blog.csdn.net/20160402170840297?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
简单的图形化界面操作数据库:模仿写一个类似于navicat完成可视化检索功能的界面,利用PyQt可视化编程快速完成界面设计,这点比Tkinter设计要简单;
1.利用PyQt进行界面设计:
打开PyQt,新建,选择Main Window,选择左端的控件拖动完成界面的设计,选中元素,可以从右侧查看元素的信息以及修改其属性值,下面贴上设计图:
[img]http://img.blog.csdn.net/20160402171935129?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
在设计界面中选中元素双击,可以修改其属性名,添加text,改变显示字符格式等;
2.设计完成后,保存为.ui文件,接下来将其转换为.py文件:
打开cmd:进入PyQt安装目录:
Lib\site-packages\PyQt4\uic>
键入命令格式:
python pyuic.py -o .py文件目录+文件名 .ui文件目录+文件名
[img]http://img.blog.csdn.net/20160402172829789?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
我保存的.ui文件放在D盘根目录中,生成的.py文件命名为nihao0.py
[img]http://img.blog.csdn.net/20160402173110446?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
3.打开Python运行.py,查看界面
在原有的代码基础上添加下列代码:
import sys

class 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
运行结果:
[img]http://img.blog.csdn.net/20160402174254202?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
这时我们得到了一个我们想得到的界面,只需要往这个界面中添加各种事件即可;
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, QtGui
import sys
import MySQLdb

try:
    _fromUtf8 = QtCore.QString.fromUtf8
except AttributeError:
    def _fromUtf8(s):
        return s

try:
    _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中存在mysql数据库以及students表格;
运行结果:
[img]http://img.blog.csdn.net/20160402183200674?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
由于原来设计的界面比较小,后来改变了各个控件的长宽;
这样就完成的简单的界面化动态构造SQL语句,并将结果返回界面显示功能;
......显示全文...
    点击查看全文


上一篇文章           查看所有文章
2016-04-03 20:45:40  
数据库 最新文章
Python&MySQL&PyQt
最新用python来操作mysql完全解析
mongodb的安装详解
1.PDO简介
《MySQL必知必会学习笔记》:高级联结
【翻译自mos文章】怎么对Microsoft(Office)
MyCAT全局表描述及示例
ocp
关于SQL数据表存储过程表名前缀换成dbo代码
数据库调优教程(二)慢查询数据准备
360图书馆 软件开发资料 文字转语音 购物精选 软件下载 美食菜谱 新闻资讯 电影视频 小游戏 Chinese Culture 股票 租车
生肖星座 三丰软件 视频 开发 短信 中国文化 网文精选 搜图网 美图 阅读网 多播 租车 短信 看图 日历 万年历 2018年1日历
2018-1-23 4:08:36
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  软件世界网 --