Failed to update table record: 1064 (42000): You have an error in your SQL syntax; Python-CodePudding

I want to send my variable to MySQL database. But I have a warning. This code was successful to upload.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='mydb',
                                         user='root',
                                         password='')
    cursor = connection.cursor()

    username = 1111111 #example

    isian = "tryfile.pkl" #example, i want send string data

    print("Before updating a record ")
    sql_select_query = """select * from signature where id = %s"""
    cursor.execute(sql_select_query %(username))
    record = cursor.fetchone()
    print(record)

    # Update single record now
    sql_update_query = """Update signature set signature = "%s" where id = %s"""
    cursor.execute(sql_update_query %(isian,username))
    connection.commit()
    print("Record Updated successfully ")

    print("After updating record ")
    cursor.execute(sql_select_query)
    record = cursor.fetchone()
    print(record)
    
except mysql.connector.Error as error:
    print("Failed to update table record: {}".format(error))
finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

Warning

Failed to update table record: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%s' at line 1

I don't understand with error, because I am using MySQL not MariaDB

What's happen to my code?

CodePudding user response：

MariaDB is the engine used in modern MySQL databases.

The problem is your second call to sql_select_query, because you forgot to add the % (username), so nothing was substituted. HOWEVER, you should not be doing the substitutions yourself. You need to let the database connector do it. It's an easy change:

    print("Before updating a record ")
    sql_select_query = """select * from signature where id = ?"""
    cursor.execute(sql_select_query, (username,))
    record = cursor.fetchone()
    print(record)

    # Update single record now
    sql_update_query = """Update signature set signature = ? where id = ?"""
    cursor.execute(sql_update_query, (isian,username))
    connection.commit()
    print("Record Updated successfully ")

    print("After updating record ")
    cursor.execute(sql_select_query, (username,))
    record = cursor.fetchone()
    print(record)