Displaying retrieved database image inside Qtablewidget in Python #5718

CXX22 · 2021-09-16T20:22:10Z

CXX22
Sep 16, 2021

Although I have posted something similar which is yet to be resolved, I decide to use this other pattern.

I have some records in a database table. The attributes (columns) of the db table include imagedata which has a datatype of varbinary(max). In this code example, I am trying to retrieve the records from the database, make them display in the qtablewidget cells as I have named the headers to correspond with the column names in the database table.

I am having issue with the image (photo) displaying inside the tablewidget cell (there's a column in the qtablewidget meant for the image). Here is the code below. With the code so far, the other data gets displayed in the qtablewidget except the image.

import sys
import pyodbc, uuid, os, hashlib
from PyQt5 import QtWidgets, QtCore, QtGui
from PyQt5.QtWidgets import *
from PyQt5.QtGui import (QIcon, QFont, QPixmap)
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
import datetime, getpass
from random import randint as ri

class userUpdate(QWidget):
    def __init__(self):
        super().__init__()
        self.setGeometry(100, 100, 800, 700)

        # setting the background stylesheet
        self.setAutoFillBackground(True)
        winP = self.palette()
        winP.setColor(self.backgroundRole(), Qt.darkGray)
        self.setPalette(winP)

        gridtab = QGridLayout()
        gboxtab = QGroupBox()
        gboxtab.setCheckable(True)
        self.setLayout(gridtab)

        vboxtab = QVBoxLayout()
        gboxtab.setLayout(vboxtab)

        hboxtab = QHBoxLayout()
        hboxtab2 = QHBoxLayout()
        vboxtab.addLayout(hboxtab)
        vboxtab.addLayout(hboxtab2)

        self.tab = QtWidgets.QTableWidget(self)
        gboxtab.setStyleSheet("background-color: powderblue")
        self.tab.setStyleSheet("background-color: cornsilk; gridline-color: red; border: 2px solid gray; selection-color: yellow")

        self.searchbtn = QPushButton("Search:")
        self.searchbtn.setStyleSheet("background-color: lightsteelblue; color: black")
        self.searchbtn.clicked.connect(self.retrieve1)

        displaylb = QLabel("Selected row")
        self.displaytx = QLineEdit()
        self.displaytx.setReadOnly(True)
        self.displaytx.setStyleSheet("background-color: moccasin;")

        global searchtxtab
        searchtxtab = QLineEdit()
        searchtxtab.setStyleSheet("background-color: moccasin;")

        clearbtn = QPushButton("Clear Records")
        clearbtn.setStyleSheet("background-color: lightsteelblue; color: black")
        clearbtn.clicked.connect(self.clearData)

        hboxtab.addWidget(self.searchbtn)
        hboxtab.addWidget(searchtxtab)
        hboxtab.addWidget(self.updatebtn)
        hboxtab.addWidget(clearbtn)
        hboxtab.addWidget(self.delB)

        hboxtab2.addWidget(displaylb)
        hboxtab2.addWidget(self.displaytx)

        vboxtab.addWidget(self.tab)

        gboxtab2 = QGroupBox()
        fboxtab = QFormLayout()
        gboxtab3 = QGroupBox()
        hboxtab3 = QHBoxLayout()
        vboxtab.addLayout(hboxtab3)
        hboxtab3.addWidget(gboxtab2)
        gboxtab2.setLayout(fboxtab)
        gboxtab2.setStyleSheet("background-color: lightsteelblue")

        hboxtab3.addWidget(gboxtab3)

        fname = QLabel("First name:")
        self.fname = QLineEdit()
        self.fname.setStyleSheet("background-color: mistyrose;")

        pixpath = QLabel("Photo path:")
        self.pixpath = QLineEdit()
        self.pixpath.setStyleSheet("background-color: mistyrose;")

       
        datetlb = QLabel("Date of Update:")
        self.datettx = QLineEdit()
        self.datettx.setStyleSheet("background-color: mistyrose;")
        self.datettx.setReadOnly(True)

        fboxtab.addRow(fname, self.fname)
        fboxtab.addRow(pixpath, self.pixpath)
        fboxtab.addRow(datetlb, self.datettx)

        scrol = QScrollArea()  
        scrol.setWidget(gboxtab)
        scrol.setVerticalScrollBarPolicy(Qt.ScrollBarAlwaysOn)
        scrol.setHorizontalScrollBarPolicy(Qt.ScrollBarAlwaysOn)
        scrol.setWidgetResizable(True)
        scrol.ensureVisible(1500, 1500)
        gridtab.addWidget(scrol)

        Headerlist = ["First name", "Photopath", "Imagedata", "Date Recorded", "SNO"]
        self.tab.setRowCount(100000)
        self.tab.setColumnCount(5)
        self.tab.setHorizontalHeaderLabels(Headerlist)
        # self.tab.horizontalHeader().setStyleSheet("::section{background-color: lightsteelblue}")
        self.tab.horizontalHeader().setStyleSheet(
            "QHeaderView::section{background: lightsteelblue; padding- left: 4px; border: 3px solid red}")
        self.tab.setGeometry(150, 200, 1700, 1700)
        self.tab.setSizeAdjustPolicy(QAbstractScrollArea.AdjustToContents)
        self.tab.resizeColumnsToContents()
        self.tab.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.tab.setSelectionMode(QAbstractItemView.SingleSelection)
        self.tab.horizontalHeader().setSectionResizeMode(
            QHeaderView.Stretch)  # this makes the columns fit in the widget
        self.tab.setEditTriggers(QtWidgets.QTableWidget.NoEditTriggers)
        # this makes the QTable widget uneditable.

        self.tab.setFont(QFont("Courier"))
        self.tab.selectionModel().selectionChanged.connect(self.handle_selection_changed)

        # hiding some columns in the table.
        for column_hidden in (4,5,6,7):                          #this hides record in columns 0 and 13
            self.tab.hideColumn(column_hidden)

        # removing the above lines would make all the columns specified in the SQL statement and the headerList display in the QTablewidget.

        self.msgBox = QMessageBox()
        self.msgBox.setIcon(QMessageBox.Information)
        self.msgBox.setGeometry(100, 100, 100, 100)
        # self.update = updateRecord()

    def db_connection(self):
        try:
            # ensure there's no space before and after the = sign
            self.conn = pyodbc.connect('Driver={SQL Server}; Server=mine1;database=logistics; Trusted_Connection=yes;')
            self.cursor = self.conn.cursor()
            print("Connected to database")
            self.msgBox.setText("Program connected to database")
            self.msgBox.exec_()

        except Exception as err:
            print("couldn't connect")
            print("General error :: ", err)
            self.conn.close()
            dlg = QDialog(self)
            dlg.setWindowTitle("ALERT!")
            dlg.setGeometry(100, 100, 200, 100)
            dlg.move(300, 300)
            bb = QPushButton("ERROR!", dlg)
            bb.move(30, 50)
            dlg.exec_()

    def retrieve1(self):
        try:
            self.db_connection()
            schbox = searchtxtab.text()  # to make the searchbox active, ensure it is coded close to the db connection.
            self.cursor.execute(
                "SELECT * FROM pixxdate WHERE fname LIKE '%{}%' OR photopath LIKE '%{}%' OR imagedata LIKE '%{}%' OR dateRecorded LIKE '%{}%' "
                .format(schbox, schbox, schbox, schbox))
            result = self.cursor.fetchall()
            print(result)

            for row_number, row_data in enumerate(result):
                self.tab.insertRow(row_number)
                for column_number, column_data in enumerate(row_data):
                    item = str(column_data)
                    if (column_number == 2):
                        item = self.getImageLabel(column_data)
                        self.tab.setCellWidget(row_number, column_number,item)
                    else:
                        self.tab.setItem(row_number, column_number, QTableWidgetItem(str(column_data)))

            print(self.cursor.rowcount, "row(s) affected")
            self.conn.commit()
            msgBox = QMessageBox()
            msgBox.setIcon(QMessageBox.Information)
            msgBox.setText("DATA RETRIEVED")
            msgBox.setWindowTitle("MESSAGE FROM DB")
            msgBox.exec_()

        except Exception as err:
            print(err)
            self.conn.close()
            dlg = QDialog(self)
            dlg.setWindowTitle("ALERT!")
            dlg.setGeometry(100, 100, 200, 200)
            dlg.move(300, 300)
            dlg.setGeometry(100, 100, 200, 100)
            bb = QPushButton("ERROR", dlg)
            bb.move(30, 50)
            dlg.exec_()

        self.show()

    def getImageLabel(self):
        try:
            img = QLabel("Image")
            imglabel = QLabel(" ")
            imglabel.setScaledContents(True)
            pixmap = QPixmap()
            pixmap.loadFromData(img,'jpg')
            imglabel.setPixmap(pixmap)
            return imglabel

        except Exception as err:
            print(err)

    def handle_selection_changed(self):
        rows = {
            index.row() for index in self.tab.selectionModel().selectedIndexes()
        }
        lines = []
        for row in rows:
            texts = []
            for j in range(self.tab.columnCount()):
                item = self.tab.item(row, j)
                text = "" if item is None else item.text()
                texts.append(text)
            line = " ".join(texts)
            lines.append(line)
        output = "\n".join(lines)
        self.displaytx.setText(output)
        self.appendSelection()

    def appendSelection(self):
        f_date = datetime.datetime.now()
        strFdate = str(f_date)
        self.datettx.setText(strFdate)

        systuser = getpass.getuser()
        strGuser = str(systuser)

        rows = {index.row() for index in self.tab.selectionModel().selectedIndexes()}
        lines = []
        itemList = []
        itemList.append(self.fname)
        itemList.append(self.pixpath)
        itemList.append(self.passlb)
        itemList.append(self.datettx)

        for row in rows:
            for col in range(self.tab.columnCount()):
                itemArray = self.tab.item(row, col)
                text = "" if itemArray is None else itemArray.text()
                if (col == 0):
                    itemList[0].setText(text)
                if (col == 1):
                    itemList[1].setText(text)
                if (col == 2):
                    itemList[2].setText(text)
                if (col == 3):
                    itemList[3].setText(text)

    
        def clearData(self):
        self.tab.clearContents()


if __name__ == '__main__':
    app = QApplication(sys.argv)
    window = userUpdate()
    window.show()
    app.exec()

The database table:

fname varchar(50),
imagedata varbinary(max),
Photopath varchar(500),
dateRecorded datetime DEFAULT getdate()

Microsoft database --- pyodbc module --- Python

Answered by CXX22

Sep 16, 2021

Hey!
I've fixed it just now.

I discovered the images were in png format, I used jpg there in the getImageLabel method there. I changed it to png and it works fine now.

View full answer

CXX22 · 2021-09-16T20:23:25Z

CXX22
Sep 16, 2021
Author

@hugovk @radarhere please see this and help.

1 reply

CXX22 Sep 16, 2021
Author

The main focus is here:

    def retrieve1(self):
    try:
        self.db_connection()
        schbox = searchtxtab.text()  # to make the searchbox active, ensure it is coded close to the db connection.
        self.cursor.execute(
            "SELECT * FROM pixxdate WHERE fname LIKE '%{}%' OR photopath LIKE '%{}%' OR imagedata LIKE '%{}%' OR dateRecorded LIKE '%{}%' "
            .format(schbox, schbox, schbox, schbox))
        result = self.cursor.fetchall()
        print(result)

        for row_number, row_data in enumerate(result):
            self.tab.insertRow(row_number)
            for column_number, column_data in enumerate(row_data):
                item = str(column_data)
                if (column_number == 2):
                    item = self.getImageLabel(column_data)
                    self.tab.setCellWidget(row_number, column_number,item)
                else:
                    self.tab.setItem(row_number, column_number, QTableWidgetItem(str(column_data)))

        print(self.cursor.rowcount, "row(s) affected")
        self.conn.commit()
        msgBox = QMessageBox()
        msgBox.setIcon(QMessageBox.Information)
        msgBox.setText("DATA RETRIEVED")
        msgBox.setWindowTitle("MESSAGE FROM DB")
        msgBox.exec_()

    except Exception as err:
        print(err)
        self.conn.close()
        dlg = QDialog(self)
        dlg.setWindowTitle("ALERT!")
        dlg.setGeometry(100, 100, 200, 200)
        dlg.move(300, 300)
        dlg.setGeometry(100, 100, 200, 100)
        bb = QPushButton("ERROR", dlg)
        bb.move(30, 50)
        dlg.exec_()

    self.show()

def getImageLabel(self):
    try:
        img = QLabel("Image")
        imglabel = QLabel(" ")
        imglabel.setScaledContents(True)
        pixmap = QPixmap()
        pixmap.loadFromData(img,'jpg')
        imglabel.setPixmap(pixmap)
        return imglabel

    except Exception as err:
        print(err)

CXX22 · 2021-09-16T21:09:48Z

CXX22
Sep 16, 2021
Author

Hey!
I've fixed it just now.

I discovered the images were in png format, I used jpg there in the getImageLabel method there. I changed it to png and it works fine now.

0 replies

CXX22 · 2021-09-16T23:50:56Z

CXX22
Sep 16, 2021
Author

Having been able to achieve some steps, can you help me here:

   def appendSelection(self):
           try:
               rows = {index.row() for index in self.tab.selectionModel().selectedIndexes()}
               lines = []
               itemList = []
   
               itemList.append(self.fname)
               itemList.append(self.pixpath)
   
               itemList.append(self.passlb)
   itemList.append(self.datetx)
   
              
   
               for row in rows:
                   for col in range(self.tab.columnCount()):
                       itemArray = self.tab.item(row, col)
                       text = "" if itemArray is None else itemArray.text()
                       if (col == 0):
                           itemList[0].setText(text)
                       if (col == 1):
                           itemList[1].setText(text)
                       if (col == 2):
                           itemList[2].setText(text)
                      
   
                       if (col == 3):
                        itemList[3].setText(text)

Column 2 is the image column. I want to append it to a qpixmap inside a formlayout. The qpixmap in the formlayout is self.passlb. it is a QLabel.

So how do I append it just like other data (text) get appended to form qLineEdits.

I tried:

if (col == 2):
itemList[2].setPixmap()

But it didn't work.
Kindly help with the right line of code.

2 replies

radarhere Sep 17, 2021
Collaborator

Am I right in saying that your image is appearing correctly, and you're now just asking about how to move it elsewhere in your PyQt5 UI? You wouldn't rather ask this question in a community that specialises in PyQt5?

CXX22 Sep 17, 2021
Author

Please try to help if you can

radarhere · 2021-09-17T10:23:10Z

radarhere
Sep 17, 2021
Collaborator

I still have trouble running your code, so I have taken it, removed pyodbc, simplified some other things, and taken a best guess at what you are trying to do.

Open this file, run the Python script inside, and see if it does what you want - script.py.zip

3 replies

CXX22 Sep 17, 2021
Author

Hello @radarhere , thank you sooo much for your effort in making sure I achieve my desired goal and smile. I have run your file, it works as expected, but I am yet to connect it to my database table to see how it would work. I wanna study your append function to see if I could grasp some things then and apply the logic. I will keep you updated.

CXX22 Sep 17, 2021
Author

hello @radarhere you are a super man!!!!!!!!!!!!

You won't understand how happy I am to have finally resolved this. I am so elated and grateful to you. Although it was tough and would have been tougher but you came to my rescue and made it easier, you also gave me some hints on where to do further research. For this, I am very grateful.

My code works fine now. @hugovk , all is okay for now.

I need to move on to another step where another problem awaits me. smiles

Thanks once again.

CXX22 Sep 17, 2021
Author

Eureka!

CXX22 · 2021-10-06T21:26:15Z

CXX22
Oct 6, 2021
Author

Hello @radarhere , in an attempt to update an already saved photo, I have this code:

try:
    img = QLabel("Image")
    imglabel = QLabel(" ")
    imglabel.setScaledContents(True)
    pixmap = QPixmap()
    pixmap.loadFromData(img,'jpg')
    imglabel.setPixmap(pixmap)
    return imglabel

in a situation whereby the images can be in jpg, jpeg, png, or jfif format, how should I write the loadFromData method?

pixmap.loadFromData(img, 'jpg', 'jpeg', 'png')?

Or

pixmap.loadFromData(img, 'jpg, jpeg, png')?

Or

pixmap.loadFromData(img, 'jpg' 'png' 'jpeg')?

2 replies

radarhere Oct 6, 2021
Collaborator

Hi. This is not the place for PyQt questions. This is the place for Pillow questions. I am not an expert in what you are talking about. https://www.riverbankcomputing.com/support/pyqt has some advice for getting help with PyQt.

CXX22 Oct 6, 2021
Author

Alright, thanks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Displaying retrieved database image inside Qtablewidget in Python #5718

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 5 comments 8 replies

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

Select a reply

Displaying retrieved database image inside Qtablewidget in Python #5718

CXX22 Sep 16, 2021

Replies: 5 comments · 8 replies

CXX22 Sep 16, 2021 Author

CXX22 Sep 16, 2021 Author

CXX22 Sep 16, 2021 Author

CXX22 Sep 16, 2021 Author

radarhere Sep 17, 2021 Collaborator

CXX22 Sep 17, 2021 Author

radarhere Sep 17, 2021 Collaborator

CXX22 Sep 17, 2021 Author

CXX22 Sep 17, 2021 Author

CXX22 Sep 17, 2021 Author

CXX22 Oct 6, 2021 Author

radarhere Oct 6, 2021 Collaborator

CXX22 Oct 6, 2021 Author

CXX22
Sep 16, 2021

Replies: 5 comments 8 replies

CXX22
Sep 16, 2021
Author

CXX22 Sep 16, 2021
Author

CXX22
Sep 16, 2021
Author

CXX22
Sep 16, 2021
Author

radarhere Sep 17, 2021
Collaborator

CXX22 Sep 17, 2021
Author

radarhere
Sep 17, 2021
Collaborator

CXX22 Sep 17, 2021
Author

CXX22 Sep 17, 2021
Author

CXX22 Sep 17, 2021
Author

CXX22
Oct 6, 2021
Author

radarhere Oct 6, 2021
Collaborator

CXX22 Oct 6, 2021
Author