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 Mar 15 '19 at 08:38 — Axelle, Mar 15 '19 at 08:38

jcbsv · Accepted Answer · 2019-03-15T09:21:17.983

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 '19 at 09:21

answered Mar 15 '19 at 08:37

jcbsv

466
3
13

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 – Eve11 Mar 15 '19 at 09:09
1

@Eve11 The `?` in your second query string is not a valid placeholder for parameters in `mysql.connector`. You need to change that to `%s`. – shmee Mar 15 '19 at 09:18
Updated answer with fix for the second issue – jcbsv Mar 15 '19 at 09:24
Thanks, coming here from a whole different case, but with the same root problem. It did not detect the variable as a tuple. – phrogg Jul 22 '22 at 10:13

Zr.Ms. Bruinvis · Answer 2 · 2019-03-15T09:24:11.543

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 '19 at 09:24

answered Mar 15 '19 at 08:47

Zr.Ms. Bruinvis

54
7

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 Mar 15 '19 at 09:15
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 – Eve11 Mar 15 '19 at 09:17
1

Its because you insert the new user before you check if theres a duplicate; – Zr.Ms. Bruinvis Mar 15 '19 at 09:22
1

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) – Zr.Ms. Bruinvis Mar 15 '19 at 09:23
@Eve11 Or, use a [unique constraint](http://www.mysqltutorial.org/mysql-unique/) on the column holding the user name. You'll have to handle `IntegrityError`s on the INSERT or use INSERT IGNORE. See [this](https://stackoverflow.com/questions/27787472/how-to-avoid-duplicate-entries-in-a-mysql-database-without-throwing-an-error) – shmee Mar 15 '19 at 09:27

ValueError: Could not process parameters in Python/MySQL

2 Answers2

Related