Python cannot insert a a single value to mysqldatabase table: value needs to be a tuple/list/dict

Question

I have a database table were a certain GUID (which is string of en envoriment variable) will be sent through and be inserted into table. This table does contain multiple columns but considering these can be updated later based on this unqiue ID. But I just cannot insert into the table. it says it has the value has to be a list/tuple/dict. How do I solve this?

deviceUID = str(socket.gethostname())

def deviceUID_check():
    try:
        sql_select_query = "Select * From kiosk_status where deviceUID = %s"
        selectuidcursor = ourdb.cursor()
        selectuidcursor.execute(sql_select_query, (deviceUID))
        record = selectuidcursor.fetchall()
        print("Found UID")
    except mysql.connector.Error as error:
        UIDinsertcursor = ourdb.cursor()
        print("Failed to get record from MySQL table: {}".format(error))
        sql = "INSERT INTO kiosk_status (deviceUID) VALUES %s"
        val = (deviceUID)
        UIDinsertcursor.execute(sql, val)
        ourdb.commit()
        print("Added a new deviceUid")

Traceback:

  Message=1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''DESKTOP-6H43O5H'' at line 1
  Source=D:CLASSIFIED/Raspberry Pi Test\RaspBerry API\RaspBerry_API.py
  StackTrace:
  File "D:CLASSIFIED/Raspberry Pi Test\RaspBerry API\RaspBerry_API.py", line 52, in KIOSKUID_check
    UIDinsertcursor.execute(sql, val)
  File "D:CLASSIFIED/Raspberry Pi Test\RaspBerry API\RaspBerry_API.py", line 141, in <module> (Current frame)
    deviceUID_check()

Console log:

pygame 2.1.2 (SDL 2.0.18, Python 3.7.8)
Hello from the pygame community. https://www.pygame.org/contribute.html
DESKTOP-6H43O5H

Failed to get record from MySQL table: Could not process parameters: str(DESKTOP-6H43O5H), it must be of type list, tuple or dict

deviceUID = str(socket.gethostname())

New Code:

def deviceUID_check():
    try:
        sql_select_query = "Select * From kiosk_status where deviceUID = %s"
        selectuidcursor = ourdb.cursor()
        selectuidcursor.execute(sql_select_query, (deviceUID))
        record = selectuidcursor.fetchall()
        print("Found UID")
    except mysql.connector.Error as error:
        UIDinsertcursor = ourdb.cursor()
        print("Failed to get record from MySQL table: {}".format(error))
        sql = "INSERT INTO kiosk_status (deviceUID) VALUES %s"
        val = (deviceUID,)
        UIDinsertcursor.execute(sql, val)
        ourdb.commit()
        print("Added a new deviceUid")

This isn't about _inserting_ a tuple, but about passing arguments to `cursor.execute()`. Tuples are defined by _commas_, not parentheses. `(deviceUID)` is not a tuple, but `(devinceUID,)` is. — Chris, Feb 06 '22 at 18:29
Please edit the exact error message with traceback into your question. — Chris, Feb 06 '22 at 18:30
Update this line too: `selectuidcursor.execute(sql_select_query, (deviceUID))`. That `(deviceUID)` needs to be `(deviceUID,)`. — Chris, Feb 06 '22 at 18:41
Thanks for this but I have different problem now, it seems the entire string does not get inserted into the table. I am pretty sure the column does not have a character limit. — ThunderSpark, Feb 06 '22 at 18:52
That's a different question. If you post a new question about it, please provide an example. — Chris, Feb 06 '22 at 19:02

Python cannot insert a a single value to mysqldatabase table: value needs to be a tuple/list/dict

0 Answers0