SQLite3表未正确保存

Question

我最近在做一个登录数据库项目，遇到了一个问题：表没有正确保存

我有密码

def change_password(username, password):
    while 1:
        new_pass = input("Enter new password:\n")
        if not new_pass:
            print("Cancelled")
            raise SystemExit()

        if new_pass == password:
            print("That's already your password.")
        elif len(new_pass) < 6:
            print("Password must be at least 6 characters")
        else:
            break

    cursor.execute("""UPDATE logins SET password = ? WHERE username = ?""", (new_pass, username))

    date_time = dt.now()
    time = date_time.strftime('%H:%M:%S')
    date = date_time.strftime('%d/%m/%Y')

    cursor.execute("""UPDATE logins SET changepass_date = ? WHERE username = ?""", (date, username))
    cursor.execute("""UPDATE logins SET changepass_time = ? WHERE username = ?""", (time, username))
    db.commit()

它完全按照预期工作（更新password、changepass_date和changepass_time）

但是我也有代码

def change_username(username):
    while 1:
        new_username = input("Enter new username:\n")
        if not new_username:
            print("Cancelled")
            raise SystemExit()

        if new_username == username:
            print("That's already your username")
        elif len(new_username) < 6:
            print("Username must be at least 6 characters")
        else:
            break

    try:
        cursor.execute("""UPDATE logins SET username = ? WHERE username = ?""", (new_username, username))
        date_time = dt.now()
        time = date_time.strftime('%H:%M:%S')
        date = date_time.strftime('%d/%m/%Y')

        cursor.execute("""UPDATE logins SET changeuser_date = ? WHERE username = ?""", (date, username))  # doesn't work
        cursor.execute("""UPDATE logins SET changeuser_time = ? WHERE username = ?""", (time, username))  # doesn't work
        db.commit()

        print("Successfully changed your username to '{}'".format(new_username))
    except sql.IntegrityError:
        print("That username is already taken")
        change_username(username)

它不能按预期工作（它只保存username，而不是changeuser_date或changeuser_time）。 我花了很多时间研究这个问题，并在StackOverflow上看到了不同的页面，然而，这是因为没有提交—我已经（db.commit()）

我创建表的代码是

cursor.execute(
    '''
    CREATE TABLE IF NOT EXISTS logins(
        id INTEGER PRIMARY KEY unique,
        username TEXT unique,
        password TEXT,
        admin BOOLEAN,
        created_date TEXT,
        created_time TEXT,
        recent_date TEXT,
        recent_time TEXT,
        changepass_date TEXT,
        changepass_time TEXT,
        changeuser_date TEXT,
        changeuser_time TEXT)
    ''')

非常感谢您的帮助

Answer 1

当您尝试更新日期和时间时，用户名已经更改，因此update语句找不到任何具有旧username的行

在不起作用的语句中，将new_username而不是username传递给UPDATE语句的WHERE子句

---

此外，您可以对其进行改进，并将3个查询减少为1：

cursor.execute(
    """
    UPDATE logins
      SET (username = ?, changeuser_date = ?, changeuser_time = ?)
      WHERE username = ?
    """,
    (new_username, date, time, username))

密码更新也是如此：

cursor.execute(
    """
    UPDATE logins
      SET (password = ?, changepass_date = ?, changepass_time = ?)
      WHERE username = ?
    """,
    (new_pass, date, time, username))