我正在使用SQL查询来查找具有特定RFID值的所有用户,每个用户可以有多个RFID值。
我已经创建了一个准备好的语句,这不是真正必需的,因为插入的值应该只是数字,但是,即使我只输入一个存储在元组中的值,我也不断收到错误消息说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
查询工作正常,因为我使用了它并且手动输入值。
提前致谢
不应在 %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 子句而不是 =(相等运算符)以避免子查询返回更多行时出错
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 ;"""