pyqt5做的页面与MySQL数据库交互

最新推荐文章于 2024-10-09 03:45:00 发布

大青衣123

最新推荐文章于 2024-10-09 03:45:00 发布

阅读量1.1w

点赞数 8

本文链接：https://blog.csdn.net/weixin_44282031/article/details/96474285

版权

第一次项目专栏收录该内容

3 篇文章 0 订阅

订阅专栏

第一次接触python和数据库，简单记录一下历程。
1.安装python、pyqt5和MySQL
常规sql语句的操作见 mysql数据库操作
2.python（2.7版本）与MySQL数据库的连接需要 MySQLdb 模块
下载连接Linux平台可以访问：https://pypi.python.org/pypi/MySQL-python
执行以下命令安装：

$ gunzip MySQL-python-1.2.2.tar.gz
$ tar -xvf MySQL-python-1.2.2.tar
$ cd MySQL-python-1.2.2
$ python setup.py build
$ python setup.py install

安装结束以后import一下，看看是否成功。
3.python与数据库连接

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb

def open1():
  db = MySQLdb.connect("localhost", "root", "123", "jishu", charset='utf8')
  cursor = db.cursor()
  sql = "SELECT number FROM shuliang "   
  cursor.execute(sql)
  results = cursor.fetchall()
  for row in results:
     a = row[0]    
     print (a)    
     return a   #获取返回值，方便以后的调用

def open2():
  db = MySQLdb.connect("localhost", "root", "123", "jishu", charset='utf8')
  cursor = db.cursor()
  sql = "UPDATE shuliang SET number = number+1 "  
  cursor.execute(sql)
  db.commit()

def open3():
  db = MySQLdb.connect("localhost", "root", "123", "jishu", charset='utf8')
  cursor = db.cursor()
  sql = "UPDATE shuliang SET number = 0 "  
  cursor.execute(sql)
  db.commit()
 
if __name__ == '__main__':
    open1()
    open2()
    open3()

open1()函数为查询语句，查询的是我的数据库中jishu这个表中的shuliang的number值。
open2()是更新语句，对number值做加1操作。
open3()也是更新语句，作用是把number值清零。
4.页面设计
为了测试页面与MySQL数据库的交互，页面是简单做的。通过designer生产基础页面数据
在这里插入图片描述
通过designer生成的是.ui后缀的文件，需要转化成.py文件。
具体做法在包含ui文件的目录下，通过pyqt5指令完成。

pyuic5 -o chaxun.py chaxun.ui

chaxun.py是你要生成的py文件，chaxun.ui要转化的文件。打开转化后的py文件代码如下：

from PyQt5 import QtCore, QtGui, QtWidgets

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 600)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.pushButton = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton.setGeometry(QtCore.QRect(190, 120, 99, 27))
        self.pushButton.setObjectName("pushButton")
        self.pushButton_2 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_2.setGeometry(QtCore.QRect(190, 180, 99, 27))
        self.pushButton_2.setObjectName("pushButton_2")
        self.pushButton_3 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_3.setGeometry(QtCore.QRect(190, 240, 99, 27))
        self.pushButton_3.setObjectName("pushButton_3")
        self.lineEdit = QtWidgets.QLineEdit(self.centralwidget)
        self.lineEdit.setGeometry(QtCore.QRect(380, 180, 113, 27))
        self.lineEdit.setObjectName("lineEdit")
        self.label = QtWidgets.QLabel(self.centralwidget)
        self.label.setGeometry(QtCore.QRect(410, 140, 67, 17))
        self.label.setObjectName("label")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 31))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.pushButton.setText(_translate("MainWindow", "查询"))
        self.pushButton_2.setText(_translate("MainWindow", "加1"))
        self.pushButton_3.setText(_translate("MainWindow", "清零"))
        self.label.setText(_translate("MainWindow", "数据库"))

后面加上以下语句，在python运行就可以看到设计的页面了。

if __name__ == "__main__":
    print ('time:',time1)
    app = QtWidgets.QApplication(sys.argv)  
    MainWindow = QtWidgets.QMainWindow()    
    ui = Ui_MainWindow()                 
    ui.setupUi(MainWindow)                  
    #ui.setupFunction()             #还没有添加操作函数      
    MainWindow.show()                    
    sys.exit(app.exec_())

5.页面与数据库交互
因为交互文件用到了之前的数据库操作语句，所以直接那个文件

from PyQt5 import QtCore, QtGui, QtWidgets
import sys
import MySQLdb
import time
import chaxun      #引用的数据库操作语句

添加 ui.setupFunction()

    def setupFunction(self):
        self.pushButton.clicked.connect(self.chaxun)
        self.pushButton_2.clicked.connect(self.jia)
	    self.pushButton_3.clicked.connect(self.qing)

    def chaxun(self):
	    f=chaxun.open1()
	    self.lineEdit.setText(str(f))

    def jia(self):
        chaxun.open2()

    def qing(self):
        chaxun.open3()

这样就完成了。
显示的结果：

数据库里面的number数值：

完整代码如下：
chaxun.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb

def open1():
  db = MySQLdb.connect("localhost", "root", "123", "jishu", charset='utf8')
  cursor = db.cursor()
  sql = "SELECT number FROM shuliang "   
  cursor.execute(sql)
  results = cursor.fetchall()
  for row in results:
     a = row[0]    
     print (a)
     return a

def open2():
  db = MySQLdb.connect("localhost", "root", "123", "jishu", charset='utf8')
  cursor = db.cursor()
  sql = "UPDATE shuliang SET number = number+1 "  
  cursor.execute(sql)
  db.commit()

def open3():
  db = MySQLdb.connect("localhost", "root", "123", "jishu", charset='utf8')
  cursor = db.cursor()
  sql = "UPDATE shuliang SET number = 0 "  
  cursor.execute(sql)
  db.commit()
       
if __name__ == '__main__':
    open1()
    open2()
    open3()

chaxun4.py

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

# Form implementation generated from reading ui file 'chaxun4.ui'
#
# Created by: PyQt5 UI code generator 5.9.2
#
# WARNING! All changes made in this file will be lost!

from PyQt5 import QtCore, QtGui, QtWidgets
import sys
import MySQLdb
import time
import chaxun

time1 = time.strftime('%Y.%m.%d.%s',time.localtime(time.time()))



class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 600)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.pushButton = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton.setGeometry(QtCore.QRect(190, 120, 99, 27))
        self.pushButton.setObjectName("pushButton")
        self.pushButton_2 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_2.setGeometry(QtCore.QRect(190, 180, 99, 27))
        self.pushButton_2.setObjectName("pushButton_2")
        self.pushButton_3 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_3.setGeometry(QtCore.QRect(190, 240, 99, 27))
        self.pushButton_3.setObjectName("pushButton_3")
        self.lineEdit = QtWidgets.QLineEdit(self.centralwidget)
        self.lineEdit.setGeometry(QtCore.QRect(380, 180, 113, 27))
        self.lineEdit.setObjectName("lineEdit")
        self.label = QtWidgets.QLabel(self.centralwidget)
        self.label.setGeometry(QtCore.QRect(410, 140, 67, 17))
        self.label.setObjectName("label")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 31))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.pushButton.setText(_translate("MainWindow", "查询"))
        self.pushButton_2.setText(_translate("MainWindow", "加1"))
        self.pushButton_3.setText(_translate("MainWindow", "清零"))
        self.label.setText(_translate("MainWindow", "数据库"))

    def setupFunction(self):
        self.pushButton.clicked.connect(self.chaxun)
        self.pushButton_2.clicked.connect(self.jia)
	    self.pushButton_3.clicked.connect(self.qing)

    def chaxun(self):
	    f=chaxun.open1()
	    self.lineEdit.setText(str(f))

    def jia(self):
        chaxun.open2()

    def qing(self):
        chaxun.open3()

if __name__ == "__main__":
    print ('time:',time1)
    app = QtWidgets.QApplication(sys.argv)  # 创建一个QApplication，也就是你要开发的软件app
    MainWindow = QtWidgets.QMainWindow()    # 创建一个QMainWindow，用来装载你需要的各种组件、控件
    ui = Ui_MainWindow()                    # ui是Ui_MainWindow()类的实例化对象
    ui.setupUi(MainWindow)                  # 执行类中的setupUi方法，方法的参数是第二步中创建的QMainWindow
    ui.setupFunction()                      # 执行类中的setupFunction方法  
    MainWindow.show()                       # 执行QMainWindow的show()方法，显示这个QMainWindow
    sys.exit(app.exec_())                   # 使用exit()或者点击关闭按钮退出QApplication