尝试使用Python对使用SQLite 3创建的数据库进行详细验证。

huangapple go评论69阅读模式
英文:

Attempting to do detail validation on a db created in sqlite 3 using python

问题

我正在从在文本文档中保存和检查登录文件的方式转换为使用SQL数据库。在我的当前解决方案中,我已经使其能够检查文件中的用户名和密码,然后分别报告用户名或密码是否正确。以下是我SQL实现的代码部分:

def check_login_on_db():
    user = UserSignUpDetails(None, None, log_in_window_username_entry.get(),
                             log_in_window_password_entry.get())
    enteredUsername = user.username
    enteredPassword = user.password

    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()

    cursor.execute('SELECT * from SignUpDetails where username="%s"' % enteredUsername)
    values = cursor.fetchall()

    for i in values:
        if values[i] == None:
            log_in_additional_info.config(text="This username is incorrect")
            conn.close()
        else:
            if values[i] == enteredUsername:
                if values[i+1] != enteredPassword:
                    log_in_additional_info.config(text="The password is incorrect")
                    conn close()
                else:
                    log_in_additional_info.config(text="")
                    conn close

我会感激任何帮助。

我期望如果我输入我知道是错误的详细信息,应该会得到错误消息,但没有得到。如果我使用这个实现,它会工作并显示错误,但我更希望它告诉我哪一部分是错误的:

cursor.execute('SELECT * from SignUpDetails where username="%s"' % enteredUsername)
if cursor.fetchone():
   log_in_additional_info.config(
                    text="Success")
else:
   log_in_additional_info.config(
                    text="Incorrect username or password")
英文:

I'm transitioning from keeping and checking login files on a text document to from a SQL database. In my current solution, I have got it working where I can check the file for the username and password and then report back if the username or password are correct individually. The code is as follows for my SQL implementation:

def check_login_on_db():
        user = UserSignUpDetails(None, None, log_in_window_username_entry.get(),
                                 log_in_window_password_entry.get())
        enteredUsername = user.username
        enteredPassword = user.password

        conn = sqlite3.connect('database.db')
        cursor = conn.cursor()

        cursor.execute('SELECT * from SignUpDetails where username="%s"'%(enteredUsername))
        values = cursor.fetchall()

        for i in values:
            if values[i] == None:
                log_in_additional_info.config(text="This username is incorrect")
                conn.close()
            else:
                if values[i] == enteredUsername:
                    if values[i+1] != enteredPassword:
                        log_in_additional_info.config(text="The password is incorrect")
                        conn.close()
                    else:
                        log_in_additional_info.config(text="")
                        conn.close

I'd appreciate any help on this.

I expected to get the error messages if I were to input details I know are wrong, but did not.
If I use this implementation, I get it to work and show the error, but I would much rather have it working where it tells me which part is wrong:

cursor.execute('SELECT * from SignUpDetails where username="%s"'%(enteredUsername))
        if cursor.fetchone():
           log_in_additional_info.config(
                            text="Success")
        else:
           log_in_additional_info.config(
                            text="Incorrect username or password")

答案1

得分: 1

我觉得在这里使用变量i来表示一行可能会让事情变得有点混乱。让我们重新命名一些变量以增加一些清晰度。

在我们整理代码的同时,我会做一些额外的更改。首先,SQL通常不喜欢双引号,所以让我们将引号反过来,这样SQL就能看到单引号了。其次,我将使用for语句的else子句(又称no_break)来处理没有找到匹配密码的情况。

最后,顺便说一句,你的代码目前处理了两个账户理论上可能有相同共享用户名的情况。这实际上是你想要的吗?如果不是,你可以使用fetchone(),然后摆脱for循环:

注意,没有经过测试,只是我脑海中的想法。

def check_login_on_db():
    user = UserSignUpDetails(
        None,
        None,
        log_in_window_username_entry.get(),
        log_in_window_password_entry.get()
    )

    enteredUsername = user.username
    enteredPassword = user.password

    with sqlite3.connect("database.db") as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * from SignUpDetails where username = '%s'" % (enteredUsername))
            rows = cursor.fetchall()  # 两个账户实际上可以有相同的用户名吗?

    if not rows:
        log_in_additional_info.config(text="This username is incorrect")
        return

    for row in rows:
        if row[2] == enteredPassword:
            log_in_additional_info.config(text="")
            break
    else:
        log_in_additional_info.config(text="The password is incorrect")

如果你不喜欢这里的else子句,那么在这种情况下,你也可以这样做:

def check_login_on_db():
    user = UserSignUpDetails(
        None,
        None,
        log_in_window_username_entry.get(),
        log_in_window_password_entry.get()
    )

    enteredUsername = user.username
    enteredPassword = user.password

    with sqlite3.connect("database.db") as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * from SignUpDetails where username = '%s'" % (enteredUsername))
            rows = cursor.fetchall()  # 两个账户实际上可以有相同的用户名吗?

    if not rows:
        log_in_additional_info.config(text="This username is incorrect")
        return

    for row in rows:
        if row[2] == enteredPassword:
            log_in_additional_info.config(text="")
            return

    log_in_additional_info.config(text="The password is incorrect")
英文:

I think the use of the variable i here to represent a row makes things a little confusing. Let's rename a few variables to help add some clarity.

While we are cleaning things up, I'm going to make a couple of additional changes. First, SQL tends to not like double quotes, so let's flip your quotes around so SQL sees single quotes. Secondly, I am going to use the else clause (AKA no_break) of the for statement to handle the case that no row with a matching password was found.

Finally, as an aside, your code currently handles the case where two accounts could in theory have the same shared username. Is that actually what you want? If not you might do fetchone() and then get rid of the for loop:

Note, not tested, just from the top of my head.

def check_login_on_db():
    user = UserSignUpDetails(
        None,
        None,
        log_in_window_username_entry.get(),
        log_in_window_password_entry.get()
    )

    enteredUsername = user.username
    enteredPassword = user.password

    with sqlite3.connect("database.db") as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * from SignUpDetails where username = '%s'" % (enteredUsername))
            rows = cursor.fetchall()  # can two accounts actually have the same username?

    if not rows:
        log_in_additional_info.config(text="This username is incorrect")
        return

    for row in rows:
        if row[2] == enteredPassword:
            log_in_additional_info.config(text="")
            break
    else:
        log_in_additional_info.config(text="The password is incorrect")

If you did not like the else clause here then in this instance, you could also do:

def check_login_on_db():
    user = UserSignUpDetails(
        None,
        None,
        log_in_window_username_entry.get(),
        log_in_window_password_entry.get()
    )

    enteredUsername = user.username
    enteredPassword = user.password

    with sqlite3.connect("database.db") as conn:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * from SignUpDetails where username = '%s'" % (enteredUsername))
            rows = cursor.fetchall()  # can two accounts actually have the same username?

    if not rows:
        log_in_additional_info.config(text="This username is incorrect")
        return

    for row in rows:
        if row[2] == enteredPassword:
            log_in_additional_info.config(text="")
            return

    log_in_additional_info.config(text="The password is incorrect")

huangapple
  • 本文由 发表于 2023年3月9日 22:31:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/75685968.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定