python - 如何判断sqlite3数据库中是否存在值，python

Question

这个问题在这里已经有了答案:

"Insert if not exists" statement in SQLite (4 个回答)

关闭 5年前。

如何判断一个值是否存在于 sqlite3 数据库中，python

到目前为止，这是我的代码:

def signup():
    email = request.form['email']
    username = request.form['user']
    password = request.form['password']
    g.db.execute("INSERT INTO users VALUES (?, ?, ?)", [email, username, password])
    g.db.commit()

如果 email 和 username 不在数据库中，我希望它只将值插入数据库，但我不知道从哪里开始。

Answer 1

您所要做的就是在插入之前进行查询，然后执行 fetchone .如果 fetchone 返回一些东西，那么您肯定知道数据库中已经有一条记录，该记录具有 email 或 username:

def signup():
    email = request.form['email']
    username = request.form['user']
    password = request.form['password']

    # Create cursor object
    cur = g.db.cursor()

    # run a select query against the table to see if any record exists
    # that has the email or username
    cur.execute("""SELECT email
                          ,username
                   FROM users
                   WHERE email=?
                       OR username=?""",
                (email, username))

    # Fetch one result from the query because it
    # doesn't matter how many records are returned.
    # If it returns just one result, then you know
    # that a record already exists in the table.
    # If no results are pulled from the query, then
    # fetchone will return None.
    result = cur.fetchone()

    if result:
        # Record already exists
        # Do something that tells the user that email/user handle already exists
    else:
        cur.execute("INSERT INTO users VALUES (?, ?, ?)", (email, username, password))
        g.db.commit()