【PyQt5之连接MySQL数据库以及查询获取数据】

最新推荐文章于 2024-04-06 13:00:41 发布

zy215215

最新推荐文章于 2024-04-06 13:00:41 发布

阅读量8.1k

点赞数 6

文章标签： python

原文链接：https://blog.csdn.net/weixin_43350361/article/details/106607441

版权

最近做界面终于需要用到数据库了，本科学过的基本忘光噜，所以临时快速入门了一下！先记录一波！步骤呢，首先mysql以及pyqt5肯定是需要的，然后就是安装python连接mysql的库pymysql，在cmd中用pip install pymysql安装就好了（注意：pymysql是python3才开始用的库）。接下来我会以一个例子为例，来介绍如何实现连接mysql以及通过按钮进行简单查询并获取数据库。

1、画界面

用Qt Designer画一个示例窗口界面，添加如下两个表格以及增删改查的按钮，再生成py文件，然后新建一个主函数文件用来显示界面。这一节只介绍在pyqt5的界面窗口实现查询获取语句，更新、删除与插入数据在下一节再介绍。
在这里插入图片描述

2、连接数据库

首先需要导入pymysql库，因为每次连接数据库用完后都需要及时关闭，所以我选择在每个按钮的槽函数中都进行一次数据库的连接和关闭，点击按钮就连接数据库，按钮中的事件完了后就关闭数据库。以【读取内容】按钮为例，连接和关闭数据库的代码如下，代码挺好理解，connect连接所需要的参数依次为mysql数据库的“主机地址”、“端口号”、“用户名”、“密码”和想要连接的“数据库名”。cursor是用来执行sql语句的，之后会用到。点击该按钮之后，系统就会自动连接数据库，因为中间没有其它操作，所以会立马又自动关闭掉！

import pymysql

# 【读取内容】按钮功能
def pushB_read_Clicked(self):
conn = pymysql.connect(host=‘localhost’, port=3306, user=‘root’, password=“199632”, db=“test”)
cur = conn.cursor()
# 执行sql语句和实现事件、、、
cur.close()
conn.close()

3、查询并获取数据库的数据

下图是我在数据库“test”中建的表“tab1”（使用的是mysql的可视化软件SQLyog），共有两列，首先对它进行查询，然后将查询到的数据显示在窗口中的表格上，这一步比较关键！
在这里插入图片描述
代码依旧是实现在【读取内容】按钮的槽函数中，如下所示，查询并获取id为1的那一行数据，但是要注意，获取到的data数据是字典格式（即二维数组），用print打印可以看出，需要使用data[i][j]的方式才能得到想要的单个数据。然后就是将id和name分别显示到界面中的表格，其中因为表格中的id为int型，所以这里需要转换成string型，效果如下所示：

        # 【读取按钮】功能
        self.pushB_read.clicked.connect(self.pushB_read_Clicked)
<span class="token comment"># 【读取内容】按钮功能</span>
<span class="token keyword">def</span> <span class="token function">pushB_read_Clicked</span><span class="token punctuation">(</span>self<span class="token punctuation">)</span><span class="token punctuation">:</span>
    conn <span class="token operator">=</span> pymysql<span class="token punctuation">.</span>connect<span class="token punctuation">(</span>host<span class="token operator">=</span><span class="token string">'localhost'</span><span class="token punctuation">,</span> port<span class="token operator">=</span><span class="token number">3306</span><span class="token punctuation">,</span> user<span class="token operator">=</span><span class="token string">'root'</span><span class="token punctuation">,</span> password<span class="token operator">=</span><span class="token string">"199632"</span><span class="token punctuation">,</span> db<span class="token operator">=</span><span class="token string">"test"</span><span class="token punctuation">)</span>
    cur <span class="token operator">=</span> conn<span class="token punctuation">.</span>cursor<span class="token punctuation">(</span><span class="token punctuation">)</span>
    <span class="token comment"># 查询的sql语句</span>
    sql <span class="token operator">=</span> <span class="token string">"SELECT * FROM tab1 WHERE id=1;"</span>
    cur<span class="token punctuation">.</span>execute<span class="token punctuation">(</span>sql<span class="token punctuation">)</span>
    <span class="token comment"># 获取查询到的数据，是以字典的形式存储的，所以读取需要使用data[i][j]下标定位</span>
    data <span class="token operator">=</span> cur<span class="token punctuation">.</span>fetchall<span class="token punctuation">(</span><span class="token punctuation">)</span>
    <span class="token comment"># 打印测试</span>
    <span class="token keyword">print</span><span class="token punctuation">(</span>data<span class="token punctuation">)</span>
    <span class="token keyword">print</span><span class="token punctuation">(</span>data<span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
    <span class="token comment"># 将id和name显示到界面表格上</span>
    self<span class="token punctuation">.</span>tableWidget<span class="token punctuation">.</span>setItem<span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">,</span> QtWidgets<span class="token punctuation">.</span>QTableWidgetItem<span class="token punctuation">(</span><span class="token builtin">str</span><span class="token punctuation">(</span>data<span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
    self<span class="token punctuation">.</span>tableWidget<span class="token punctuation">.</span>setItem<span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> QtWidgets<span class="token punctuation">.</span>QTableWidgetItem<span class="token punctuation">(</span>data<span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
    cur<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
    conn<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>

print()打印测试结果：
在这里插入图片描述
点击【读取内容】按钮后，界面显示效果，这里只简单介绍显示单行数据，多行的方法都是一样的！

4、完整代码

主函数的完整代码如下，界面生成的文件就不放了，在ui界面画好后生成py文件即可！

import sys
from PyQt5 import QtGui, QtWidgets
from PyQt5.QtCore import *
from PyQt5.QtWidgets import QApplication, QMainWindow, QHeaderView
from untitled import Ui_MainWindow
import pymysql

class MyMainWindow(QMainWindow, Ui_MainWindow):
def init(self):
super(MyMainWindow, self).init()
self.setupUi(self)
# 表格扩充拉伸
self.tableWidget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.tableWidget_2.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# 【读取按钮】功能
self.pushB_read.clicked.connect(self.pushB_read_Clicked)

<span class="token comment"># 【读取内容】按钮功能</span>
<span class="token keyword">def</span> <span class="token function">pushB_read_Clicked</span><span class="token punctuation">(</span>self<span class="token punctuation">)</span><span class="token punctuation">:</span>
    conn <span class="token operator">=</span> pymysql<span class="token punctuation">.</span>connect<span class="token punctuation">(</span>host<span class="token operator">=</span><span class="token string">'localhost'</span><span class="token punctuation">,</span> port<span class="token operator">=</span><span class="token number">3306</span><span class="token punctuation">,</span> user<span class="token operator">=</span><span class="token string">'root'</span><span class="token punctuation">,</span> password<span class="token operator">=</span><span class="token string">"199632"</span><span class="token punctuation">,</span> db<span class="token operator">=</span><span class="token string">"test"</span><span class="token punctuation">)</span>
    cur <span class="token operator">=</span> conn<span class="token punctuation">.</span>cursor<span class="token punctuation">(</span><span class="token punctuation">)</span>
    <span class="token comment"># 查询的sql语句</span>
    sql <span class="token operator">=</span> <span class="token string">"SELECT * FROM tab1 WHERE id=1;"</span>
    cur<span class="token punctuation">.</span>execute<span class="token punctuation">(</span>sql<span class="token punctuation">)</span>
    <span class="token comment"># 获取查询到的数据，是以字典的形式存储的，所以读取需要使用data[i][j]下标定位</span>
    data <span class="token operator">=</span> cur<span class="token punctuation">.</span>fetchall<span class="token punctuation">(</span><span class="token punctuation">)</span>
    <span class="token comment"># 打印测试</span>
    <span class="token keyword">print</span><span class="token punctuation">(</span>data<span class="token punctuation">)</span>
    <span class="token keyword">print</span><span class="token punctuation">(</span>data<span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
    <span class="token comment"># 将id和name显示到界面表格上</span>
    self<span class="token punctuation">.</span>tableWidget<span class="token punctuation">.</span>setItem<span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">,</span> QtWidgets<span class="token punctuation">.</span>QTableWidgetItem<span class="token punctuation">(</span><span class="token builtin">str</span><span class="token punctuation">(</span>data<span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
    self<span class="token punctuation">.</span>tableWidget<span class="token punctuation">.</span>setItem<span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> QtWidgets<span class="token punctuation">.</span>QTableWidgetItem<span class="token punctuation">(</span>data<span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
    cur<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
    conn<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>

if name == “main”:
QApplication.setAttribute(Qt.AA_EnableHighDpiScaling)
app = QApplication(sys.argv)
win = MyMainWindow()
win.show()
sys.exit(app.exec())

zy215215

关注

6
点赞
踩
79

收藏

觉得还不错? 一键收藏
0
评论
【PyQt5之连接MySQL数据库以及查询获取数据】

最近做界面终于需要用到数据库了，本科学过的基本忘光噜，所以临时快速入门了一下！先记录一波！步骤呢，首先mysql以及pyqt5肯定是需要的，然后就是安装python连接mysql的库pymysql，在cmd中用pip install pymysql安装就好了（注意：pymysql是p...
复制链接

扫一扫