[FIXED] Getting "mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement" when inserting list values into %s of MySQL Query

Issue

I've been having a problem with this code. I'm trying to make a Login Page with 2 input boxes, a Mobile box and a Password box (Mobile is the primary key in the MySQL table for the same).

import tkinter as tk 
import re

import mysql.connector as ms 
mycon = ms.connect (host = 'localhost', user='root', password='Welcome@123', database='cs_project')
cursor = mycon.cursor()

root = tk.Tk()
root.geometry('2560x1600')
root.title("Hotel")
label = tk.Label(root, text='Welcome!', font=('Baskerville', 60), fg='Purple')
label.pack(padx=20, pady=20)

def Login():
    
    global LI_Mobile
    global LI_Password
    
    def validate_numbers(action, value_if_allowed):
        pattern = r'[0-9]*$'
        return re.match(pattern, value_if_allowed) is not None

    validate_numbers_cmd = root.register(validate_numbers)

    LI_Mobile_Label = tk.Label(root, text='Mobile:', font=('Baskerville', 25))
    LI_Mobile_Label.pack(padx=5, pady=5)
    LI_Mobile = tk.Entry(root, font=('Big Caslon', 20), validate='key', validatecommand=(validate_numbers_cmd, '%d', '%P'))
    LI_Mobile.pack(padx=5, pady=5)

    LI_Password_Label = tk.Label(root, text='Password:', font=('Baskerville', 25))
    LI_Password_Label.pack(padx=5, pady=5)
    LI_Password = tk.Entry(root, font=('Big Caslon', 20))
    LI_Password.pack(padx=5, pady=5)

    AccountDoesntExist_Label = tk.Label(root, text='You do not have an account, please create one by signing up.', font=('Baskerville', 25))
    AccountExists_Label = tk.Label(root, text='Welcome, please click the button below to be redirected to the Main Menu.', font=('Baskerville', 25))
    WrongPassword_Label = tk.Label(root, text='Incorrect Password, please try again.', font=('Baskerville', 25))
    
    def Submit2():
        mobile2=LI_Mobile.get()
        password2=LI_Password.get()
        mobile3 = list(mobile2)
        password3 = list(password2)
        
        MobileExist = cursor.execute('SELECT * FROM Accounts WHERE mobile="%s")',(mobile3));
        PasswordCorrect = cursor.execute('SELECT * FROM Accounts WHERE password="%s")',(password3));
        
        if MobileExist is None and PasswordCorrect is None:
            AccountDoesntExist_Label.pack(padx=5,pady=10)
       
        elif MobileExist is None and PasswordCorrect is not None:
            AccountDoesntExist_Label.pack(padx=5, pady=10)
       
        elif PasswordCorrect is None and MobileExist is not None:
            WrongPassword_Label.pack(padx=5, pady=10)
            
        else:
            AccountExists_Label.pack(padx=5, pady=5)
            MenuButton.pack(padx=5,pady=10)
            
            
    Submit2_Button = tk.Button(root, text='Submit', font=('Baskerville', 25), command=Submit2)
    Submit2_Button.pack (padx=5, pady=5)
    
LoginButton = tk.Button(root, text= "Log in", font=('Baskerville', 30), command=Login)
LoginButton.pack(padx=10, pady=10)

MenuButton = tk.Button(root, text= "Menu", font=('Baskerville', 30))

root.mainloop()

At first, mobile2 was an integer value and password2 had string, but I then received this error:

"mysql.connector.errors.ProgrammingError: Could not process parameters: int(1234567890), it must be of type list, tuple or dict"

(I put the value 1234567890 in the input box of Mobile)

So I changed my values forcibly into a list and tried the program again, but I received a new error:-

mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

My main target with this part of the program is to take in user input, check if the values given are matching with the values in the MySQL table "Accounts", and then lead them to a Menu Page afterwards via a Menu Button (whose command I'll define later).

I believe the main thing that needs changing are these lines:

MobileExist = cursor.execute('SELECT * FROM Accounts WHERE mobile="%s")',(mobile3));
PasswordCorrect = cursor.execute('SELECT * FROM Accounts WHERE password="%s")',(password3));

Solution

Two problems:

• Do not put parameter placeholders within quotes in the SQL expression.
• A tuple of a single value needs a trailing comma, or else it's just a scalar value with parentheses around it.

Write the code like this:

MobileExist = cursor.execute('SELECT * FROM Accounts WHERE mobile=%s)',(mobile3,));
PasswordCorrect = cursor.execute('SELECT * FROM Accounts WHERE password=%s)',(password3,));

Alternatively, you could pass a list instead of a tuple. I like this style, because I don't have to remember to put the trailing comma when the tuple is only one member.

MobileExist = cursor.execute('SELECT * FROM Accounts WHERE mobile=%s)',[mobile3]);
PasswordCorrect = cursor.execute('SELECT * FROM Accounts WHERE password=%s)',[password3]);

Answered By - Bill Karwin