python 图片展示及数据库存储demo PyQt_pyqt选择图像并存入数据库

软件功能

点击添加图片，打开本地文件夹，选中的图片显示在页面上，同时添加进数据库。按键盘PgUp和PgDn键可以切换上一张或下一张图片。

用到的知识

• pyqt designer 生成文件转为代码
• pyqt基本组件的使用
• 连接mysql数据库、数据库增、查操作

文件架构

软件界面

效果图：

使用PyQt Designer拖拽制作好界面，保存为ui.ui文件。在cmd中输入命令：

pyuic5 -x [ui文件名.ui] -o [py文件名.py]
1

最终生成的ui.py：

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

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

from PyQt5 import QtCore, QtGui, QtWidgets


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(392, 316)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.verticalLayout = QtWidgets.QVBoxLayout(self.centralwidget)
        self.verticalLayout.setObjectName("verticalLayout")
        self.label = QtWidgets.QLabel(self.centralwidget)
        self.label.setObjectName("label")
        self.verticalLayout.addWidget(self.label)
        self.label_pic = QtWidgets.QLabel(self.centralwidget)
        self.label_pic.setObjectName("label_pic")
        self.verticalLayout.addWidget(self.label_pic)
        self.line = QtWidgets.QFrame(self.centralwidget)
        self.line.setFrameShape(QtWidgets.QFrame.HLine)
        self.line.setFrameShadow(QtWidgets.QFrame.Sunken)
        self.line.setObjectName("line")
        self.verticalLayout.addWidget(self.line)
        self.horizontalLayout = QtWidgets.QHBoxLayout()
        self.horizontalLayout.setObjectName("horizontalLayout")
        self.btn_add = QtWidgets.QPushButton(self.centralwidget)
        self.btn_add.setObjectName("btn_add")
        self.horizontalLayout.addWidget(self.btn_add)
        spacerItem = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)
        self.horizontalLayout.addItem(spacerItem)
        self.btn_del = QtWidgets.QPushButton(self.centralwidget)
        self.btn_del.setObjectName("btn_del")
        self.horizontalLayout.addWidget(self.btn_del)
        self.verticalLayout.addLayout(self.horizontalLayout)
        self.verticalLayout.setStretch(0, 1)
        self.verticalLayout.setStretch(1, 8)
        self.verticalLayout.setStretch(3, 1)
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 392, 18))
        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.label.setText(_translate("MainWindow", "按键盘左右键切换图片"))
        self.label_pic.setText(_translate("MainWindow", "显示图片"))
        self.btn_add.setText(_translate("MainWindow", "添加图片"))
        self.btn_del.setText(_translate("MainWindow", "删除"))



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

创建数据库

数据库结构：

注意！ 图片以相对路径储存在数据库中，而不是以二进制的方式储存，否则当数据增多时，查询会十分缓慢！

逻辑代码

关于代码的一点解说：

• 问题1：退出软件后id重新初始化，存储位置发生改变。
  解决办法：可以select语句获取整列id存成一个list，然后切片访问list。每次对数据库更新同时也更新list。
• 问题2：使用键盘查询图片时容易发生越界。
  解决办法：if语句，查询游标超过id数组长度的时候弹框提示不行。

import sys, os
import time, random
import shutil
import cv2
import numpy as np
import pymysql as ml
from PyQt5.QtWidgets import QMainWindow, QApplication
from PyQt5.Qt import QFileDialog, QMessageBox
from PyQt5.QtGui import QPixmap, QImage
from PyQt5.QtCore import Qt
from ui import Ui_MainWindow


class MyMainWindows(QMainWindow, Ui_MainWindow):

    def __init__(self):
        super(MyMainWindows, self).__init__()
        self.setupUi(self)
        self.btn_add.clicked.connect(self.openPic)
        self.btn_del.clicked.connect(self.deletePic)
        # 初始化图片id

        self.idList = []
        self.getCurrentIdList()  # 删除的Id会缺失
        self.id = self.idList[-1]
        print('init id:', self.id)

        self.read_cur = 0  # 查询图片的游标

        self.root_pic = r'D:\Python\workplace\database\database'  # 存放上传的图片
        if not os.path.exists(self.root_pic):
            os.makedirs(self.root_pic)

    def openPic(self):
        print('open dir')
        imgName, imgType = QFileDialog.getOpenFileName(self, '选择图片', r'D:\aaaaaa', 'Images(*.png *.jpg)')
        jpg = QPixmap(imgName).scaled(self.label_pic.width(), self.label_pic.height())
        self.label_pic.setPixmap(jpg)
        # 图片命名规则是 年月日时分秒+四位数随机数
        picName = time.strftime("%Y%m%d%H%M%S", time.localtime()) + str(random.randint(1000,9999)) + '.jpg'
        save_path = os.path.join(self.root_pic, picName)
        try:
            shutil.copyfile(imgName, save_path)
        except Exception as e:
            print('无权限')

        # 把相对路径插入数据库
        self.id += 1
        self.idList.append(self.id)
        self.insertPic(self.id, picName)
        print(imgName, '------', picName)

    def deletePic(self):
        # delete 也要更新self.idList
        print('del pic')

    def getCurrentIdList(self):
        db = ml.connect(host='localhost', user='root', password='123456', db='picture', port=3306)
        cur = db.cursor()
        sql = 'select id from pic_table'
        try:
            cur.execute(sql)
            result = cur.fetchall()
            for num in result:
                self.idList.append(num[0])
            db.commit()
        except Exception as e:
            raise e
        finally:
            db.close()

    def insertPic(self, id, picName):
        db = ml.connect(host='localhost', user='root', password='123456', db='picture', port=3306)
        cur = db.cursor()
        sql = 'insert into pic_table(id, pic) values(%s,%s)'
        try:
            cur.execute(sql, (id, picName))
            db.commit()
        except Exception as e:
            raise e
        finally:
            db.close()

    def readPic(self, id):
        db = ml.connect(host='localhost', user='root', password='123456', db='picture', port=3306)
        cur = db.cursor()
        sql = 'select pic from pic_table where id = %s'
        try:
            cur.execute(sql, id)
            result = cur.fetchone()
            result = result[0]
            print('open----', result)
            pic_path = os.path.join(self.root_pic, result)
            jpg = QPixmap(pic_path).scaled(self.label_pic.width(), self.label_pic.height())
            self.label_pic.setPixmap(jpg)
            db.commit()
        except Exception as e:
            raise e
        finally:
            db.close()

    def keyPressEvent(self, event):
        # 没有写越界的代码
        key = event.key()
        if key == Qt.Key_PageDown:
            self.read_cur += 1
            if self.read_cur > len(self.idList) - 1:
                self.read_cur -= 1
                QMessageBox.about(self, '越界', '没有下一张了')
            readId = self.idList[self.read_cur]
            self.readPic(readId)
            print('read id:', '→', readId)
        elif key == Qt.Key_PageUp:
            self.read_cur -= 1
            if self.read_cur < 0:
                self.read_cur += 1
                QMessageBox.about(self, '越界', '没有上一张了')
            readId = self.idList[self.read_cur]
            self.readPic(readId)
            print('read id:', readId, '←')




if __name__ == '__main__':
    app = QApplication(sys.argv)
    myWin = MyMainWindows()
    myWin.show()
    sys.exit(app.exec_())
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130

运行效果