Saving QPixmap to PostgreSQL without using prepared queries

Question

There are many answers on how to save QPixmap (or QImage) to Sqlite using binding methods, but what if my database doesn't support binding methods (like PostgreSQL). Is there a way to do this in raw SQL?

I converted the example from this Qt Wiki example to PyQt to better explain my problem:

import sys

from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtSql import *

app = QApplication([])

db = QSqlDatabase.addDatabase( "QSQLITE" )
db.setDatabaseName( ":memory:" )
db.open()

query = QSqlQuery(db)
query.exec("CREATE TABLE IF NOT EXISTS imgTable (filename TEXT, imagedata BLOB)")

screen = app.primaryScreen()
inPixmap = screen.grabWindow(0)
byteArray = QByteArray()
buffer = QBuffer(byteArray)
buffer.open(QIODevice.WriteOnly)
inPixmap.save(buffer, "PNG")

# Insert image bytes into the database
query.prepare("INSERT INTO imgTable (filename, imagedata) VALUES ('screenshot.png', :imageData)")
query.bindValue(":imageData", byteArray)
query.exec()

# Get image data back from database
query.exec("SELECT imagedata from imgTable")
query.first()
#outByteArray = query.value(0).toByteArray() # doesn't work
outByteArray = query.value(0)
outPixmap = QPixmap()
outPixmap.loadFromData(outByteArray)

db.close()

# Display image
myLabel = QLabel()
myLabel.setPixmap( outPixmap )
myLabel.show()

sys.exit(app.exec())

This example works on my Win machine. It grabs the screenshot, stores it to Sqlite database, retrieves it from database and displays it as QLabel.

My problem: It is easy to store image to Sqlite database when you can use query.bindValue(":imageData", byteArray)

But my PostgreSQL database doesn't support prepared queries. For example, for PostgreSQL db.driver().hasFeature(QSqlDriver.PreparedQueries) returns False.

So, I can't use:

query.prepare("INSERT INTO imgTable (filename, imagedata) VALUES ('screenshot.png', :imageData)")
query.bindValue(":imageData", byteArray)
query.exec()

I can only use raw SQL:

query.exec("INSERT INTO imgTable (filename, imagedata) VALUES ('screenshot.png', i_dont_know_how_to_store_byteArray_here)")

What this query should look like in order to store value to PostgreSQL database, in which case field imagedata is bytea and the table is created like this:

query.exec("CREATE TABLE IF NOT EXISTS imgTable (filename TEXT, imagedata BYTEA)")

user_6622587 · Answer

In this case the solution is to encode the bytes in base64 and concatenate the string (obviously you have to verify the query since by not using placeholder you can be susceptible to SQL Injection attacks):

import os
import sys

from PyQt5 import QtCore, QtSql, QtGui, QtWidgets


def create_connections():
    db = QtSql.QSqlDatabase.addDatabase("QPSQL")
    db.setHostName("localhost")
    db.setDatabaseName("db")
    db.setUserName("user")
    db.setPassword("pass")
    if not db.open():
        print(db.lastError().text())
        return False
    query_str = (
        """CREATE TABLE IF NOT EXISTS imgTable (filename TEXT, imagedata BYTEA)"""
    )
    query = QtSql.QSqlQuery(query_str)
    if not query.exec_():
        print(query.lastError().text())
    return True


if __name__ == "__main__":
    app = QtWidgets.QApplication(sys.argv)
    if not create_connections():
        sys.exit(-1)

    screen = QtWidgets.QApplication.primaryScreen()
    inPixmap = screen.grabWindow(0)
    byteArray = QtCore.QByteArray()
    qbuffer = QtCore.QBuffer(byteArray)
    qbuffer.open(QtCore.QIODevice.WriteOnly)
    inPixmap.save(qbuffer, "PNG")
    data = byteArray.toBase64().data().decode()

    query = QtSql.QSqlQuery()
    RAW_SQL = """INSERT INTO imgTable (filename, imagedata) VALUES ('screenshot.png', '{}')""".format(
        data
    )
    if not query.exec_(RAW_SQL):
        print(query.lastError().text())
        sys.exit(-1)

    q = QtSql.QSqlQuery("SELECT filename, imagedata FROM imgTable")
    if not q.first():
        print(q.lastError().text())
        sys.exit(-1)

    filename = q.value(0)
    data = q.value(1)

    ba = QtCore.QByteArray.fromBase64(data)
    pixmap = QtGui.QPixmap()
    pixmap.loadFromData(ba)
    w = QtWidgets.QWidget()
    label_pixmap = QtWidgets.QLabel()
    label_text = QtWidgets.QLabel()
    lay = QtWidgets.QVBoxLayout(w)
    lay.addWidget(label_pixmap)
    lay.addWidget(label_text)
    label_pixmap.setPixmap(pixmap)
    label_text.setText(filename)
    w.show()
    app.exec_()

Saving QPixmap to PostgreSQL without using prepared queries

1 Answers