SELECT EXISTS检查行是否存在于另一个表中

Question

我试图使用SELECT EXISTS查找基于电子邮件列的任何匹配行。一开始我认为它是有效的，但后来我注意到无论我做什么，它都会给我相同的结果。

代码如下:

def insert():
time.sleep(5)
email = "[e-mail]"
company = "Conglomo"
typeemail = "whitelist"
try:    
connecttosql = sqlite3.connect('database/spamdatabase.db')
connectioncursor = connecttosql.cursor()
connectioncursor.execute("SELECT EXISTS(SELECT 1 FROM loggedemails WHERE email = ?)", (email,))
# print(connectioncursor.fetchone())
if connectioncursor.fetchone():
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:            
sql_insertval = """INSERT INTO heldforreview (email, company, type)
VALUES(?,?,?)
CHECK ;"""
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
print(insert.result)
return insert.result
insert()

上面的代码假定检查loggedemails列以查看[e-mail]是否已经添加。然而，它说它已经被添加了，即使它还没有故意添加。

有什么建议吗?

谢谢大家!

Answer 1

如果您的SELECT EXISTS查询找到您正在寻找的行，那么fetchone将返回一个包含1(意思是真)的元组。

如果没有找到行，那么fetchone将返回一个包含0(即false)的元组。

在这两种情况下，if connectioncursor.fetchone():都将成功，因为非空元组是真值，无论它包含什么。

你可以通过修改你的条件来修复它:

if connectioncursor.fetchone()[0]:

或

if connectioncursor.fetchone()==(1,):