如果mySQL数据库中已经存在用户名和密码，我如何在python中进行验证? [英] How can i validate in python if a username and password already exist in mySQL Database?

问题描述

所以我现在已经在这个项目上工作了大约一天(python和mySQL的新手) 所以我的问题是我如何查看文本框中的输入用户凭据是否已经是注册用户?

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

更新我将username_verify.get()值放入变量中仍然无法正常工作，并且仍会发布相同的错误.

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

推荐答案

我将为您提供一个适用于mysql.connector所有查询的代码

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)

这篇关于如果mySQL数据库中已经存在用户名和密码，我如何在python中进行验证?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！