Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

send pixmap to mysql database not working



  • 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


  • Lifetime Qt Champion

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



  • @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)?



  • @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.



  • @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



  • @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 = ....



  • @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


  • Lifetime Qt Champion

    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.



  • @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)
    
    

  • Lifetime Qt Champion

    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 ?



  • 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


  • Lifetime Qt Champion

    Are they all connected to the same MySQL server ?



  • @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


  • Lifetime Qt Champion

    Did you compare the client library versions as well ?



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


Log in to reply