ValueError: Could not process parameters in Python/MySQL

Question

I want to prevent duplicate usernames when people register.

Here is my code snippet:

def submit(self):
    username_info = username.get()
    username_password = password.get()
  
    #connect to db
    db = mysql.connector.connect(host = 'localhost', user = 'root', password = '', database = 'user')

    #create a cursor
    mycursor = db.cursor()
    #insert to db
    sql = ("INSERT INTO useraccess (user_type, password) VALUES (%s, %s)")

    query = (username_info, username_password)
    mycursor.execute(sql, query)
    #commit
    db.commit()

    #create a messagebox
    messagebox.showinfo("Registration", "Successfully Register")

    #if username has been used
    find_user = ("SELECT * FROM useraccess WHERE user_type = ?")
    user_query = (username_info)

    mycursor.execute(find_user, user_query)
    #if (username == username_info):
    if mycursor.fetchall():
        messagebox.showerror("Registration", "The username chosen is already used. Please select another username")
    else:
        messagebox.showinfo("Registration", "Account Created!")

But every time I run it, although the username has been registered in the db, it only shows the successfully created messagebox and error:

ValueError: Could not process parameters.

Anyone can help me to solve this problem?

You need to make sure your query variables are tuples.

Axelle
– Axelle

2019-03-15 08:38:30 +00:00
Commented Mar 15, 2019 at 8:38 — Axelle
– Axelle, Commented Mar 15, 2019 at 8:38

jcbsv · Accepted Answer · 2019-03-15 09:21:17Z

20

I believe the source of the problem is in the line

user_query = (username_info)

It should be

user_query = (username_info,)

The trailing comma is the syntactic difference between an expression in parentheses and a tuple.

Another issue with code is the query:

find_user = ("SELECT * FROM useraccess WHERE user_type = ?")

Which should be:

find_user = ("SELECT * FROM useraccess WHERE user_type = %s")

edited Mar 15, 2019 at 9:21

answered Mar 15, 2019 at 8:37

jcbsv

4653 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Eve11 Over a year ago

when i change it to user_query = (username_info,) , it shows another error which is mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

shmee Over a year ago

@Eve11 The ? in your second query string is not a valid placeholder for parameters in mysql.connector. You need to change that to %s.

jcbsv Over a year ago

Updated answer with fix for the second issue

phrogg Over a year ago

Thanks, coming here from a whole different case, but with the same root problem. It did not detect the variable as a tuple.

Zr.Ms. Bruinvis · Accepted Answer · 2019-03-15 09:24:11Z

0

Have you checked these variables,

username_info = username.get()
username_password = password.get()

are they in proccesable formats? (i.e. can you directly put the username.get() into user_type ?)

I'm not familiar with this way of passing a parameter

find_user = ("SELECT * FROM useraccess WHERE user_type = ?")

have you double checked this? (why not the %s method?)

also, you probably get the "Account Created!" because mycursor.fetchall() fails.

edited Mar 15, 2019 at 9:24

answered Mar 15, 2019 at 8:47

Zr.Ms. Bruinvis

647 bronze badges

5 Comments

Eve11 Over a year ago

I change it to #if username has been used find_user = ("SELECT * FROM useraccess WHERE user_type = %s") user_query = (username_info, ) mycursor.execute(find_user, user_query) #if (username == username_info): if mycursor.fetchall(): messagebox.showerror("Registration", "The username chosen is already used. Please select another username") else: messagebox.showinfo("Registration", "Account Created!")

Eve11 Over a year ago

now when i run that code, there is no error. But it only shows "The username chosen is already used. Please select another username" messagebox, but the username is duplicated in the db

Zr.Ms. Bruinvis Over a year ago

Its because you insert the new user before you check if theres a duplicate;

Zr.Ms. Bruinvis Over a year ago

nb. mycursor.execute(sql, query) already adds the duplicate before you check if theres going to be a duplicate (advies: run the insert after checking if theres a duplicate)

shmee Over a year ago

@Eve11 Or, use a unique constraint on the column holding the user name. You'll have to handle IntegrityErrors on the INSERT or use INSERT IGNORE. See this

Collectives™ on Stack Overflow

ValueError: Could not process parameters in Python/MySQL

2 Answers 2

4 Comments

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Linked

Related