Putting values into SQL Server Database Table using PYMSSQL

Question

I am trying to put values into the Table of a Database on SQL Server.

My program will subscribe to an MQTT Server and whenever it receives a message, it will put the message into the table of the database.

The following is my code:

import paho.mqtt.client as mqtt
import signal
import sys
import pymssql
from os import getenv
from time import gmtime, strftime

#Signal Handler
def signal_handler(signal, frame):
    print("\nProgram has been interrupted!")
    sys.exit(0)

#MQTT Subscribe ON_CONNECT
def on_connect(client, userdata, flags, rc):
    if str(rc) == '0':
        print ("Connected Successfully")
    else:
        print ("Connection has a problem")

    #CLIENT SUBSCRIPTION
    client.subscribe("topic1")

#MQTT Subscribe ON_MESSAGE
def on_message(client, userdata, msg):
    print("[" + msg.topic + "] " + str(msg.payload) )
    deviceID = msg.payload
    time = strftime("%Y%m%d%H%M%S", gmtime())
    #Puts the data into the SQL Server DB Table "Topic"
    cursor.execute("""
    IF OBJECT_ID('Topic', 'U') IS NOT NULL
        DROP TABLE Topic
    CREATE TABLE Topic(
        id INT NOT NULL,
        deviceID INT NOT NULL,
        dateTime INT NOT NULL,
        PRIMARY KEY(id)
    )
    """)
    cursor.execute(
        "INSERT INTO Topic VALUES (%d)",
        [(id, deviceID, time)]

    conn.commit()

#Signal Handler
signal.signal(signal.SIGINT, signal_handler)

#Connect to the SQL Server
server = 'mqtt.server.address.com'
user = 'sa'
password = 'pwd'
database = 'topics'

#SQL Server Connection Established
conn = pymssql.connect(server, user, password, database)
cursor = conn.cursor()

#Establishing MQTT Subscribe Connection
client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message
client.connect("mqtt.server.address.com", 1883, 60)
client.loop_forever()

And I have been getting the following error:

Thanks for your help in advance.

Answer 1

1. You should post your error as text directly in your question.

2. The error clearly suggests that the query_params argument should be a
   tuple or a dictionary and not a list.

   cursor.execute("INSERT INTO Topic VALUES (%d)",
                  [(id, deviceID, time)])
   

   You are trying to insert a list with one tuple into a single column.

   Also note that you are missing a closing ) in this line.

   Instead you should insert to each column individually, and use a tuple for your arguments:

   cursor.execute("INSERT INTO Topic VALUES (%d, %d, %d)",
                  (id, deviceID, time))