准备好的语句和错误的参数数量

Question

我使用一个SQL查询来查找具有某个RFID值的所有用户，每个用户可以有多个RFID值

我已经创建了一个prepared语句，这并不是必需的，因为插入的值应该只是数字，但是，我不断收到错误消息说Incorrect number of arguments executing prepared statement，即使我只输入了一个存储在元组中的值

变量RFID是函数中的一个参数

conn = Database.connect()  # returns an instance of connector.connect 

cursor = conn.cursor(prepared=True)  # Creates a cursor that is expecting 

sql_query = """SELECT RFID, users.UserID, FirstName, Surname, Email, Dob, Gender, Mobile 
               FROM users 
               JOIN RFIDs ON (users.UserID = RFIDs.UserID) 
               WHERE users.UserID = (SELECT UserID FROM RFIDs WHERE RFID = '%s');"""

cursor.execute(sql_query, (rfid,))  # Get user info


user_info_array = cursor.fetch()  # Store the results


conn.commit()  # Save the changes to the database

当我在中手动输入值时，查询工作正常

提前谢谢

Answer 1

您不应该在%s周围使用单引号

sql_query = """SELECT RFID, users.UserID, FirstName, Surname, Email, Dob, Gender, Mobile 
           FROM users 
           JOIN RFIDs ON (users.UserID = RFIDs.UserID) 
           WHERE users.UserID = (SELECT UserID FROM RFIDs WHERE RFID = %s);"""

正如spencer7593所建议的，子查询应该使用IN子句，而不是=（equal运算符），这样可以避免子查询返回的行数超过一行时出错

 sql_query = """SELECT RFID, users.UserID, FirstName, Surname, Email, Dob, Gender, Mobile 
       FROM users 
       JOIN RFIDs ON (users.UserID = RFIDs.UserID) 
       WHERE users.UserID IN (SELECT UserID FROM RFIDs WHERE RFID = %s);"""

或者内部连接

sql_query = """SELECT RFID, users.UserID, FirstName, Surname, Email, Dob, Gender, Mobile 
   FROM users 
   JOIN RFIDs ON (users.UserID = RFIDs.UserID) 
   inner join  (
     SELECT UserID FROM RFIDs WHERE RFID = %s
   ) t on T.UserID  = users.UserID ;"""