MQTT and SQLite3 communication using Python-CodePudding

I am working on an MQTT application that communicates with an SQLite Database (I am using python and SQLite3) First, I've created my database, you will find below the corresponding python code. And then wrote the Publisher and Subscriber scripts. The problem is that when executing the Subscriber Script to get the user name, I am getting this output :

Client created successfully ! 
Client connected 
The default result code for this connection is: 0
THIS IS THE ON_MESSAGE FUNCTION: Ok
The received ID is: 
b'1004'
the name is: 
('User2',)

while the expected output is

Client created successfully ! 
Client connected 
The default result code for this connection is: 0
THIS IS THE ON_MESSAGE FUNCTION: Ok
The received ID is: 
'1004'
the name is: 
'User2'

Can someone tell me how to solve that problem please? If u need further information just let me know about it

Thank you in advance.

This is The Python Code of the Subscriber

import paho.mqtt.client as mqtt
import time 
import sqlite3

port = 1883
brocker = "localhost"
topic = "Auth"

def on_connect(client, userdata, flags, rc):

    print("Client connected \n")
    print("The default result code for this connection is: " str(rc)) 

def on_message(client, userdata, message):

    print("THIS IS THE ON_MESSAGE FUNCTION: Ok")
    time.sleep(1)
    print("The received ID is: ")
    print(message.payload)

    conn = sqlite3.connect('EMP_DB')
    cur = conn.cursor() 
    cur.execute('''SELECT emp_name FROM employee WHERE emp_id = 1004''')
    print("the name is: ")
    res = cur.fetchone()
    print(res)
    conn.commit()
    conn.close()
    if message.retain==1:
        print("This is a retained message \n")
try:

    clt = mqtt.Client()
    print(" Client created successfully ! \n")

    clt.on_connect = on_connect
    clt.on_message = on_message

    clt.connect(brocker,port)
    clt.loop_start()
    clt.subscribe(topic)
    time.sleep(4)
    print("subscribtion: successful \n")
    clt.loop_stop()

except Exception as inst: 
    print("\n Exception found \n")
    print(type(inst))
    print(inst.args)
    print(inst)
    print("\n")

This is The Python Code of the Publisher

import paho.mqtt.client as mqtt
import time 

port = 1883
brocker = "localhost"
message = 1004 
topic = "Auth"

def on_publish(client,userdata,mid):
    print("on_publish callback mid: " str(mid))
    print("The message published is: "  str(message))
    
def on_connect(client, userdata, flags, rc):
    print("The default result code for this connection is: " str(rc)) 

def on_disconnect(client,userdata,rc):
    print("client disconnected \n")

def main():
    try:
        clt = mqtt.Client("client1")
        print("Client CREATED successfully  \n")

        clt.on_connect = on_connect
        clt.on_publish = on_publish

        clt.connect(brocker,port)
        print("Client connected \n")

        ret = clt.publish(topic,message)
        time.sleep(4)
        print("The publish result is : " str(ret) "\n")
        
        clt.on_disconnect = on_disconnect
        clt.disconnect()

    except Exception as inst: 
        print("\n Exception found \n")
        print(type(inst))
        print(inst.args)
        print(inst)
        print("\n")

main()

CodePudding user response：

The incoming message payload will always be a byte array (as indicated by the leading b' when printed), if you want to convert it to a string you should use:

message.payload.decode("utf-8")

As for printing out the result, you have a Row object, you will need to use

res['emp_name']

to extract the emp_name column from the row.