主页 / 问题 / 1325256
Accepted
Konstantin
Asked:2022-09-04 20:00:47 +0800 CST2022-09-04 20:00:47 +0800 CST

如何将 BLOB 文件插入数据库 QtSql.QSqlQuery()

  • 772

使用以下方法将数据插入数据库QtSql.QSqlQuery()：

query = QSqlQuery()
query.prepare("INSERT INTO person (id, forename, surname) "
              "VALUES (:id, :forename, :surname)")
query.bindValue(":id", 1001)
query.bindValue(":forename", "Bart")
query.bindValue(":surname", "Simpson")
query.exec_()

问题：如果数据如何插入BLOB？

如果可能的话，一个小例子。我从逻辑上理解需要翻译成什么BLOB，然后插入一个变量......

最小的例子：

from PySide2 import QtSql
import os

def convertToBinaryData(file_path):
    # Конвертирование в BLOB
    with open(file_path, 'rb') as file:
        blobData = file.read()
        print()
    return blobData


def createConnection():
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("local_base.db")  # !!! .db
    if not db.open():
        print("Cannot establish a database connection")
        return False
    return db

# Получим данные BLOB
# картинка test_BLOB.jpg рядом с файлом скрипта
file_path = (f"{os.getcwd()}\\test_BLOB.jpg")
test_BLOB = convertToBinaryData(file_path)

db = createConnection()
db.transaction()
q = QtSql.QSqlQuery()
q.exec_("DROP TABLE IF EXISTS company;")
q.exec_("CREATE TABLE company ("
        "id INT PRIMARY KEY, "
        "name_company TEXT NOT NULL, "
        "blob_data BLOB NOT NULL );")

# Вставка тестовых значений

query = QtSql.QSqlQuery()
query.prepare("INSERT INTO company (id, name_company, blob_data) "
              "VALUES (:id, :name_company, :blob_data)")
query.bindValue(":id", 1)
query.bindValue(":name_company", 'АО КОПЫТА')
query.bindValue(":blob_data", test_BLOB)
query.exec_()

db.commit()


if __name__ == '__main__':
    print("Запуск")

idname_company它插入底座，但不插入BLOB。

python

2 个回答

  1. Best Answer

    试试这样：

    import sys
from PyQt5.QtWidgets import QMessageBox, QWidget, QApplication, \
    QVBoxLayout, QPushButton, QComboBox, QLabel, QFileDialog
from PyQt5.QtCore import QDir, QFile, QFileInfo, QIODevice
from PyQt5.QtGui import QPixmap
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel


def createConnection():
    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName("testImage_q1325256.db")
    
    if not db.open():
        QMessageBox.critical(None, "Не удается открыть базу данных ",
                "Не удалось установить соединение с базой данных.\n"
                "Этот пример требует поддержки SQLite. Пожалуйста, прочитайте Qt SQL "
                "документацию для получения информации о том, как ее построить.\n\n"
                "Click Cancel to exit.",
                QMessageBox.Cancel)
        return False

    query = QSqlQuery()
    return query.exec_('''
        CREATE TABLE IF NOT EXISTS imgTable
            (
             id INTEGER primary key AUTOINCREMENT, 
             filename TEXT, 
             imagedata BLOB
            )
    ''')


class Demo(QWidget):
    def __init__(self):
        super().__init__()

        self.load_btn = QPushButton("Выбрать изображение и загрузить в БД")
        self.combo = QComboBox()
        self.label = QLabel()
        
        self.model = QSqlTableModel()
        self.model.setTable("imgTable")
        self.model.select() 
        
        self.combo.setModel(self.model)
        self.combo.setModelColumn(1)
        
        vbox = QVBoxLayout(self)
        vbox.addWidget(self.load_btn)
        vbox.addWidget(self.combo)
        vbox.addWidget(self.label)

        self.load_btn.clicked.connect(self.load_image)
        self.combo.currentIndexChanged.connect(self.on_change_select)

    def on_change_select(self, row):
        ix  = self.combo.model().index(row, 2)
        pix = QPixmap()
        pix.loadFromData(ix.data())
        self.label.setPixmap(pix)

    def load_image(self):
        fname, _ = QFileDialog.getOpenFileName(
            self, 
            'Open file', 
            QDir.currentPath(), 
            "Image files (*.jpg, *.gif, *.png)"
        )
        if fname:
            self.saveImage(fname)

    def saveImage(self, filename):
        file = QFile(filename)
        if not file.open(QIODevice.ReadOnly):
            return

        ba = file.readAll()
        name = QFileInfo(filename).fileName()
        
        record = self.model.record()
        record.setValue("filename", name)
        record.setValue("imagedata", ba)

        if self.model.insertRecord(-1, record):
            self.model.select()


if __name__ == '__main__':
    app = QApplication(sys.argv)
    if not createConnection():
        sys.exit(-1)
    w = Demo()
    w.show()
    sys.exit(app.exec_())

    在此处输入图像描述

    在此处输入图像描述

    • 2

  2. 一般来说，我不知道这有多正确，但是在 convertToBinaryData 函数的结果之后，您需要另外通过 QtCore.QByteArray 传递 blobData 并将其插入到 INSERT INTO 中。然后一切都被显着插入，但这是一个纯粹直观的解决方案）

    from PySide2 import QtSql, QtCore
import os

def convertToBinaryData(file_path):
    # Конвертирование в BLOB
    with open(file_path, 'rb') as file:
        blobData = file.read()
        print()
    return blobData


def createConnection():
    db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
    db.setDatabaseName("local_base.db")  # !!! .db
    if not db.open():
        print("Cannot establish a database connection")
        return False
    return db

# Получим данные BLOB
# картинка test_BLOB.jpg рядом с файлом скрипта
file_path = (f"{os.getcwd()}\\test_BLOB.jpg")
test_BLOB = convertToBinaryData(file_path)
ba = QtCore.QByteArray(test_BLOB)

db = createConnection()
db.transaction()
q = QtSql.QSqlQuery()
q.exec_("DROP TABLE IF EXISTS company;")
q.exec_("CREATE TABLE company ("
        "id INT PRIMARY KEY, "
        "name_company TEXT NOT NULL, "
        "blob_data BLOB NOT NULL );")

# Вставка тестовых значений

query = QtSql.QSqlQuery()
query.prepare("INSERT INTO company (id, name_company, blob_data) "
              "VALUES (:id, :name_company, :blob_data)")
query.bindValue(":id", 1)
query.bindValue(":name_company", 'АО КОПЫТА')
query.bindValue(":blob_data", ba)
query.exec_()

db.commit()


if __name__ == '__main__':
    print("Запуск")
    • 0

相关问题