CODEWITHSUNDEEP
pythonmysql
Ollie Pugh
Ollie Pugh

Reputation: 446

Prepared Statements and incorrect number of arguments

I am using an SQL query to find all the user with a certain RFID value, each user can have multiple RFID values.

I have created a prepared statement, which isn't really required as the value inserted should just be numbers, but, I keep getting the error message saying Incorrect number of arguments executing prepared statement even though I am only entering one value that is stored in a tuple.

The variable RFID is a parameter within the function

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

The query works fine as I have used it and when manually entering the values in.

Thanks in advance

Upvotes: 1

Views: 328

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

You should not use the single quote around %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);"""

and as suggested by spencer7593 for the subquery should be used the IN clause and not the = (equal operator) this for avoid error when the subquery return more then a row 

 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);"""

or an inner join 

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 ;"""

Upvotes: 2

Related Questions