python - 如何搜索具有不同搜索数据的行(有些给出，有些没有)

Question

我使用 SQL 语句根据给定的数据在数据库中搜索行。如果列是(ID，用户名，密码，权限，Class_Count)，那么我的程序有时只会搜索用户名或权限。但有时它会同时搜索用户名和类(class)数。我不知道有什么方法可以轻松地将其实现到我的代码中，而无需创建(我相信)大约 7 个不同的 IF 语句来检查哪些数据用于搜索(示例将在下面的代码中给出)

def Get_Users_DB(self, Search_Data):
    Details_Dic = Create_User_Dict((None,Search_Data[0],None,Search_Data[1],Search_Data[2]))
    try:  # Defensive programming to prevent database errors from stopping the program from running
        with global_lock:
            if Details_Dic["Username"]:
                # If a username is given, no other values need to be checked as username are unique
                self.DB_Cursor.execute("SELECT * FROM USERS WHERE username = ?", (Details_Dic["Username"],))
                # Selects user from USERS table based on the username provided
                User_Data = self.DB_Cursor.fetchall()
                # Fetches the user if applicable, returns as a list for processing purposes

            elif Details_Dic["Clearance"] and Details_Dic["Class_Count"] is not None:
                print("Here b0ss")
                # If there is a value for clearance and Class_Count is not a none type
                self.DB_Cursor.execute("SELECT * FROM USERS WHERE\
                                       clearance = ? AND Class_Count = ?",
                                       (Details_Dic["Clearance"], Details_Dic["Class_Count"]))
                # Select all users based on these restrictions
                User_Data = self.DB_Cursor.fetchall()

            elif Details_Dic["Clearance"]:  # If only a clearance level is given
                self.DB_Cursor.execute("SELECT * FROM USERS WHERE\
                                       clearance = ?", (Details_Dic["Clearance"],))
                User_Data = self.DB_Cursor.fetchall()
            elif Details_Dic["Class_Count"] is not None:  # If only a class value is given
                self.DB_Cursor.execute("SELECT * FROM USERS WHERE\
                                       Class_Count = ?", (Details_Dic["Class_Count"],))
                User_Data = self.DB_Cursor.fetchall()
            else:  # If no values are given, get all users
                self.DB_Cursor.execute("SELECT * FROM USERS")
                User_Data = self.DB_Cursor.fetchall()

        if User_Data:  # If any value was returned from the database
            User_Dict_List = []
            for User_Details in User_Data:  # For every user in the list convert them to a dictionary
                User_Dict = Create_User_Dict(User_Details)
                User_Dict_List.append(User_Dict)
            return User_Dict_List
        else:
            return False  # Tell called from function that the user does not exist

    except sqlite3.Error as Err:  # If an error occurs display a message in the console
        Error_Report(Err, "Get_User_DB")
        return False  # Tell called from function that the function was unsuccessful

从这个程序中，我基本上想要一种更简化的方法来检查给定的数据以及查询数据库所需的数据

编辑:我现在尝试了提供的方法:

def Create_Where_Condition(self, Details_Dic):
    print("In Where Condition")
    Where_Condition = ""
    for Key, Value in Details_Dic.items():
        print("Key:",Key)
        print("Value:", Value)
        if Value is not None:
            Prefix = " AND " if Where_Condition else " WHERE "
            Where_Condition += Prefix + "{}={}".format(Key, Value)
    return Where_Condition

def Get_Users_DB(self,Search_Data):
    print("In get_user_db")
    Details_Dic = Create_User_Dict((None, Search_Data[0], None, Search_Data[1], Search_Data[2]))
    print("after details_dic")
    SQL_Statement = "SELECT * FROM USERS" + self.Create_Where_Condition(Details_Dic)
    print("SQL STATEMENT:\n{}".format(SQL_Statement))
    try:  # Defensive programming to prevent database errors from stopping the program from running
        with global_lock:
            self.DB_Cursor.execute(SQL_Statement)
            User_Data = self.DB_Cursor.fetchall()
            print(User_Data)
        if User_Data:  # If any value was returned from the database
            User_Dict_List = []
            for User_Details in User_Data:  # For every user in the list convert them to a dictionary
                User_Dict = Create_User_Dict(User_Details)
                User_Dict_List.append(User_Dict)
            return User_Dict_List
        else:
            return False  # Tell called from function that the user does not exist

    except sqlite3.Error as Err:  # If an error occurs display a message in the console
        Error_Report(Err, "Get_User_DB")
        return False  # Tell called from function that the function was unsuccessful

但是现在我收到错误:

sqlite3.OperationalError: no such column: foo

其中“foo”是我正在搜索的用户名

Answer 1

现在您的字典键与表列的大小写不匹配。如果您可以更改它，您可以创建一个函数来为您创建 WHERE 条件:

def create_where_condition(details_dic):
    where_condition = ""
    for key, value in details_dic.items():
        if value is not None:
            prefix = " AND " if where_condition else " WHERE "
            where_condition += prefix + '{}="{}"'.format(key, value)
    return where_condition

create_where_condition({"username": "Tom", "clearance": None, "Class_Count": 10})  # -> ' WHERE username=Tom AND Class_Count=10'
create_where_condition({"username": "Tom", "clearance": 100, "Class_Count": 10})  # -> ' WHERE username=Tom AND clearance=100 AND Class_Count=10'
create_where_condition({"username": None, "clearance": None, "Class_Count": None})  # -> ''

此方法的优点是，如果您想在 WHERE 子句中包含更多行，而无需添加额外的 if/elif 语句，它可以扩展。

如果您的details_dic还包含与表中的列不对应的其他键，或者您不希望包含在WHERE子句中，则可以添加白名单作为第二个参数:

def create_where_condition(details_dic, rows_to_include):
    where_condition = ""
    for key, value in details_dic.items():
        if key in rows_to_include and value is not None:
            if isinstance(value, str):
                value = '"' + value + '"'
            prefix = " AND " if where_condition else " WHERE "
            where_condition += prefix + '{}={}'.format(key, value)
    return where_condition