Issue with getting Python to correctly compare username input to username from MySQL DB

Question

I'm using PyCharm to run Python (with tkinter and mysql.connector) and I am trying to run the following code:

from tkinter import *
import tkinter.messagebox as MessageBox
import mysql.connector as mysql

root = Tk() 
top = Toplevel()
top.geometry("900x600")
top.title("LIMS")
global mycursor


db = mysql.connect(
    host="localhost",
    user="root",
    password="mypass",
    database="db name"
)

global user

def loginprogram():
    user = e_username.get()
    pw = e_password.get()
    mycursor = db.cursor()
    myuser_queue=[]
    user_query = "SELECT username FROM user WHERE username =%s" % (username)

    try:
        mycursor.execute("SELECT username FROM user")
        users = mycursor.fetchall()
        for x in users:
            print(x)
            if (user == x):
                print("Correct!")
            myuser_queue.append(x)
            print(myuser_queue)
    except:
        print('Error occurred')

    if (user) in myuser_queue:
            print("True " + user)
    else:
        print("Still wrong")
    mycursor.execute("SELECT password FROM user")
    passwords = mycursor.fetchall()
    for x in passwords:
        print(x)

def exitprogram():
    top.destroy()
    root.destroy()
    sys.exit()

title = Label(top, text='Enter Log In Credentials', font={'Helvetica', 10})
title.place(x=20,y=30)

username = Label(top,text='Enter Username',font=('bold',10))
username.place(x=20,y=60)

e_username = Entry(top)
e_username.place(x=150, y=60)

password = Label(top,text='Enter Password',font=('bold',10))
password.place(x=20,y=90)

e_password = Entry(top)
e_password.place(x=150, y=90)

login_button = Button(top, text="Login",  font=("bold", 10), bg="white",command=lambda: loginprogram())
login_button.place(x=150, y=120)

exitbutton = Button(top, text="Exit",  font=("bold", 10), bg="white",command=lambda: exitprogram())
exitbutton.place(x=150, y=150)

root.withdraw()
root.mainloop()

I have it set up to print and have it correctly pulling in the username from the database. It is also correctly placing each username into the list. The issue appears to be in the section: I have also tried several iterations of the query, but the one in the actual mycursor appears to be the only one correctly pulling the information from the MySQL database.

if (user) in myuser_queue:
    print("True " + user)

score 1 · Answer 1 · answered Apr 17 '21 at 07:30

cursor.fetchall() returns list of tuples

cur.execute("""SELECT username FROM users""")
rows = cur.fetchall()
for row in rows:
    print(row)

Output:

('Alice',)
('Bob',)
('Carol',)

But the code in the question assumes that the return value is a list of strings.

You want to extract the first element in each of the tuples:

        mycursor.execute("SELECT username FROM user")
        users = mycursor.fetchall()
        for row in users:
            print(row[0])
            if (user == row[0]):
                print("Correct!")
            myuser_queue.append(row[0])

or alternatively

        mycursor.execute("SELECT username FROM user")
        users = mycursor.fetchall()
        # Unpack x from its containing tuple
        for (x,) in users:
            print(x)
            if (user == x):
                print("Correct!")
            myuser_queue.append(x)

Issue with getting Python to correctly compare username input to username from MySQL DB

1 Answers1