sending full database table with images through tcp socket

rhx9 · 16 Nov 2021, 05:00

Hi,
I have a project which uses mysql database to store the members information, the mysql server lives in a remote machine, currently i want to replace the mysql server with an sqlite database and have a server app that sends and recieve data from the main application.

i have the following sqllite structure:

CREATE TABLE "members" (
	"rfid"	TEXT NOT NULL,
	"memberName"	TEXT NOT NULL,
	"avatar"	BLOB NOT NULL,
	"memberShipExpiary"	TEXT NOT NULL,
	"notes"	TEXT DEFAULT NULL,
	"unrestricted"	TEXT NOT NULL DEFAULT 'no',
	PRIMARY KEY("rfid")
)

the server code is:


import sys
import os
import signal
import traceback
import json
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtCore as qtc
from PyQt5 import QtGui as qtg
from PyQt5.QtNetwork import QHostAddress, QTcpServer

from PyQt5 import QtCore as qtc
from PyQt5.QtNetwork import QHostAddress, QTcpServer

from PyQt5 import QtSql


class MainWindow(qtw.QWidget):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.resize(400, 300)
        self.gridLayout = qtw.QGridLayout(self)
        self.gateState = qtw.QLabel(self)
        self.gateState.setText("connect to 127.0.0.1:8000")

        self.gridLayout.addWidget(self.gateState, 0, 0, 1, 1)
        self.db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName("gym.sqlite")
        self.db.open()
        self.tcpServer = QTcpServer(self)
        # allow multiple clients to connect, store clients in the connectedclients variable
        self.connectedclients = []
        PORT = 8000
        if not self.tcpServer.listen(QHostAddress.SpecialAddress.AnyIPv4, PORT):
            print("cant listen!")
        self.tcpServer.newConnection.connect(self.on_newConnection)

        self.show()

    @qtc.pyqtSlot(int)
    def readmessage(self, clientid):
        print("reading incoming data")
        # read incomming data
        instr = self.connectedclients[clientid].readAll()
        print("data was read")
        recievedmsg = str(instr, encoding='utf-8')
        messagejson = json.loads(recievedmsg)
        if messagejson['type'] == "fetch_all_members":
            client = self.connectedclients[clientid]
            allmembers = self.fetch_all_members()
            members = {"type": "fetch_all_members", "data": []}
            for rownum, row in enumerate(allmembers):
                members['data'].append({"rownum": rownum, "row": row})
            message = bytes(json.dumps(members), encoding="utf-8")
            if client.state() == 3:
                print("client is connected")
                client.waitForBytesWritten(3000)
                client.write(message)
                client.waitForBytesWritten(3000)

    @qtc.pyqtSlot()
    def fetch_all_members(self):
        print("fetch_all_members")
        if self.db.isOpen():
            results = self.db.exec_("SELECT * FROM members")
            if not results.lastError().isValid():
                print("members were fetched")
                members = []
                while results.next():
                    members.append(
                        {"rfid": results.value("rfid"),
                         "memberName": results.value("memberName"),
                         "avatar": results.value("avatar").toBase64().data().decode(),
                         "memberShipExpiary": results.value("memberShipExpiary"),
                         "notes": results.value("notes"),
                         "unrestricted": results.value("unrestricted")})
                return members

    def on_newConnection(self):
        # Get a QTcpSocket from the QTcpServer
        print("got connection")
        # add the clinet to the connectedclients variable
        self.connectedclients.append(self.tcpServer.nextPendingConnection())
        clientid = len(self.connectedclients)-1
        self.connectedclients[clientid].readyRead.connect(
            lambda: self.readmessage(clientid))


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()
    # or QtWidgets.QApplication.exit(0)


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()
    # app.aboutToQuit(GPIO.cleanup())
    ret = app.exec_()
    sys.exit(ret)

the main application code is :


import sys
import os
import signal
import traceback
import json
import base64

from PyQt5 import QtCore as qtc
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtGui as qtg
from PyQt5.QtNetwork import QTcpSocket, QAbstractSocket


class MainWindow(qtw.QMainWindow):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.centralwidget = qtw.QWidget(self)
        self.centralwidget.setObjectName("centralwidget")
        self.gridLayout = qtw.QGridLayout(self.centralwidget)
        self.gridLayout.setObjectName("gridLayout")
        self.membersTable = qtw.QTableView(self.centralwidget)
        self.membersTable.setEnabled(True)
        self.membersTable.setSelectionMode(
            qtw.QAbstractItemView.SingleSelection)
        self.membersTable.setSelectionBehavior(
            qtw.QAbstractItemView.SelectRows)
        self.model = qtg.QStandardItemModel()

        self.membersTable.setModel(self.model)
        self.membersTable.showNormal()
        self.membersTable.verticalHeader().setSectionResizeMode(
            qtw.QHeaderView.ResizeToContents)
        self.membersTable.horizontalHeader().setSectionResizeMode(qtw.QHeaderView.Stretch)
        self.gridLayout.addWidget(self.membersTable, 2, 0, 1, 1)
        self.setCentralWidget(self.centralwidget)

        self.tcpSocket = QTcpSocket()
        self.tcpSocket.setSocketOption(QTcpSocket.KeepAliveOption, 1)

        self.tcpSocket.readyRead.connect(self.on_read)

        self.tcpSocket.connected.connect(self.on_connected)

        self.socket_connect()
        self.show()

    @qtc.pyqtSlot()
    def socket_connect(self):
        print("socket state: ", self.tcpSocket.state())
        while self.tcpSocket.state() == 0:
            print("tcpsocket connecting")
            try:
                self.tcpSocket.connectToHost(
                    '127.0.0.1', 8000, qtc.QIODevice.ReadWrite)
            except Exception as e:
                print("exception in socket connecting")
                print(e)
            else:
                if self.tcpSocket.state() == 3:
                    print("socket connected successfully")
        print("socket state: ", self.tcpSocket.state())

    @qtc.pyqtSlot()
    def on_connected(self):
        print("connected to socket")
        message = bytes(json.dumps(
            {"type": "fetch_all_members", "data": None}), encoding="utf-8")
        print("fetch_all_members")
        self.tcpSocket.waitForConnected(1000)
        self.tcpSocket.waitForBytesWritten()
        print("connected")
        self.tcpSocket.write(message)
        print("sent 'fetch_all_members' message")

    def on_read(self):
        print("reading message from server")
        message = self.tcpSocket.readAll()
        message = message.data()
        messagejson = json.loads(message)
        if messagejson['type'] == "fetch_all_members":
            try:
                members = messagejson['data']
                for member in members:
                    for datanum, (key, data) in enumerate(member['row'].items()):
                        item = qtg.QStandardItem()
                        item.setEditable(False)
                        if key == "avatar":
                            qimg = qtg.QImage.fromData(base64.b64decode(data))
                            pixmap = qtg.QPixmap.fromImage(qimg)
                            data = qtc.QVariant(pixmap.scaledToHeight(
                                200, qtc.Qt.SmoothTransformation))
                            role = qtc.Qt.DecorationRole
                        else:
                            role = qtc.Qt.DisplayRole
                        item.setData(data, role)
                        self.model.setItem(member['rownum'], datanum, item)

            except json.decoder.JSONDecodeError:
                print("failed to decode json")


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."""
    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()
    # or QtWidgets.QApplication.exit(0)


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()
    # app.aboutToQuit(GPIO.cleanup())
    ret = app.exec_()
    sys.exit(ret)

as you can see i have one command right now "fetch_all_members" the main application sends this command to the server app->the server app fetches all the members from database and convert them into a json string to be sent through tcp

Then the application parses the json and populate a QTableView with the data

the example works fine if i have one entry in the database with a relatively small avatar image, if I add other entries in the database and increase the image size then i get the following error:

json.decoder.JSONDecodeError: Unterminated string starting at: line 1 column 9321 (char 9320)

this is because the data is not sent in chunks instead of one big string
so the json decoder can't decode a chunk of the string.

what is the best way to avoid this? can i tell the server to send the data as a single string and not split it ? is there a better way to achieve this ?

Regards

StarterKit · 16 Nov 2021, 14:24

@rhx9 said in sending full database table with images through tcp socket:

can i tell the server to send the data as a single string and not split it ?

No, you can't. There are at least 2 reasons behind: 1) data are sent in packets through the network, so if your data is too large server will split it into packets, 2) data transmission is usually slower than your CPU and it may take time - i.e. far end of your message may arrive much later than its beginning.

As you named it - "message is sent in chunks" - so the answer will be - you need first to collect all chunks from the network and then decode the message.

JonB · 16 Nov 2021, 14:36

@rhx9 said in sending full database table with images through tcp socket:

the mysql server lives in a remote machine, currently i want to replace the mysql server with an sqlite database and have a server app that sends and recieve data from the main application.

Separately from @StarterKit's comments.

Your desire seems strange to me. If you have a multi-user database on a remote server then MySQL --- or other server-based RDBMS --- seems like the correct choice. I don't know why you would want to change that to a SQLite system. How do you think your new server code handles transactions and multiple simultaneous clients, for example? Seems likely to be most non-robust to me.....

Also your (multi-connection) server is an application with a UI. This too is very strange for a server. I don't know what/why you are trying to achieve.

rhx9 · wrote on 16 Nov 2021, 14:24

@StarterKit said in sending full database table with images through tcp socket:

As you named it - "message is sent in chunks" - so the answer will be - you need first to collect all chunks from the network and then decode the message.

And how might i achieve that ? care to provide a simple code example ?
I'm not even sure i should be sending the data as json, maybe there is a better way/format to send the data?

Regards

rhx9 · 16 Nov 2021, 14:52

@JonB said in sending full database table with images through tcp socket:

@rhx9 said in sending full database table with images through tcp socket:

the mysql server lives in a remote machine, currently i want to replace the mysql server with an sqlite database and have a server app that sends and recieve data from the main application.

Separately from @StarterKit's comments.

Your desire seems strange to me. If you have a multi-user database on a remote server then MySQL --- or other server-based RDBMS --- seems like the correct choice. I don't know why you would want to change that to a SQLite system. How do you think your new server code handles transactions and multiple simultaneous clients, for example? Seems likely to be most non-robust to me.....

Also your (multi-connection) server is an application with a UI. This too is very strange for a server. I don't know what/why you are trying to achieve.

Well, this is a turnstile control program, the server app has a ui(not the one in my example, i removed it for simplicity) that provides info about the member trying to enter the building, another application is run on a control pc on the same network, the control app is used to add/remove/update the members info and permissions, the mysql server is on the same machine as the server app (both are on a raspberrypi 3).
we noticed that the mysql server uses a bit much resources from the raspberry pi than we like, also we are trying to replace the raspberry pi 3 with a raspberry pi zero which has much less CPU and GPU power, but is a much better choice in our case due to low power consumption and heating issues, also we want to run the raspberry pi in a read only mode and have only the database file on a different drive to reduce SD card wear, which could be achieved with mysql but is easier with SQLite.

Although, the system we run is only controlled by one client, the example code i wrote handles multiple clients.

Regards

JonB · 21 Nov 2021, 23:49

@rhx9
All that is fine for your usage of SQLite instead of MySQL, so long as you know what you are doing.

And how might i achieve that ? care to provide a simple code example ?

I'm not even sure i should be sending the data as json, maybe there is a better way/format to send the data?

The JSON encoding seems not unreasonable. For the coding you need to use Qt's signals and slots.

Try not to use any waitFor... calls, as they block.
message = self.tcpSocket.readAll(): This does not guarantee to read anything and everything already sent to you. readAll() just reads whatever is available at the instant it is called, and the readyRead() signal can and will be raised multiple times, calling readmessage() slot each time. So to be robust you must buffer the bytes received so far, appending the new stuff each time. You cannot know that it has received all of your JSON message until either you implement a protocol which can recognise this (e.g. message-length byte count exchanged, or special "marker" at end of JSON message) or you keep retrying JSON parse of what you have received until it no longer says "premature end of data". One possible way of handling the buffering is by using QDataStream transactions around your sockets.

SGaist · 16 Nov 2021, 22:00

Hi,

How many "terminals" are you going to have in your system ?

rhx9 · S SGaist 16 Nov 2021, 20:06

@SGaist said in sending full database table with images through tcp socket:

Hi,

How many "terminals" are you going to have in your system ?

I'm not sure what do you mean by "Terminal".
The system will eventually consist of two programs:
1- a server program in PyQT5 that runs on the raspberrypi, it reads the rfid cards and decides whether to let the person in or not by controlling a relay, it also has the function of replying to messages sent by a the control program and takes actions on these messages that alter the database.
2- a control program in PyQT5 also running on a different machine, the program connects to the server program though tcp and can add/remove/alter the users and their permissions.

SGaist · wrote on 18 Nov 2021, 21:30

By terminal I mean the number of devices that are going to connect to your main server.

Depending on that, you device could also be simply thin client that work directly on the server.

JoeCFD · 21 Nov 2021, 23:38

Is it possible to get the data through a web server on the server side and download it by polling on the client side?
It is easy to set-up a web-server.

rhx9 · J JoeCFD 18 Nov 2021, 22:16

@JoeCFD part of why we are moving to sqlite is to minimize resource usage, running a web server would defeat that purpose

rhx9 · 22 Nov 2021, 08:04

@JonB so I've used datastreams to send messages, and although it helps it also has problems

the main problem i have is it's not possible to queue messages, so if the client pushes 2 buttons that sends different commands very quickly it would not send the message.
the following example illustrate the problem:

the server:

import sys
import json
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtCore as qtc
from PyQt5 import QtGui as qtg

from PyQt5.QtNetwork import QHostAddress, QTcpServer
from PyQt5 import QtSvg
from PyQt5 import QtSql
from PyQt5.QtMultimedia import QSound

class Server(qtw.QDialog):
    def __init__(self):
        super().__init__()

        self.tcp_server = QTcpServer()
        self.connectedclients = []
        self.connectedclients_datastreams = []

        if not self.tcp_server.listen(QHostAddress.AnyIPv4, 8000):
            print("can't listen!")
        self.tcp_server.newConnection.connect(self.on_new_connection)
        self.blockSize = 0

    @qtc.pyqtSlot(int)
    def readmessage(self, clientid):
        client = self.connectedclients[clientid]
        print("reading data from client")
        print("reading incoming data")
    

        instr = self.connectedclients_datastreams[clientid]
        instr.startTransaction()
        if self.blockSize == 0:
            print("self.blockSize == 0")
            if client.bytesAvailable() < 4:
                print("self.tcpSocket.bytesAvailable() < 4")
                return
            self.blockSize = instr.readUInt32()
            print("blocksize: ", self.blockSize)
            print("self.tcpSocket.bytesAvailable(): ",client.bytesAvailable())
        if client.bytesAvailable() < self.blockSize:
            print("client.bytesAvailable() < self.blockSize:", client.bytesAvailable() < self.blockSize)
            return
        
        self.blockSize = 0
        recievedmsg = str(instr.readString(), encoding='utf-8')
        
        print("transaction started")


        if (not instr.commitTransaction()):
            print("wait for more data")
            return

        print("recieved command: '",recievedmsg,"'")
        try:
            messagejson = json.loads(recievedmsg)
            if messagejson['type'] == "open_gate":
                print("opening gate")
            elif messagejson['type'] == "settime":
                print("setting os time")
            elif messagejson['type'] == "shutdown_gate":
                print("shutting down the system")
        except json.decoder.JSONDecodeError:
            print("failed to parse json message")
    
    @qtc.pyqtSlot()
    def on_new_connection(self):
        print("got connection")
        self.connectedclients.append(self.tcp_server.nextPendingConnection())
        
        clientid = len(self.connectedclients)-1
        self.connectedclients_datastreams.append(qtc.QDataStream(self.connectedclients[clientid]))
                        
        self.connectedclients_datastreams[clientid].setVersion(qtc.QDataStream.Qt_5_15)
        self.connectedclients[clientid].readyRead.connect(
            lambda: self.readmessage(clientid))

if __name__ == '__main__':
    app = qtw.QApplication(sys.argv)
    server = Server()
    sys.exit(server.exec_())

the client :

import time
import json
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtCore as qtc
from PyQt5 import QtGui as qtg
from PyQt5 import QtSvg
from PyQt5.QtNetwork import QTcpSocket, QAbstractSocket

class Client(qtw.QWidget):
    def __init__(self):
        super().__init__()
        self.gridLayout = qtw.QGridLayout(self)
        self.open_gate_btn = qtw.QPushButton("open gate")
        self.open_gate_btn.clicked.connect(self.open_gate)
        self.gridLayout.addWidget(self.open_gate_btn)
        self.tcpSocket = QTcpSocket()
        self.tcpSocket.setSocketOption(QTcpSocket.KeepAliveOption, 1)
        self.blockSize = 0
        self.tcp_block = qtc.QByteArray()
        self.tcp_out_stream = qtc.QDataStream(self.tcp_block, qtc.QIODevice.ReadWrite)
        self.tcp_out_stream.setVersion(qtc.QDataStream.Qt_5_15)

        self.tcpSocket.readyRead.connect(self.on_read)
        self.tcpSocket.error.connect(self.displayError)
        self.tcpSocket.connected.connect(self.on_connected)
        self.tcpSocket.disconnected.connect(self.socket_disconnected)

        self.socket_connect()
    @qtc.pyqtSlot()
    def socket_connect(self):
        print("socket state: ", self.tcpSocket.state())
        if self.tcpSocket.state() == 0:
            print("tcpsocket connecting")
            try:
                self.tcpSocket.connectToHost('127.0.0.1', 8000, qtc.QIODevice.ReadWrite)
            except Exception as e:
                print("exception in socket connecting")
                print(e)
            else:
                if self.tcpSocket.state() == 3:
                    print("socket connected successfully")
 
    @qtc.pyqtSlot()
    def on_connected(self):
        current_time = time.time()
        self.send_tcp_command({"type": "settime", "data": int(current_time)})
        self.send_tcp_command({"type": "open_gate", "data": None})
    
    @qtc.pyqtSlot()
    def open_gate(self):
        self.send_tcp_command({"type": "open_gate", "data": None})
    @qtc.pyqtSlot()
    def socket_disconnected(self):
        print("socket got disconnected")

    def on_read(self):
        print("reading message from server")
        instr = qtc.QDataStream(self.tcpSocket)
        instr.setVersion(qtc.QDataStream.Qt_5_15)
        if self.blockSize == 0:
            print("self.blockSize == 0")
            if self.tcpSocket.bytesAvailable() < 4:
                # print("self.tcpSocket.bytesAvailable() < 2")
                return
            self.blockSize = instr.readUInt32()
            # print("blocksize: ", self.blockSize)
            # print("self.tcpSocket.bytesAvailable(): ",self.tcpSocket.bytesAvailable())
        if self.tcpSocket.bytesAvailable() < self.blockSize:
            print(self.tcpSocket.bytesAvailable() < self.blockSize)
            return
        # Print response to terminal, we could use it anywhere else we wanted.
        message = str(instr.readString(), encoding='utf-8')

        messagejson = json.loads(message)

    
    @qtc.pyqtSlot(dict)
    def send_tcp_command(self, message_json):
        print("func:send_tcp_command","sending the following message: ",message_json)
        message = bytes(json.dumps(message_json), encoding="utf-8")
        self.tcp_out_stream.device().seek(0)
        self.tcp_out_stream.writeUInt32(0)
        self.tcp_out_stream.writeString(message)
        self.tcp_out_stream.device().seek(0)
        self.tcp_out_stream.writeUInt32(self.tcp_block.size() - 4)
        self.tcpSocket.write(self.tcp_block)

    @qtc.pyqtSlot(QAbstractSocket.SocketError)
    def displayError(self, socketError):
        print("socket errrrr")
        if socketError == QAbstractSocket.RemoteHostClosedError:
            pass
        else:
            print("The following error occurred: %s." %
                  self.tcpSocket.errorString())

if __name__ == '__main__':
    import sys

    app = qtw.QApplication(sys.argv)
    client = Client()
    client.show()
    sys.exit(app.exec_())

note that when the connection is established (on_connected) the client immediately sends two commands , but on the server we notice that only the first message have arrived. after that if the user presses the open gate button the message would be sent fine since the socket is not busy

Regards

JonB · R rhx9 21 Nov 2021, 23:49

@rhx9
I don't know what the logic of your code implements. Messages written are always sent, and TCP does not lose any bytes. There is no difference whether a socket is "busy".

Verify that the total number of bytes sent is the same as the total number of bytes received at the other end.

I'm not sure what your protocol implements. I do not see evidence of the receiver splitting the input into separate messages.

Understand that TCP is a continuous stream of bytes. It is not record based. readyRead() signal can be called multiple times (even within one sent "message"): the only guarantee is that it will report anywhere between just 1 and the total number of bytes sent. Do not assume you will get separate calls for each message.

I have a feeling you do not allow for two messages arriving in one readyRead()? When you have picked out the first message from the stream, if more bytes than that have (already) arrived it is up to you to "buffer" those bytes beyond the first message so that they will be read for (as the start of) the second message. I believe you are not doing that, so you do not see the second message. This is more likely to happen when the messages are sent close together, which is doubtless the case when you click two sends rapidly.