Inserting image file with PyQt5 in MySQL database table column

Question

I have been having difficulty in having an image inserted in the MySQL table. I already used the QFileDialog to browse and display the image. However, after saving, whenever I clicked to check the BLOB in the table column nothing would be saved. I have tried making some researches and I have realized that I am wrong with inserting the image as a text file in the SQL command. Please teachers, what is the best practice to restructure my following code and achieve my aim of getting the image inserted?

class TestReg(QWidget):

    def __init__(self):
        super().__init__()
        self.ui = Ui_TestRegForm()
        self.ui.setupUi(self)

        #Calling browseImage
        self.ui.browse_btn.clicked.connect(self.browseImage)

        #Calling insertPers
        self.ui.save_btn.clicked.connect(self.insertPers)

    #Browses person's picture and displays using label called image_label
    def browseImage(self):
        file_name = QFileDialog.getOpenFileName(self, 'Open File', 'c:\\', 'Image Files (*.png *.jpg *gif)')
        image_path = file_name[0]
        pixmap = QPixmap(image_path)
        self.ui.image_label.setPixmap(QPixmap(pixmap))

    #Inserts person into database
    def insertPers(self):
        try:
            con = MySQLdb.connect(host="localhost", user="root", password="", database="somedb")
            with con:
                cur = con.cursor()
                cur.execute("INSERT INTO persons(name, photo)" "VALUES('%s', '%s')" % (''.join(self.ui.name_edit.text()), ''.join(self.ui.image_label.text()))
                con.commit()
                self.displayDialog(QMessageBox.Information, "Registration", "Person has been added successfully")
        except MySQLdb.Error as e:
            self.displayDialog(QMessageBox.Warning, "Registration", str(e))
        except TypeError as e:
            self.displayDialog(QMessageBox.Warning, "Registration", str(e))
        except ValueError as e:
            self.displayDialog(QMessageBox.Warning, "Registration", str(e))

eyllanesc · Accepted Answer · 2020-09-19 04:20:59Z

2

You should not concatenate the variables to build the query, but rather use the placeholders, otherwise your code will be susceptible to SQL Injection attacks. On the other hand, you must convert the QPixmap, not the text, into bytes using a QBuffer as an intermediary:

con = MySQLdb.connect(host="localhost", user="root", password="", database="somedb")
with con:
    cur = con.cursor()
    name = self.ui.name_edit.text()
    buff = QBuffer()
    buff.open(QIODevice.WriteOnly)
    pixmap = QPixmap(self.ui.image_label.pixmap())
    pixmap.save(buff, "PNG")
    binary_img = buff.data().toBase64().data()
    cur.execute("INSERT INTO persons(name, photo) VALUES (%s, %s)", (name, binary_img))
    con.commit()

edited Sep 19, 2020 at 4:20

answered Sep 18, 2020 at 18:57

eyllanesc

246k19 gold badges205 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

YoungE Over a year ago

Thank you great guru. May you be blessed.

YoungE Over a year ago

It's now displaying this error: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near'\*89PNG\\r\\n\\x00\\x00...' at line 1")

eyllanesc Over a year ago

@YoungE 1) Have you copied all my code? 2) how did you create the table? 3) I'll test it later. 4) Please answer each question with the number I have used.

YoungE Over a year ago

please forgive me. Your first solution actually worked. I figured out now that there is a part of your code where I didn't notice and correct in my previous code and that is where I put the placeholders in inverted commas. I got to figure out now after re-inspecting your code that yours are without the inverted commas. May you live long and blessed Guru!

Collectives™ on Stack Overflow

Inserting image file with PyQt5 in MySQL database table column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related