使用SELECT EXISTS检查另一表行是否存在时结果恒相同问题求助

问题原因 #

• 核心逻辑错误：SELECT EXISTS 查询的返回结果是单元素元组，匹配到记录时返回 (1,)，未匹配到返回 (0,)。Python 里所有非空元组的布尔值都是 True，你直接用 if connectioncursor.fetchone() 做判断，不管有没有匹配到记录都会进入已处理的分支。
• SQL语法错误：插入语句末尾的 CHECK ; 是非法内容，就算进入插入分支也会触发语法报错。

修复步骤 #

1. 调整判断逻辑，读取元组的第一个值做判断
2. 删除插入语句中多余的 CHECK 关键字
3. 建议补充数据库连接关闭逻辑，避免资源占用

修复后代码示例 #

import time
import sqlite3

def insert():
    time.sleep(5)
    email = "fakeemailgunicornalltestf@fakemail.com"
    company = "Conglomo"
    typeemail = "whitelist"
    connecttosql = None
    try:
        connecttosql = sqlite3.connect('database/spamdatabase.db')
        connectioncursor = connecttosql.cursor()

        connectioncursor.execute("SELECT EXISTS(SELECT 1 FROM loggedemails WHERE email = ?)", (email,))
        # 取元组第一个值判断是否存在记录
        exists = connectioncursor.fetchone()[0]
        if exists:
            alreadyprocessed = "This has been already processed. This is was an error you may want to reverse this action. (Feature coming soon)"
            print(alreadyprocessed)
            return alreadyprocessed
        else:
            # 删除多余的CHECK关键字
            sql_insertval = """INSERT INTO heldforreview (email, company, type)
                                VALUES(?,?,?)"""
            sowreqeust = (email,company,typeemail)
            connectioncursor.execute(sql_insertval, sowreqeust)
            connecttosql.commit()
            insert.result = "SUCCESS"

    except Exception as Argument:
        print("Email is added to database error: "+str(Argument))
        insert.result = "FAILED"
        print(insert.result)
        return insert.result
    finally:
        # 操作完成后关闭数据库连接，避免资源泄漏
        if connecttosql:
            connecttosql.close()
    print(insert.result)
    return insert.result

insert()

内容的提问来源于stack exchange，提问作者user16660683