String variable not being passed in SELECT WHERE query of SQL in Python

Question

I am trying to pass a string variable to SELECT WHERE statement of SQL query. And it is not returning anything while if I pass a hard-coded number then the query is working fine. Also, when I am printing the string variable it is returning the value like 86 but the SQL query is not taking this value when the string variable is passed.

This is the code with which I tried. I have not included database connection details. I have checked that I am correctly using database connection details.

def on_message(client, userdata, message):
    print("message received " ,str(message.payload.decode("utf-8")))
    print("message topic=",message.topic)
    s2 = "controlfan/"
    topic = message.topic
    fannumber = topic[topic.index(s2) + len(s2):]
    print(fannumber)   # it prints 86 
    mydb = mysql.connector.connect(
      host="",
      user="",
      password="",
      database=""
)
    mycursor = mydb.cursor()
    sql = ("SELECT macaddress FROM deviceids WHERE serialnumber  = fannumber ")  # works fine if I 
                                                                                   put serialnumber = 
                                                                                   86
    mycursor.execute(sql)
    myresult = mycursor.fetchone()
    for x in myresult:
     print(x)

I have tried this also:

    sql = ("SELECT macaddress FROM deviceids WHERE serialnumber  = %s ")
    mycursor.execute(sql, fannumber)

I did not work also. Please guide me on how this string variable could be passed successfully.

Does this answer your question? [How to use variables in SQL statement in Python?](https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python) — Ture Pålsson, Jun 30 '20 at 08:17

score 2 · Accepted Answer · answered Jun 30 '20 at 07:47

2

sql = ("SELECT macaddress FROM deviceids WHERE serialnumber=?", (fannumber,))

Notice that the second argument (fannumber,) is a tuple

answered Jun 30 '20 at 07:47

Ahmed

74
4

You have the right idea here, but your line won't work is pasted as-is into the OP:s example; you'd have to change the next line to `mycursor.execute(*sql)`. – Ture Pålsson Jun 30 '20 at 08:15
so I chould add `mycursor.execute(*sql)` to the answer? – Ahmed Jun 30 '20 at 08:26

String variable not being passed in SELECT WHERE query of SQL in Python

1 Answers1