send pixmap to mysql database not working

[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