python - 如何使用python向mysql数据库插入变量？ "You have an error in your SQL syntax"

Question

我正在尝试使用 python 将一个变量插入 mysql 数据库。但我有语法错误..你能帮我吗？ :)

import mysql.connector
    from mysql.connector import Error
    from mysql.connector import errorcode

    try:
        connection = mysql.connector.connect(host='localhost',
                                         database='Xiaomi_Temp',
                                         user='user',
                                         password='pass')
        cursor = connection.cursor()
        mySql_insert_query = """ INSERT INTO Temp_test (batt) VALUES (%d) """
        recordTuple = (batt)
        print(batt)
        cursor.execute(mySql_insert_query, recordTuple)


        connection.commit()
        print("Record inserted successfully into table")
        cursor.close()

    except mysql.connector.Error as error:
        print("Failed to insert record into table {}".format(error))

    finally:
        if (connection.is_connected()):
            connection.close()
            print("MySQL connection is closed")

我有变量batt，它有整数值。我的输出是:

Failed to insert record into table 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 '%d)' at line 1
MySQL connection is closed

Answer 1

由于在执行查询时整个查询需要采用字符串格式，因此应使用 %s 而不是 %d，可以按如下方式完成，

         mySql_insert_query = """ INSERT INTO Temp_test (batt) VALUES (%s) """
         recordTuple = (batt)
         print(batt)
         cursor.execute(mySql_insert_query, recordTuple)