SQLite记录转Python变量：PyQt6邮箱匹配逻辑故障排查

我开发了一个基于PyQt6的登录界面，其中email_compare()方法存在异常。该方法原本打算通过线性搜索，把输入框里的邮箱和SQLite数据库U+P.db的Details表内邮箱对比，但输入内容完全一致时，无法输出‘Correct email’提示。另外我不确定能不能把SQLite记录转为可直接对比的变量，希望得到可行的替代实现方案。

以下是我的代码：

from PyQt6 import QtCore, QtGui, QtWidgets
import sqlite3
from SIGNUP import Ui_MainWindow
class Ui_Dialog(object):
    
    def openWindow(self):
        self.window = QtWidgets.QMainWindow()
        self.ui = Ui_MainWindow()
        self.ui.setupUi(self.window)
        Dialog.close()
        self.window.show()

    def setupUi(self, Dialog):
        Dialog.setObjectName("Dialog")
        Dialog.resize(480, 620)
        Dialog.setStyleSheet("background: rgb(64, 64, 64)")
        self.label = QtWidgets.QLabel(Dialog)
        self.label.setGeometry(QtCore.QRect(210, 60, 200, 50))
        self.label.setStyleSheet("color:rgb(255, 255, 255);\nfont-size: 28pt;")
        self.label.setObjectName("label")
        self.label_2 = QtWidgets.QLabel(Dialog)
        self.label_2.setGeometry(QtCore.QRect(60, 150, 91, 41))
        self.label_2.setStyleSheet("font: 23pt; color: rgb(255, 0, 0)")
        self.label_2.setObjectName("label_2")
        
        self.email = QtWidgets.QLineEdit(Dialog)
        self.email.setGeometry(QtCore.QRect(60, 190, 251, 41))
        self.email.setStyleSheet("color: rgb(255, 255, 255)")
        self.email.setObjectName("email")
        
        self.label_3 = QtWidgets.QLabel(Dialog)
        self.label_3.setGeometry(QtCore.QRect(60, 260, 101, 41))
        self.label_3.setStyleSheet("font: 23pt; color: rgb(255, 0, 0)")
        self.label_3.setObjectName("label_3")
        
        self.password = QtWidgets.QLineEdit(Dialog)
        self.password.setGeometry(QtCore.QRect(60, 300, 251, 41))
        self.password.setStyleSheet("color: rgb(255, 255, 255)")
        self.password.setText("")
        self.password.setObjectName("password")
        
        self.loginbutton = QtWidgets.QPushButton(Dialog)
        self.loginbutton.setGeometry(QtCore.QRect(330, 390, 100, 32))
        self.loginbutton.setStyleSheet("color: rgb(255, 0, 0)")
        self.loginbutton.setObjectName("loginbutton")
        
        self.loginbutton_2 = QtWidgets.QPushButton(Dialog)
        self.loginbutton_2.setGeometry(QtCore.QRect(50, 390, 100, 32))
        self.loginbutton_2.setStyleSheet("background-color: rgb(0, 255, 212);")
        self.loginbutton_2.setObjectName("loginbutton_2")

        self.loginbutton_2.clicked.connect(self.openWindow)

        self.loginbutton.clicked.connect(self.show_password)
        self.loginbutton.clicked.connect(self.email_compare)
        
        self.retranslateUi(Dialog)
        QtCore.QMetaObject.connectSlotsByName(Dialog)
        
    def retranslateUi(self, Dialog):
        _translate = QtCore.QCoreApplication.translate
        Dialog.setWindowTitle(_translate("Dialog", "Dialog"))
        self.label.setText(_translate("Dialog", "Login"))
        self.label_2.setText(_translate("Dialog", "Email"))
        self.label_3.setText(_translate("Dialog", "Password"))
        self.loginbutton.setText(_translate("Dialog", "Login!"))
        self.loginbutton_2.setText(_translate("Dialog", "Sign up!"))


    # this is my problem
    def email_compare(self):
        count = 0
        check = self.email.text()
        check = "('"+check+"',)"
        conn = sqlite3.connect("U+P.db")
        c = conn.cursor()
        c.execute("SELECT email FROM Details")
        compare = c.fetchall() 
        print(compare)
        print(check)
        right = False
        while count < len(compare) and right == False:
            print(compare[count])
            if compare[count] == check:
                print("Correct email")
                right = True
            else:
                count = count + 1
        conn.close()



if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    Dialog = QtWidgets.QDialog()
    ui = Ui_Dialog()
    ui.setupUi(Dialog)
    Dialog.show()
    sys.exit(app.exec())

问题根源 #

1. 数据类型不匹配：c.fetchall()返回的是元组列表（例如[('test@example.com',)]），但你手动把输入邮箱拼接成了字符串格式的元组（例如"('test@example.com',)"），两种类型无法匹配。
2. 低效的本地遍历：没必要把所有邮箱拉到本地对比，SQL原生支持条件查询，效率更高。

解决方案 #

方案一：修复现有线性搜索逻辑 #

直接提取元组内的字符串进行对比，无需手动拼接格式：

def email_compare(self):
    check = self.email.text().strip()  # 去除前后空格，避免输入误判
    conn = sqlite3.connect("U+P.db")
    c = conn.cursor()
    c.execute("SELECT email FROM Details")
    compare = c.fetchall() 
    right = False
    for email_tuple in compare:
        # 取元组第一个元素（邮箱字符串）对比
        if email_tuple[0] == check:
            print("Correct email")
            right = True
            break
    conn.close()

方案二：用SQL条件查询替代本地遍历（推荐） #

直接让数据库筛选匹配的邮箱，代码更简洁且效率更高，同时避免SQL注入风险：

def email_compare(self):
    check = self.email.text().strip()
    conn = sqlite3.connect("U+P.db")
    c = conn.cursor()
    # 使用参数化查询，防止SQL注入
    c.execute("SELECT 1 FROM Details WHERE email = ?", (check,))
    # 若查询到结果，说明邮箱存在
    if c.fetchone():
        print("Correct email")
    conn.close()

额外优化建议 #

• 登录逻辑可以一步完成邮箱+密码校验：

def login_check(self):
    email = self.email.text().strip()
    password = self.password.text().strip()
    conn = sqlite3.connect("U+P.db")
    c = conn.cursor()
    c.execute("SELECT 1 FROM Details WHERE email = ? AND password = ?", (email, password))
    if c.fetchone():
        print("登录成功")
        # 此处添加跳转主界面逻辑
    else:
        print("邮箱或密码错误")
    conn.close()

• 永远使用参数化查询，不要手动拼接SQL语句，避免安全风险。

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