在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()