I am trying to insert an array into sql3 and retrieve it by first converting them into a BLOB, but its not inserting anything into the DB, code Below of a widget with QTableView using a pyqt model pattern

from PyQt6.QtWidgets import QWidget, QVBoxLayout, QHBoxLayout, QLineEdit, QTableView, QMessageBox, QAbstractItemView, QHeaderView
from PyQt6.QtSql import QSqlTableModel, QSqlDatabase, QSqlError, QSqlQuery
from PyQt6.QtCore import Qt, QSortFilterProxyModel

from numpy import array, frombuffer

from app.tools.cube_config import Config


class CubeDataTable(QWidget):
    def __init__(self):
        super(CubeDataTable, self).__init__()
        self.lay = QVBoxLayout(self)
        self.connect()
        self.__model = QSqlTableModel()
        self.__view = QTableView()
        self.__proxy = QSortFilterProxyModel()
        self.rows = 0

        self._set_main_layout()
        self.insert_data()

    def _set_main_layout(self) -> None:
        self._set_data_model()
        self._set_table_view()

        self.lay.addWidget(self.__view)

    def _set_data_model(self) -> None:
        self.__model.setTable('audio')
        self.__model.setEditStrategy(QSqlTableModel.EditStrategy.OnManualSubmit)

        self.__model.setHeaderData(0, Qt.Orientation.Horizontal, "ID")
        self.__model.setHeaderData(1, Qt.Orientation.Horizontal, "Name")
        self.__model.setHeaderData(2, Qt.Orientation.Horizontal, "Sample Rate")
        self.__model.setHeaderData(3, Qt.Orientation.Horizontal, "Raw Frames")
        self.__model.sort(1, Qt.SortOrder.DescendingOrder)

        self.__model.select()
        self.rows = self.__model.rowCount()

        self.__proxy.setSourceModel(self.__model)
        self.__proxy.setFilterCaseSensitivity(Qt.CaseSensitivity.CaseInsensitive)
        self.__proxy.setFilterKeyColumn(1)

    def _set_table_view(self) -> None:
        self.__view.setModel(self.__proxy)
        self.__view.setSortingEnabled(True)
        self.__view.setAlternatingRowColors(True)
        self.__view.setColumnHidden(0, True)
        self.__view.verticalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
        self.__view.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
        self.__view.setEditTriggers(QAbstractItemView.EditTrigger.NoEditTriggers)
        self.__view.setSelectionBehavior(QAbstractItemView.SelectionBehavior.SelectRows)
        self.__view.setSelectionMode(QAbstractItemView.SelectionMode.SingleSelection)
        self.__view.resizeColumnsToContents()
        self.__view.selectionModel().selectionChanged.connect(self._get_selected_row)

    def _get_selected_row(self, selected, deselected):
        index_entity = self.__view.selectionModel().selectedIndexes()
        temp_entity = self.__view.selectionModel().model()
        for index in sorted(index_entity):
            d = temp_entity.data(index)
            print(str(temp_entity.data(index)))

    def insert_data(self):
        try:
            a = array([0.1, 0.2, 0.3])

            self.__model.insertRows(self.rows, 1)
            self.__model.setData(self.__model.index(self.rows, 1), 'foo')
            self.__model.setData(self.__model.index(self.rows, 3), a.tobytes())
            self.__model.submitAll()
            self.rows  = 1
        except (Exception, QSqlError) as e:
            self.__model.revertAll()
            QSqlDatabase.database().rollback()
            print("Error: "   str(e))

   
    @staticmethod
    def connect() -> bool:
        config = Config.get('data')
        conn = QSqlDatabase.addDatabase('QSQLITE')
        conn.setHostName(config['host'])
        conn.setPort(config['port'])
        conn.setPassword(config['password'])
        conn.setUserName(config['user'])
        conn.setDatabaseName(config['database'])
        if not conn.open():
            QMessageBox.critical(QMessageBox(), "Error", "Error: %s" % conn.lastError().text())
            return False

        cursor = QSqlQuery()
        cursor.exec(
            """
            CREATE TABLE audio (
                id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                name VARCHAR(40) NOT NULL,
                sampleRate INTEGER,
                rawFrames BLOB                
            )
            """
        )
        return True

CodePudding user response：

When dealing with Qt it's important to remember that their python bindings (PyQt and PySide) use automatic type conversions, and that functions that can accept variable ("union" types) use QVariant on the Qt side.

While most conversions are done transparently on the python side, this might not be the case for special cases, including raw data in database classes, since Qt has to know more specifically the data type in order to properly submit it to the database.

The general rule is that if the data is to be stored as bytes, it's normally required to use QByteArray:

self.__model.setData(self.__model.index(self.rows, 3), QByteArray(a.tobytes()))

Note that if you need to display the array in a human readable form, you also need a specific delegate that can handle it. For this, it's normally enough to implement displayText() of QStyledItemDelegate and convert the byte array to a printable version of it:

class Delegate(QStyledItemDelegate):        
    def displayText(self, text, locale):
        if isinstance(text, QByteArray):
            try:
                res = frombuffer(text.data())
                return str(res)
            except:
                pass
        return super().displayText(text, locale)

Then, set the delegate for the required column:

self.__view.setItemDelegateForColumn(3, Delegate(self.__view))

Note that it's normally better to avoid a "static" reference for self.rows as you may forget to update it whenever you change the model elsewhere, or in case the update is not successful; instead, just use the dynamic getter rowCount() and remember that it's always important to check whether the function has been successful when using database models.

    def insert_data(self):
        a = array([0.1, 0.2, 0.3])
        row = self.__model.rowCount()
        if not self.__model.insertRow(row):
            # some warning using self.__model.lastError(), then return

        self.__model.setData(self.__model.index(row, 1), 'foo')
        # ...
        if not self.__model.submitAll():
            # as above