How do I insert values into a sql table using python3?

Question

I'm trying to setup a MQTT client using the Paho library. With that there are no problems, but I'm trying to insert the received publishes in a sql database. I'm converting the received payload string into a dictionary and add a few entries. When executing the following code:

def insert_values(db_connection=None, topic=None, payload=None, time_received=None):
    query_1 = "SELECT topic_id FROM topics WHERE topic = %s"
    query_2 = """INSERT INTO measurements (start_time, end_time, value, max_value, min_value, time_received, topic_id)
                 VALUES (%(start_time)s, %(end_time)s, %(value)s, %(max_value)s, %(min_value)s, %(time_received)s, 
                        %(topic_id)s)"""
    cursor = db_connection.cursor(prepared=True)
    topic_id = cursor.execute(query_1, topic)
    payload["time_received"] = time_received
    payload["topic_id"] = topic_id
    cursor.executemany(query_2, payload)
    db_connection.commit()
    db_disconnect(db_connection, cursor)

I get the following error:

Caught exception in on_message: 1210: Incorrect number of arguments executing prepared statement

The payload looks like this:

payload = {
        "Starttime:": 2020-02-18 10:11:22.2145563,
        "Endtime:": 2020-02-18 10:12:22.2145563,
        "Average Humidity:": 44.256241,
        "Number of Values:": 22,
        "Max Humidity:": 44.586214,
        "Min Humidity:": 44.012148521)
}

Plus some additional info, like the time the payload was received. In the insert_values method I'm trying to get the topic_id from the table topics and write it into the payload.

Edit: The table in which the measurements are to be written looks like this:

CREATE TABLE IF NOT EXISTS measurements
(measurement_id INT AUTO_INCREMENT,
 start_time DATETIME,
 end_time DATETIME,
 value FLOAT,
 max_value FLOAT,
 min_value FLOAT,
 time_received DATETIME,
 topic_id INT,
 PRIMARY KEY (measurement_id),
 FOREIGN KEY (topic_id) REFERENCES topics(topic_id))

xplo4d · Answer 1 · 2020-02-18T12:25:26.293

1

Your payload has 6 keys, and then you add another 2 that makes it 8 In your query you have only 7 arguments I'm not sure but i think you forgot to add "Average Humidity" EDIT : After seeing your table DESC

    query_2 = """INSERT INTO measurements (start_time, end_time, value, max_value, min_value, time_received, topic_id)
             VALUES (%s,%s,%s,%s,%s,%s,%s)"""
cursor = db_connection.cursor(prepared=True)
topic_id = cursor.execute(query_1, topic)
payload["time_received"] = time_received
payload["topic_id"] = topic_id
payload.pop("Average Humidity:", None)
cursor.executemany(query_2, payload)
db_connection.commit()
db_disconnect(db_connection, cursor)

edited Feb 18 '20 at 12:25

answered Feb 18 '20 at 10:31

xplo4d

264
1
6

Yes, I´ve left out the mesurement_id as that is the auto_increment primary key of the table mesurements. I thought it would automaticalliy add a new id for each entry – Nordmann1861 Feb 18 '20 at 10:36
can you poste a description of your mesurement table? – xplo4d Feb 18 '20 at 10:39
I tried your corrected query, but I get the same error – Nordmann1861 Feb 18 '20 at 11:33
Thanks, but if I pop the Average Humidity, I would loose one of the key values I want to write in the database. It is a small IoT Project I´m doing and I need alll the values of the payload written in the database. The column is called value because at a later time I may need to wirte values other than a humidity into the table. – Nordmann1861 Feb 18 '20 at 12:41
your measurements table has 7 fields, your payload after adding time_received,topic_id has 8 fields, that's why you get the error. If you want to add all the keys of payload then you must add another field to your table, otherwise you must remove one key from the payload – xplo4d Feb 18 '20 at 14:07
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/208055/discussion-between-xplo4d-and-nordmann1861). – xplo4d Feb 18 '20 at 14:09

score 1 · Answer 2 · answered Feb 18 '20 at 12:37

Here:

"""VALUES (%(start_time)s, %(end_time)s, %(value)s, %(max_value)s, %(min_value)s, %(time_received)s, %(topic_id)s)"""

and here:

payload = {
    "Starttime:": 2020-02-18 10:11:22.2145563,
    "Endtime:": 2020-02-18 10:12:22.2145563,
    "Average Humidity:": 44.256241,
    "Number of Values:": 22,
    "Max Humidity:": 44.586214,
    "Min Humidity:": 44.012148521)
}

Your payload keys must match the query's placeholders names - or your placeholders match the payload keys. Your db client will definitly not try and guess that "start_time" and "Starttime" are actually supposed to be the same thing. And you of course must have mathcing keys for all the query's placeholders.

I corrected the payload using the keys I used in the table. Now it gets caught in an execption. ```Caught exception in on_message: 'start_time'``` Do you know this error and can you enlighten me what went wrong? It still doesn´t write the values of the payload into the table — Nordmann1861, Feb 18 '20 at 13:30

How do I insert values into a sql table using python3?

2 Answers2