Using Python to add MQTT values to sqlite3 data-CodePudding

I am working on a script that is built to take values received from an MQTT broker and insert them into an sqlite3 database. The code is below.

import paho.mqtt.client as mqtt
import sqlite3 as sql

test_name = "test1"
test_type = "big_bertha"

def generate_sql(test, function, data=[]):
    """Generates strings of SQL to be executed by the cursor object
       test: the type of test the script is recording as a string
       function: either the string "create" or "insert" depending on what sql needs to be generated
       return: A string of sql to be executed
    """
    if test == "big_bertha":
        if function == "create":
            create_statement = "CREATE TABLE "   test_name   " (ID INTEGER PRIMARY KEY AUTOINCREMENT, " \
                                "time DATETIME DEFAULT(STRFTIME('%Y-%m-%d %H:%M%f', 'NOW', 'localtime')), " \
                                "jog_setpoint REAL, position REAL, force_setpoint INTEGER, force INTEGER, " \
                                "pressure_t INTEGER, pressure_c INTEGER)"
            return create_statement
        else:
            insert_statement = f"INSERT INTO {test_name} (jog_setpoint,position,force_setpoint,force,pressure_t,pressure_c) VALUES({data[0]},{data[1]},{data[2]},{data[3]},{data[4]},{data[5]})"
            return insert_statement

#MQTT callbacks
def on_message(client, userdata, msg):
    if msg.topic == "end":
        cur.close()
        con.close()
        end_msg = "Test Finished at: "   str(dt.now().strftime("%d/%m/%Y-%H:%M:%S"))
        client.publish("Important","Test Finished at: "   end_msg)
        sys.exit()
    else:
        payload_data = str(msg.payload,"utf-8")
        payload_data_list = payload_data.strip("\r").split(",")
        sqlite3_command = generate_sql(test_type, "insert",payload_data_list)
        print(con.total_changes)
        cur.execute(sqlite3_command)
        
def on_connect(client,userdata,flags,rc):
    client.subscribe("end")
    client.subscribe("bigBertha")
    client.publish("Important", "Connected and Recording")

#create sql connection and cursor object
con = sql.connect("testlab.db")
cur = con.cursor()

#create table for this test
cur.execute(generate_sql(test_type, "create"))
print(generate_sql(test_type, "insert",[23.02,23.00,-774.00,-164.00,0.00,0.00]))
cur.execute(generate_sql(test_type, "insert",[23.02,23.00,-774.00,-164.00,0.00,0.00]))

#connect to mqtt 
client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message

#run program
client.connect("192.168.50.10",1883)
client.loop_forever()

I have tried running the generated sql in the terminal and it works but when I run the script the MQTT will connect and the table will get created but none of the insert statements work. Does anyone know how to fix this?

The payload data list will look like: [23.23,23.5,12200,6500,0.0,0.0]

CodePudding user response：

"The INSERT statement implicitly opens a transaction, which needs to be committed before changes are saved in the database (see Transaction control for details). Call con.commit() on the connection object to commit the transaction:"

con.commit()

https://docs.python.org/3/library/sqlite3.html