send pixmap to mysql database not working

[rhx9]

Hi,
I'm using this code to send a pixmap to the database

images_submit.py

import sys
import os
import signal
import traceback
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtCore as qtc
from PyQt5 import QtGui as qtg
from PyQt5 import QtSql


import time
from PyQt5 import QtCore as qtc
from PyQt5.QtNetwork import QTcpSocket, QAbstractSocket
from PyQt5.QtMultimedia import QCameraInfo, QCamera, QCameraImageCapture

from PyQt5.QtMultimediaWidgets import QCameraViewfinder
from main import Ui_MainWindow

class cameraViewFinder(QCameraViewfinder):
    doubleclicked = qtc.pyqtSignal()
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)

    def mouseDoubleClickEvent(self,e):
        self.doubleclicked.emit()

class MainWindow(qtw.QMainWindow):
    donecapturing = qtc.pyqtSignal(qtg.QPixmap)
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.ui = Ui_MainWindow()
        self.ui.setupUi(self)


        self.viewFinder = cameraViewFinder()
        self.viewFinder.doubleclicked.connect(self.capture_img)
        self.donecapturing.connect(self.updateAvatar)
        self.viewFinder.hide()
        self.ui.load.clicked.connect(self.editPhoto)
        self.ui.submit.clicked.connect(self.submit_data)
        self.db = QtSql.QSqlDatabase.addDatabase("QMYSQL")
        self.db.setDatabaseName("test")
        self.db.setHostName("127.0.0.1")
        self.db.setPassword("77887788")
        self.db.setUserName("root")
        self.db.open()
        self.show()
    
    def get_webcam(self, i):
        self.my_webcam = QCamera(self.online_webcams[i])
        self.my_webcam.setViewfinder(self.viewFinder)
        self.my_webcam.setCaptureMode(QCamera.CaptureStillImage)
        self.my_webcam.error.connect(lambda: self.alert(self.my_webcam.errorString()))
        print("starting webcam")
        self.my_webcam.start()
        print("setting resolution")
        self.my_webcam.viewfinderSettings().setResolution(1280 , 720)
        print("printing resolutions")
        print(self.my_webcam.viewfinderSettings().resolution())

        self.capture = QCameraImageCapture(self.my_webcam)

        self.capture.setCaptureDestination(self.capture.CaptureToBuffer)
        self.capture.error.connect(lambda i, e, s: self.alert(s))

        self.capture.imageCaptured.connect(self.captured)
        self.current_camera_name = self.online_webcams[i].description()
    
    
    @qtc.pyqtSlot(qtg.QPixmap)
    def updateAvatar(self, img):
        self.viewFinder.hide()
        
        print("scale the pixmap")
        img = img.scaledToWidth(480, qtc.Qt.SmoothTransformation)
        print(img)
        self.ui.image_from_camera.setPixmap(img)
        self.ui.image_from_camera.show()
        
    def captured(self,d,i):
        print('captured')
        print(i)
        self.pixmap = qtg.QPixmap.fromImage(i)
        self.donecapturing.emit(self.pixmap)
    @qtc.pyqtSlot()
    def capture_img(self):
        if (self.capture.isReadyForCapture()):
            self.capture.capture()

    @qtc.pyqtSlot()
    def editPhoto(self):
        print("getting list of webcam")
        self.online_webcams = QCameraInfo.availableCameras()

        if not self.online_webcams:
            self.statusbar.showMessage("you didn't connect a camera")
        else:
            self.ui.camera_layout.addWidget(self.viewFinder)
            print("getting webcam")
            self.get_webcam(0)
            self.ui.image_from_camera.hide()
            self.viewFinder.show()
    def submit_data(self):
        if(self.db.isOpen()):
            qry = "INSERT INTO members VALUES (NULL, :image)"
            Qquery = QtSql.QSqlQuery()
            Qquery.prepare(qry)
            #convert the image to QByteArray
            ba = qtc.QByteArray()
            buff = qtc.QBuffer(ba)
            buff.open(qtc.QIODevice.WriteOnly)
            ok = self.ui.image_from_camera.pixmap().save(buff, "PNG")
            assert ok
            Qquery.bindValue(":image", ba)
            print("inserting new member")
            qrystat = Qquery.exec_()
            qryerr = Qquery.lastError().nativeErrorCode()
            print("mysql error code: ",qryerr)
            print("mysql error text: ",Qquery.lastError().text())
            if(qrystat and qryerr == ''):
                print("everything is ok")
                print("getting back our image")
                insertedid = Qquery.lastInsertId()
                print("inserted id: ",insertedid)
                qry = "SELECT * FROM members where `id` = :id"
                Qquery = QtSql.QSqlQuery()
                Qquery.prepare(qry)
                Qquery.bindValue(":id", insertedid)
                qrystat = Qquery.exec_()
                qryerr = Qquery.lastError().nativeErrorCode()
                print("mysql error code: ",qryerr)
                print("mysql error text: ",Qquery.lastError().text())
                
                Qquery.first()
                print("we got back a binary of type:",type(Qquery.value("image")))
                qimg = qtg.QImage.fromData(Qquery.value("image"))
                pixmap = qtg.QPixmap.fromImage(qimg)
                self.ui.image_from_db.setPixmap(pixmap)

        else:
            print( "database is not connected")

    def load_avatar(self):
        self.avatar.setPixmap(qtg.QPixmap("avatar.jpg"))

    def closeEvent(self, event):
        print("closing client control")
        self.cleanup()
        qtw.QApplication.closeAllWindows()

    def cleanup(self):
        pass
        


def setup_interrupt_handling():
    """Setup handling of KeyboardInterrupt (Ctrl-C) for PyQt."""
    signal.signal(signal.SIGINT, _interrupt_handler)
    # Regularly run some (any) python code, so the signal handler gets a
    # chance to be executed:
    safe_timer(50, lambda: None)


# Define this as a global function to make sure it is not garbage
# collected when going out of scope:
def _interrupt_handler(signum, frame):
    """Handle KeyboardInterrupt: quit application."""
    w.cleanup()
    qtw.QApplication.quit()


def safe_timer(timeout, func, *args, **kwargs):
    """
    Create a timer that is safe against garbage collection and overlapping
    calls. See: http://ralsina.me/weblog/posts/BB974.html
    """
    def timer_event():
        try:
            func(*args, **kwargs)
        finally:
            qtc.QTimer.singleShot(timeout, timer_event)
    qtc.QTimer.singleShot(timeout, timer_event)


def excepthook(exc_type, exc_value, exc_tb):
    tb = "".join(traceback.format_exception(exc_type, exc_value, exc_tb))
    print("error catched!:")
    print("error message:\n", tb)
    qtw.QApplication.quit()


if __name__ == '__main__':
    os.environ["QT_AUTO_SCREEN_SCALE_FACTOR"] = "1"
    
    sys.excepthook = excepthook
    try:
        qtw.QApplication.setAttribute(qtc.Qt.AA_EnableHighDpiScaling)
    except AttributeError:  # Attribute only exists for Qt>=5.6.
        pass
    app = qtw.QApplication(sys.argv)
    setup_interrupt_handling()

    w = MainWindow()
    ret = app.exec_()
    sys.exit(ret)

main.py:

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

# Form implementation generated from reading ui file 'main.ui'
#
# Created by: PyQt5 UI code generator 5.15.3
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again.  Do not edit this file unless you know what you are doing.


from PyQt5 import QtCore, QtGui, QtWidgets


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(765, 662)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.gridLayout = QtWidgets.QGridLayout(self.centralwidget)
        self.gridLayout.setObjectName("gridLayout")
        self.image_from_db = QtWidgets.QLabel(self.centralwidget)
        self.image_from_db.setObjectName("image_from_db")
        self.gridLayout.addWidget(self.image_from_db, 0, 1, 1, 1)
        self.submit = QtWidgets.QPushButton(self.centralwidget)
        self.submit.setObjectName("submit")
        self.gridLayout.addWidget(self.submit, 2, 0, 1, 2)
        self.load = QtWidgets.QPushButton(self.centralwidget)
        self.load.setObjectName("load")
        self.gridLayout.addWidget(self.load, 1, 0, 1, 2)
        self.camera_layout = QtWidgets.QVBoxLayout()
        self.camera_layout.setObjectName("camera_layout")
        self.image_from_camera = QtWidgets.QLabel(self.centralwidget)
        self.image_from_camera.setObjectName("image_from_camera")
        self.camera_layout.addWidget(self.image_from_camera)
        self.gridLayout.addLayout(self.camera_layout, 0, 0, 1, 1)
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 765, 34))
        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.image_from_db.setText(_translate("MainWindow", "image from db"))
        self.submit.setText(_translate("MainWindow", "submit"))
        self.load.setText(_translate("MainWindow", "load"))
        self.image_from_camera.setText(_translate("MainWindow", "image from camera"))


if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())

mysql database:

CREATE TABLE `members` (
  `id` int(11) NOT NULL,
  `image` longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


ALTER TABLE `members`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `members`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=48;
COMMIT;

This is not my full app but it will show the problem clearly, to replicate:
1- run the code images_submit.py
2- click the load button this will show a camera view finder in the place of the image from camera qlabel
3- double click the view finder to capture the image
4- click submit to submit the image to the database

after clicking submit the code should submit the image to the database and if it was submitted correctly it will then fetch the image from the database and show it in the image_from_database qlabel
somehow the image is getting corrupted maybe because i all i see is an empty pixmap in image_from_database after submitting

the code used to work flawlessly before, i don't know what i changed or if a PYQT5 update broke it

Regards

[jsulm]

@rhx9 Would be nice if you would reduce all this code to something small which still shows the issue.

[rhx9]

@jsulm said in send pixmap to mysql database not working:

@rhx9 Would be nice if you would reduce all this code to something small which still shows the issue.

well i didn't remove the camera part because it might be the one causing the problems, anyways this is a simpler code that also shows the problem

import sys
import os
import signal
import traceback
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtCore as qtc
from PyQt5 import QtGui as qtg
from PyQt5 import QtSql
import time


class MainWindow(qtw.QMainWindow):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.resize(826, 627)
        self.centralwidget = qtw.QWidget(self)
        self.gridLayout = qtw.QGridLayout(self.centralwidget)
        self.submit = qtw.QPushButton(self.centralwidget)
        self.gridLayout.addWidget(self.submit, 2, 0, 1, 2)
        self.load = qtw.QPushButton(self.centralwidget)
        self.gridLayout.addWidget(self.load, 1, 0, 1, 2)
        self.image_from_camera = qtw.QLabel(self.centralwidget)
        self.gridLayout.addWidget(self.image_from_camera, 0, 0, 1, 1)
        self.image_from_db = qtw.QLabel(self.centralwidget)
        self.gridLayout.addWidget(self.image_from_db, 0, 1, 1, 1)
        self.setCentralWidget(self.centralwidget)
        self.statusbar = qtw.QStatusBar(self)
        self.setStatusBar(self.statusbar)
        self.submit.setText("submit data")
        self.load.setText( "load image")
        self.image_from_camera.setText("image from camera")
        self.image_from_db.setText( "image from db")

        self.load.clicked.connect(self.load_image)
        self.submit.clicked.connect(self.submit_data)
        self.db = QtSql.QSqlDatabase.addDatabase("QMYSQL")
        self.db.setDatabaseName("test")
        self.db.setHostName("127.0.0.1")
        self.db.setPassword("77887788")
        self.db.setUserName("root")
        self.db.open()
        self.show()
    
    
    @qtc.pyqtSlot()
    def load_image(self):
        print("loading image from the disk")
        self.image_from_camera.setPixmap(qtg.QPixmap("avatar.jpg"))
    def submit_data(self):
        if(self.db.isOpen()):
            qry = "INSERT INTO members VALUES (NULL, :image)"
            Qquery = QtSql.QSqlQuery()
            Qquery.prepare(qry)
            #convert the image to QByteArray
            ba = qtc.QByteArray()
            buff = qtc.QBuffer(ba)
            buff.open(qtc.QIODevice.WriteOnly)
            ok = self.image_from_camera.pixmap().save(buff, "PNG")
            assert ok
            Qquery.bindValue(":image", ba)
            print("inserting new member")
            qrystat = Qquery.exec_()
            qryerr = Qquery.lastError().nativeErrorCode()
            print("mysql error code: ",qryerr)
            print("mysql error text: ",Qquery.lastError().text())
            if(qrystat and qryerr == ''):
                print("everything is ok")
                print("getting back our image")
                insertedid = Qquery.lastInsertId()
                print("inserted id: ",insertedid)
                qry = "SELECT * FROM members where `id` = :id"
                Qquery = QtSql.QSqlQuery()
                Qquery.prepare(qry)
                Qquery.bindValue(":id", insertedid)
                qrystat = Qquery.exec_()
                qryerr = Qquery.lastError().nativeErrorCode()
                print("mysql error code: ",qryerr)
                print("mysql error text: ",Qquery.lastError().text())
                
                Qquery.first()
                
                qimg = qtg.QImage.fromData(Qquery.value("image"))
                pixmap = qtg.QPixmap.fromImage(qimg)
                print("we got back a binary of type:",type(Qquery.value("image")))
                print("is QByteArray null?: ",Qquery.value("image").isNull())
                print("is QByteArray empty?: ",Qquery.value("image").isEmpty())
                print("is pixmap null?: ",pixmap.isNull())
                self.image_from_db.setPixmap(pixmap)

        else:
            print( "database is not connected")

    def closeEvent(self, event):
        print("closing client control")
        self.cleanup()
        qtw.QApplication.closeAllWindows()

    def cleanup(self):
        pass
        


def setup_interrupt_handling():
    """Setup handling of KeyboardInterrupt (Ctrl-C) for PyQt."""
    signal.signal(signal.SIGINT, _interrupt_handler)
    # Regularly run some (any) python code, so the signal handler gets a
    # chance to be executed:
    safe_timer(50, lambda: None)


# Define this as a global function to make sure it is not garbage
# collected when going out of scope:
def _interrupt_handler(signum, frame):
    """Handle KeyboardInterrupt: quit application."""
    w.cleanup()
    qtw.QApplication.quit()


def safe_timer(timeout, func, *args, **kwargs):
    """
    Create a timer that is safe against garbage collection and overlapping
    calls. See: http://ralsina.me/weblog/posts/BB974.html
    """
    def timer_event():
        try:
            func(*args, **kwargs)
        finally:
            qtc.QTimer.singleShot(timeout, timer_event)
    qtc.QTimer.singleShot(timeout, timer_event)


def excepthook(exc_type, exc_value, exc_tb):
    tb = "".join(traceback.format_exception(exc_type, exc_value, exc_tb))
    print("error catched!:")
    print("error message:\n", tb)
    qtw.QApplication.quit()


if __name__ == '__main__':
    os.environ["QT_AUTO_SCREEN_SCALE_FACTOR"] = "1"
    
    sys.excepthook = excepthook
    try:
        qtw.QApplication.setAttribute(qtc.Qt.AA_EnableHighDpiScaling)
    except AttributeError:  # Attribute only exists for Qt>=5.6.
        pass
    app = qtw.QApplication(sys.argv)
    setup_interrupt_handling()

    w = MainWindow()
    ret = app.exec_()
    sys.exit(ret)

use the same sql database in the main post, and place any image with the name avatar.jpg in the same directory as the script

after starting the script, click "load image" to load the image from the disk, which should show the image in the left, then click "submit data" to send the data to the database.

the image saved in the database should be shown on the right, in my case it is showing an empty image(corrupted pixmap)?

[JonB]

@rhx9
If I were trying to debug this I would start by looking at the original size of the image, look at the size of the byte array sent to SQL, look at the size of the stored value in SQL, look at the size of the byte array received back from SQL, look at the size of the final image. It seems likely one of those loses data length? Otherwise you're faced by data content being altered.

For example, I would not write a line like qimg = qtg.QImage.fromData(Qquery.value("image")), I would want to know what is in Qquery.value("image") first.

[rhx9]

@JonB said in send pixmap to mysql database not working:

@rhx9
If I were trying to debug this I would start by looking at the original size of the image, look at the size of the byte array sent to SQL, look at the size of the stored value in SQL, look at the size of the byte array received back from SQL, look at the size of the final image. It seems likely one of those loses data length? Otherwise you're faced by data content being altered.

For example, I would not write a line like qimg = qtg.QImage.fromData(Qquery.value("image")), I would want to know what is in Qquery.value("image") first.

I'm doing some checking too in the code

print("we got back a binary of type:",type(Qquery.value("image")))
print("is QByteArray null?: ",Qquery.value("image").isNull())
print("is QByteArray empty?: ",Qquery.value("image").isEmpty())
print("is pixmap null?: ",pixmap.isNull())

which results in:

is QByteArray null?: False
is QByteArray empty?: False
is pixmap null?: True

I added more testing to check the size so the final code is:

import sys
import os
import signal
import traceback
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtCore as qtc
from PyQt5 import QtGui as qtg
from PyQt5 import QtSql
import time


class MainWindow(qtw.QMainWindow):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.resize(826, 627)
        self.centralwidget = qtw.QWidget(self)
        self.gridLayout = qtw.QGridLayout(self.centralwidget)
        self.submit = qtw.QPushButton(self.centralwidget)
        self.gridLayout.addWidget(self.submit, 2, 0, 1, 2)
        self.load = qtw.QPushButton(self.centralwidget)
        self.gridLayout.addWidget(self.load, 1, 0, 1, 2)
        self.image_from_camera = qtw.QLabel(self.centralwidget)
        self.gridLayout.addWidget(self.image_from_camera, 0, 0, 1, 1)
        self.image_from_db = qtw.QLabel(self.centralwidget)
        self.gridLayout.addWidget(self.image_from_db, 0, 1, 1, 1)
        self.setCentralWidget(self.centralwidget)
        self.statusbar = qtw.QStatusBar(self)
        self.setStatusBar(self.statusbar)
        self.submit.setText("submit data")
        self.load.setText( "load image")
        self.image_from_camera.setText("image from camera")
        self.image_from_db.setText( "image from db")

        self.load.clicked.connect(self.load_image)
        self.submit.clicked.connect(self.submit_data)
        self.db = QtSql.QSqlDatabase.addDatabase("QMYSQL")
        self.db.setDatabaseName("test")
        self.db.setHostName("127.0.0.1")
        self.db.setPassword("77887788")
        self.db.setUserName("root")
        self.db.open()
        self.show()
    
    
    @qtc.pyqtSlot()
    def load_image(self):
        print("loading image from the disk")
        self.image_from_camera.setPixmap(qtg.QPixmap("avatar.jpg"))
        
    def submit_data(self):
        if(self.db.isOpen()):
            qry = "INSERT INTO members VALUES (NULL, :image)"
            Qquery = QtSql.QSqlQuery()
            Qquery.prepare(qry)
            #convert the image to QByteArray
            ba = qtc.QByteArray()
            buff = qtc.QBuffer(ba)
            buff.open(qtc.QIODevice.WriteOnly)
            print("QByteArray size before filling is: ",ba.size())
            ok = self.image_from_camera.pixmap().save(buff, "PNG")
            assert ok
            print("QByteArray size after filling is: ",ba.size())
            Qquery.bindValue(":image", ba)
            print("inserting new member")
            qrystat = Qquery.exec_()
            qryerr = Qquery.lastError().nativeErrorCode()
            print("mysql error code: ",qryerr)
            print("mysql error text: ",Qquery.lastError().text())
            if(qrystat and qryerr == ''):
                print("everything is ok")
                print("getting back our image")
                insertedid = Qquery.lastInsertId()
                print("inserted id: ",insertedid)
                qry = "SELECT * FROM members where `id` = :id"
                Qquery = QtSql.QSqlQuery()
                Qquery.prepare(qry)
                Qquery.bindValue(":id", insertedid)
                qrystat = Qquery.exec_()
                qryerr = Qquery.lastError().nativeErrorCode()
                print("mysql error code: ",qryerr)
                print("mysql error text: ",Qquery.lastError().text())
                
                Qquery.first()
                imageByteArray = Qquery.value("image")
                qimg = qtg.QImage.fromData(imageByteArray)
                pixmap = qtg.QPixmap.fromImage(qimg)
                print("we got back a binary of type:",type(imageByteArray))
                print("is QByteArray null?: ",imageByteArray.isNull())
                print("is QByteArray empty?: ",imageByteArray.isEmpty())
                print("is pixmap null?: ",pixmap.isNull())
                print("QByteArray size we got from db is: ",imageByteArray.size())
                self.image_from_db.setPixmap(pixmap)

        else:
            print( "database is not connected")

    def closeEvent(self, event):
        print("closing client control")
        self.cleanup()
        qtw.QApplication.closeAllWindows()

    def cleanup(self):
        pass
        


def setup_interrupt_handling():
    """Setup handling of KeyboardInterrupt (Ctrl-C) for PyQt."""
    signal.signal(signal.SIGINT, _interrupt_handler)
    # Regularly run some (any) python code, so the signal handler gets a
    # chance to be executed:
    safe_timer(50, lambda: None)


# Define this as a global function to make sure it is not garbage
# collected when going out of scope:
def _interrupt_handler(signum, frame):
    """Handle KeyboardInterrupt: quit application."""
    w.cleanup()
    qtw.QApplication.quit()


def safe_timer(timeout, func, *args, **kwargs):
    """
    Create a timer that is safe against garbage collection and overlapping
    calls. See: http://ralsina.me/weblog/posts/BB974.html
    """
    def timer_event():
        try:
            func(*args, **kwargs)
        finally:
            qtc.QTimer.singleShot(timeout, timer_event)
    qtc.QTimer.singleShot(timeout, timer_event)


def excepthook(exc_type, exc_value, exc_tb):
    tb = "".join(traceback.format_exception(exc_type, exc_value, exc_tb))
    print("error catched!:")
    print("error message:\n", tb)
    qtw.QApplication.quit()


if __name__ == '__main__':
    os.environ["QT_AUTO_SCREEN_SCALE_FACTOR"] = "1"
    
    sys.excepthook = excepthook
    try:
        qtw.QApplication.setAttribute(qtc.Qt.AA_EnableHighDpiScaling)
    except AttributeError:  # Attribute only exists for Qt>=5.6.
        pass
    app = qtw.QApplication(sys.argv)
    setup_interrupt_handling()

    w = MainWindow()
    ret = app.exec_()
    sys.exit(ret)

which results in:

loading image from the disk
QByteArray size before filling is: 0
QByteArray size after filling is: 201894
inserting new member
mysql error code:
mysql error text:
everything is ok
getting back our image
inserted id: 67
mysql error code:
mysql error text:
we got back a binary of type: <class 'PyQt5.QtCore.QByteArray'>
is QByteArray null?: False
is QByteArray empty?: False
is pixmap null?: True
QByteArray size we got from db is: 373658
closing client control

so the QByteArray we sent was 201894 bytes but the returned QByteArray is 373658 bytes, i don't what caused this increase in size.

also if i download the the blob from the database and run it through a type detecting tool like file or binwalk it is not being detect as a png file at all

[JonB]

@rhx9
Start by finding out how big the blob is as stored in the SQL database, nothing to do with client/Qt/QByteArray. Presumably you can either use some external mangement tool to connect to the database to discover this, or your SQL (whatever it is) probably has something like SELECT DATALENGTH(blob_column) FROM table WHERE id = ....

[rhx9]

@JonB said in send pixmap to mysql database not working:

@rhx9
Start by finding out how big the blob is as stored in the SQL database, nothing to do with client/Qt/QByteArray. Presumably you can either use some external mangement tool to connect to the database to discover this, or your SQL (whatever it is) probably has something like SELECT DATALENGTH(blob_column) FROM table WHERE id = ....

yeah the length in the database is exactly what is returned by the second print

QByteArray size we got from db is: 373658

this only tells me that the saved in the db is a different size than what was sent, and that the returning of data is working fine, the only problem is in the sending part, and still i have no idea what is causing this

[SGaist]

Hi,

Just to check, did you verify that the saving part of the image works correctly by storing it on your disk and then opening the file ?

Before the pixmap, you should also take a look at the QImage object.

Since you are forcing the data to be stored as png, you may as well also tell it so when loading just in case.

[rhx9]

@SGaist said in send pixmap to mysql database not working:

Hi,

Just to check, did you verify that the saving part of the image works correctly by storing it on your disk and then opening the file ?

yes, it works fine, i will include a complete code down below that saves a version before sending and after receiving

Before the pixmap, you should also take a look at the QImage object.

what should i look for? i checked if the QImage is null and it does appear to be Null

Since you are forcing the data to be stored as png, you may as well also tell it so when loading just in case.

Tried that, didn't solve the problem

this the complete final code:

import sys
import os
import signal
import traceback
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtCore as qtc
from PyQt5 import QtGui as qtg
from PyQt5 import QtSql
import time


class MainWindow(qtw.QMainWindow):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.resize(826, 627)
        self.centralwidget = qtw.QWidget(self)
        self.gridLayout = qtw.QGridLayout(self.centralwidget)
        self.submit = qtw.QPushButton(self.centralwidget)
        self.gridLayout.addWidget(self.submit, 2, 0, 1, 2)
        self.load = qtw.QPushButton(self.centralwidget)
        self.gridLayout.addWidget(self.load, 1, 0, 1, 2)
        self.image_from_camera = qtw.QLabel(self.centralwidget)
        self.gridLayout.addWidget(self.image_from_camera, 0, 0, 1, 1)
        self.image_from_db = qtw.QLabel(self.centralwidget)
        self.gridLayout.addWidget(self.image_from_db, 0, 1, 1, 1)
        self.setCentralWidget(self.centralwidget)
        self.statusbar = qtw.QStatusBar(self)
        self.setStatusBar(self.statusbar)
        self.submit.setText("submit data")
        self.load.setText( "load image")
        self.image_from_camera.setText("image from camera")
        self.image_from_db.setText( "image from db")

        self.load.clicked.connect(self.load_image)
        self.submit.clicked.connect(self.submit_data)
        self.db = QtSql.QSqlDatabase.addDatabase("QMYSQL")
        self.db.setDatabaseName("test")
        self.db.setHostName("127.0.0.1")
        self.db.setPassword("77887788")
        self.db.setUserName("root")
        self.db.open()
        self.show()
    
    
    @qtc.pyqtSlot()
    def load_image(self):
        print("loading image from the disk")
        self.image_from_camera.setPixmap(qtg.QPixmap("avatar.png"))
        
    def submit_data(self):
        if(self.db.isOpen()):
            qry = "INSERT INTO members VALUES (NULL, :image)"
            Qquery = QtSql.QSqlQuery()
            Qquery.prepare(qry)
            #convert the image to QByteArray
            ba = qtc.QByteArray()
            buff = qtc.QBuffer(ba)
            buff.open(qtc.QIODevice.WriteOnly)
            print("QByteArray size before filling is: ",ba.size())
            ok = self.image_from_camera.pixmap().save(buff, "PNG")
            assert ok
            #saving a backup of the bytes that were sent
            backupfile = qtc.QFile("sentfile.png")
            backupfile.open(qtc.QIODevice.WriteOnly)
            backupfile.write(ba)
            print("QByteArray size after filling is: ",ba.size())
            Qquery.bindValue(":image", ba)
            print("inserting new member")
            qrystat = Qquery.exec_()
            qryerr = Qquery.lastError().nativeErrorCode()
            print("mysql error code: ",qryerr)
            print("mysql error text: ",Qquery.lastError().text())
            if(qrystat and qryerr == ''):
                print("everything is ok")
                print("getting back our image")
                insertedid = Qquery.lastInsertId()
                print("inserted id: ",insertedid)
                qry = "SELECT * FROM members where `id` = :id"
                Qquery = QtSql.QSqlQuery()
                Qquery.prepare(qry)
                Qquery.bindValue(":id", insertedid)
                qrystat = Qquery.exec_()
                qryerr = Qquery.lastError().nativeErrorCode()
                print("mysql error code: ",qryerr)
                print("mysql error text: ",Qquery.lastError().text())
                
                Qquery.first()
                imageByteArray = Qquery.value("image")
                qimg = qtg.QImage.fromData(imageByteArray,"PNG")
                pixmap = qtg.QPixmap.fromImage(qimg)
                print("we got back a binary of type:",type(imageByteArray))
                print("is QByteArray null?: ",imageByteArray.isNull())
                print("is QByteArray empty?: ",imageByteArray.isEmpty())
                print("is QImage null?: ",qimg.isNull())
                print("is pixmap null?: ",pixmap.isNull())
                print("QByteArray size we got from db is: ",imageByteArray.size())
                #saving a backup of the bytes that were recieved
                backupfile2 = qtc.QFile("{}.png".format(insertedid))
                backupfile2.open(qtc.QIODevice.WriteOnly)
                backupfile2.write(imageByteArray)

                self.image_from_db.setPixmap(pixmap)

        else:
            print( "database is not connected")

    def closeEvent(self, event):
        print("closing client control")
        self.cleanup()
        qtw.QApplication.closeAllWindows()

    def cleanup(self):
        pass
        


def setup_interrupt_handling():
    """Setup handling of KeyboardInterrupt (Ctrl-C) for PyQt."""
    signal.signal(signal.SIGINT, _interrupt_handler)
    # Regularly run some (any) python code, so the signal handler gets a
    # chance to be executed:
    safe_timer(50, lambda: None)


# Define this as a global function to make sure it is not garbage
# collected when going out of scope:
def _interrupt_handler(signum, frame):
    """Handle KeyboardInterrupt: quit application."""
    w.cleanup()
    qtw.QApplication.quit()


def safe_timer(timeout, func, *args, **kwargs):
    """
    Create a timer that is safe against garbage collection and overlapping
    calls. See: http://ralsina.me/weblog/posts/BB974.html
    """
    def timer_event():
        try:
            func(*args, **kwargs)
        finally:
            qtc.QTimer.singleShot(timeout, timer_event)
    qtc.QTimer.singleShot(timeout, timer_event)


def excepthook(exc_type, exc_value, exc_tb):
    tb = "".join(traceback.format_exception(exc_type, exc_value, exc_tb))
    print("error catched!:")
    print("error message:\n", tb)
    qtw.QApplication.quit()


if __name__ == '__main__':
    os.environ["QT_AUTO_SCREEN_SCALE_FACTOR"] = "1"
    
    sys.excepthook = excepthook
    try:
        qtw.QApplication.setAttribute(qtc.Qt.AA_EnableHighDpiScaling)
    except AttributeError:  # Attribute only exists for Qt>=5.6.
        pass
    app = qtw.QApplication(sys.argv)
    setup_interrupt_handling()

    w = MainWindow()
    ret = app.exec_()
    sys.exit(ret)

[SGaist]

The next thing I would do is compare the data you get from the database and the one from the file saved on disk. They should be the same.

Did you try with using the blob type rather than the longblob ?

[rhx9]

Ok this seems like an issue caused by a recent update to my system i think, why? because i tested the same code on two different machines and it works fine

• on a windows machine where the lib versions are as follows:
  Qt version: 5.15.2
  SIP version: 5.4.0
  PyQt version: 5.15.3
• on a raspberry pi running debian(raspbian os) where the lib versions as follows:
  Qt version: 5.11.3
  SIP version: 4.19.14
  PyQt version: 5.11.3

here is the weird part, my linux(Manjaro) machine that exhibit the issue has the same lib version as the windows machine:
Qt version: 5.15.2
SIP version: 5.4.0
PyQt version: 5.15.3

So i'm not sure what is causing this or where to look for

[SGaist]

Are they all connected to the same MySQL server ?

[rhx9]

@SGaist said in send pixmap to mysql database not working:

Are they all connected to the same MySQL server ?

yes, the server is on the raspberry pi, so the problem is on the client

[SGaist]

Did you compare the client library versions as well ?

[rhx9]

I did a whole system update and the problem is gone, don't know what caused it, but i'm happy it's gone