在MySQL数据库表列中插入带有PyQt5的图像文件

Question

我一直很难在MySQL表中插入图像。我已经使用QFileDialog来浏览和显示图像。但是，保存后，每当我单击检查表列中的BLOB时，都不会保存任何内容。我试着做了一些研究，我意识到在SQL命令中将图像作为文本文件插入是错误的。请老师们，重构我的以下代码并实现插入图像的目标的最佳实践是什么

class TestReg(QWidget):

    def __init__(self):
        super().__init__()
        self.ui = Ui_TestRegForm()
        self.ui.setupUi(self)

        #Calling browseImage
        self.ui.browse_btn.clicked.connect(self.browseImage)

        #Calling insertPers
        self.ui.save_btn.clicked.connect(self.insertPers)

    #Browses person's picture and displays using label called image_label
    def browseImage(self):
        file_name = QFileDialog.getOpenFileName(self, 'Open File', 'c:\\', 'Image Files (*.png *.jpg *gif)')
        image_path = file_name[0]
        pixmap = QPixmap(image_path)
        self.ui.image_label.setPixmap(QPixmap(pixmap))

    #Inserts person into database
    def insertPers(self):
        try:
            con = MySQLdb.connect(host="localhost", user="root", password="", database="somedb")
            with con:
                cur = con.cursor()
                cur.execute("INSERT INTO persons(name, photo)" "VALUES('%s', '%s')" % (''.join(self.ui.name_edit.text()), ''.join(self.ui.image_label.text()))
                con.commit()
                self.displayDialog(QMessageBox.Information, "Registration", "Person has been added successfully")
        except MySQLdb.Error as e:
            self.displayDialog(QMessageBox.Warning, "Registration", str(e))
        except TypeError as e:
            self.displayDialog(QMessageBox.Warning, "Registration", str(e))
        except ValueError as e:
            self.displayDialog(QMessageBox.Warning, "Registration", str(e))

Answer 1

您不应该连接变量来构建查询，而应该使用占位符，否则代码将容易受到SQL注入攻击。另一方面，必须使用QBuffer作为中介将QPixmap（而不是文本）转换为字节：

con = MySQLdb.connect(host="localhost", user="root", password="", database="somedb")
with con:
    cur = con.cursor()
    name = self.ui.name_edit.text()
    buff = QBuffer()
    buff.open(QIODevice.WriteOnly)
    pixmap = QPixmap(self.ui.image_label.pixmap())
    pixmap.save(buff, "PNG")
    binary_img = buff.data().toBase64().data()
    cur.execute("INSERT INTO persons(name, photo) VALUES (%s, %s)", (name, binary_img))
    con.commit()