How can i validate in python if a username and password already exist in mySQL Database?

Question

Hi so I've been working on this project about a day now(New to python and mySQL) So my question is how i can see if the input user credentials in the textbox is already a registered user?

So far I've managed to connect it to the database and store some values inside the database but i cant seem to figure out how i can scan that database and see if the user info are valid when the login button is pressed.

from tkinter import*
from tkinter import messagebox
import mysql.connector
import time
import datetime
import random

w = 300
h = 2

def register_user():

    global username_info
    global password_info

    if len(username.get()) == 0 and len(password.get()) == 0:
        print("Please fill in the Missing Info")

    if len(username.get()) == 0 and len(password.get()) != 0 :
            print("Please Enter a Username")
    elif len(username.get()) != 0 and len(password.get()) == 0:
                    print("Please enter a Password")


    else:
        username_info = username.get()
        password_info = password.get()

        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="root",
            database="loginsystem"
        )
        mycursor = mydb.cursor()
        sqlFormula = "INSERT INTO users (Username, Password) VALUES (%s, %s)"
        insertvar = (username_info, password_info)
        user1 = ("Joshua", "Cuyugan")
        mycursor.execute(sqlFormula, insertvar)
        mydb.commit()

        username.set("")
        password.set("")

def register():
    global screen1

    screen.withdraw()
    screen1 = Toplevel(screen)
    screen1.title("Registration")
    screen1.geometry("500x250+700+350")

    global username
    global password
    global username_entry
    global password_entry

    username = StringVar()
    password = StringVar()

    Label(screen1, text = " Please Enter Your Details Below", bg = "black", width = w , height = h, font = ("Calibri", 20) , fg = "white").pack()
    Label(screen1, text = "").pack()
    Label(screen1, text = "Username").place(x=220, y=85)
    username_entry = Entry(screen1, textvariable = username, width="50").place(x=100, y=110)
    Label(screen1, text = "Password").place(x=220, y=135)
    password_entry = Entry(screen1, textvariable = password, width="50").place(x=100, y=160)
    Button(screen1, text= "Register", height="1", width="20", command = register_user).place(x=80, y=200)
    Button(screen1, text="Cancel", height="1", width="20", command= on_closereg).place(x=270, y=200)


    screen1.protocol("WM_DELETE_WINDOW", on_closereg)



def login():
    global screen2

    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="root",
        database="loginsystem"
    )
    mycursor = mydb.cursor()
    sql_select_Query = "select * from users"
    mycursor.execute(sql_select_Query)
    records = mycursor.fetchall()
    for row in records:
        print("Username" , row[1],)
        print("Password", row[2], "\n" )
        mycursor.close()

    screen.withdraw()
    screen2 = Toplevel(screen)
    screen2.title("HOT or SUPER HOT")
    screen2.geometry("800x600+550+220")


    screen2.protocol("WM_DELETE_WINDOW", on_close)

def checker():
    if len(username.get()) == 0 and len(password.get()) == 0:
        print("Please fill in the Missing Info")


def on_close():
    screen2.withdraw()
    screen.update()
    screen.deiconify()
    screen.lift()

def on_closereg():
    screen1.withdraw()
    screen.update()
    screen.deiconify()
    screen.lift()

def verify():
    global name
    global userlogcred
    global userpascred

    userlogcred = username_verify.get()
    userpascred = password_verify.get()

    loadname = ("SELECT Username FROM users WHERE Username =%s")
    loadpass = ("SELECT Password FFROM users WHERE Password =%s")

    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="root",
        database="loginsystem"
    )
    mycursor = mydb.cursor()

    if len(username_verify.get()) == 0 and len(password_verify.get()) == 0:
        print("Please fill in the Missing Info")
    if len(username_verify.get()) == 0 and len(password_verify.get()) != 0 :
        print("Please Enter a Username")
    elif len(username_verify.get()) != 0 and len(password_verify.get()) == 0:
        print("Please enter a Password")

    else:
        mycursor.execute(loadname, userlogcred)
        mycursor.execute(loadpass, userpascred)
        logincheck = mycursor.fetchone()
        loginpasscheck = mycursor.fetchone()
        if logincheck is None:
            print("Sorry, could not find you in the database\nOr it just isn't working")
        if logincheck is not None and loginpasscheck is None:
            print("Please Enter your Password")
        elif logincheck is None and loginpasscheck is not None:
            print("Please enter Your Username")
        else:
            print("pass\nSuccessfully loaded {} from the database".format(username_verify.get()))



def main_Screen():

    global screen

    screen = Tk()
    screen.geometry("600x300+650+350")
    screen.title("Login System")


    Label(text = "Login System" , bg = "black", width = w , height = h, font = ("Calibri", 20) , fg = "white").pack()
    Label(text = "").pack()
    Button(text = "Login", height = h, width = "30", command = verify).place(x=50 , y=200)
    Label(text = "").pack()
    Button(text = "Register" ,height = h, width = "30", command = register).place(x=320 , y=200)


    global username_verify
    global password_verify

    username_verify = StringVar()
    password_verify = StringVar()
    Label(screen, text = "Username").place(x=265, y = 90)
    username_entry1 = Entry(screen, textvariable = username_verify, width = "80").place(x=57, y=110)
    Label(screen, text="Password").place(x=267, y=140)
    password_entry1 = Entry(screen, textvariable = password_verify, width = "80").place(x=57, y=160)

    screen.mainloop()


main_Screen()

print("Hello World")

Update I Found this code and I'm trying to apply it to my project where in this code compares the input value inside the textbox to the database data and it checks if the datas are already present if they are it then sends you to another form.

def verify():
    global name

    loadname = ("SELECT Username FROM users WHERE Username =%s")
    loadpass = ("SELECT Password FFROM users WHERE Password = %s")

    mydb = mysql.connector.connect(
        host="localhost",
        user="root",
        passwd="root",
        database="loginsystem"
    )
    mycursor = mydb.cursor()

    if len(username_verify.get()) == 0 and len(password_verify.get()) == 0:
        print("Please fill in the Missing Info")

    if len(username_verify.get()) == 0 and len(password_verify.get()) != 0 :
            print("Please Enter a Username")
    elif len(username_verify.get()) != 0 and len(password_verify.get()) == 0:
                    print("Please enter a Password")

    else:
        mycursor.execute(loadname, username_verify.get())
        mycursor.execute(loadpass, password_verify.get())
        logincheck = mycursor.fetchone()
        loginpasscheck = mycursor.fetchone()
        if logincheck is None:
            print("Sorry, could not find you in the database\nOr it just isn't working")
        if logincheck is not None and loginpasscheck is None:
            print("Please Enter your Password")
        elif logincheck is None and loginpasscheck is not None:
            print("Please enter Your Username")
        else:
            print("pass\nSuccessfully loaded {} from the database".format(login))

but I encountered this erro please help.

Traceback (most recent call last):
  File "C:\Users\lenovo\AppData\Local\Programs\Python\Python37-32\lib\tkinter\__init__.py", line 1705, in __call__
    return self.func(*args)
  File "C:/Users/lenovo/PycharmProjects/Pylog/App.py", line 141, in verify
    mycursor.execute(loadname, username_verify.get())
  File "C:\Users\lenovo\PycharmProjects\Pylog\venv\lib\site-packages\mysql\connector\cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\lenovo\PycharmProjects\Pylog\venv\lib\site-packages\mysql\connector\connection.py", line 553, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\lenovo\PycharmProjects\Pylog\venv\lib\site-packages\mysql\connector\connection.py", line 442, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1
Hello World

Update I put the username_verify.get() values into variables still didn't work and it still posts the same error.

Answer 1

import mysql.connector as sql

class connections:
    __HOST = 'localh`o`st'
    __USERNAME = 'root'
    __PASSWORD = ''
    __DATABASE = 'testing'
    
    def __init__(self):
        self.con = sql.connect(host=connections.__HOST,user=connections.__USERNAME,password=connections.__PASSWORD,database=connections.__DATABASE)


    def connect_database(self,username,password):
        #append password and username in the emptey list below for later checkings
        mypassword_queue =[]
        sql_query = "SELECT *FROM users WHERE first_name ='%s' AND password ='%s'" % (username, password)
        mycursor = self.con.cursor()

        try:
            mycursor.execute(sql_query)
            myresults =mycursor.fetchall()
            for row in myresults:
                for x in row:
                    mypassword_queue.append(x)
        except:
            print('error occured')

        if (username and password) in mypassword_queue:
            print('there is something')
        else:
            print('there is no anything')

        self.con.close()


root = connections()
#---you must have created a database with choice of your database name for this case it is testing
#---- the data inside has name as tumusiime and password 1234
root.connect_database('tumusiime','1234')

Answer 2

I will give you a code that works for me for all querys with mysql.connector

**config= {'user': your_user',
'password': 'your_pass',
'host': 'ip_of_your_db',
'database': 'name_of_your_db',
'raise_on_warnings': True}


def run_query(self,query):    
    try:
        conn = mysql.connector.connect(**self._config)
        if conn.is_connected():
            print('run_query: Connecting MySql Db')
        cur = conn.cursor()
        cur.execute(query)
        r= cur.fetchall()
        return r
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("run_query: Error in username or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("run_query: Dabatase doesn't exist")
        else:
            print(err)
    finally:
        conn.commit()
        conn.close()

and each Query need to have this format

query='SELECT * FROM {}'.format("your_db.your_table")
answer=self.run_query(query)