从含XML格式参数的SQLite查询失败登录记录，求更优方案

问题背景 #

我用SQLite数据库记录生产设备的事件，需要查询所有失败的登录尝试。目前的实现通过LIKE关键字匹配XML格式的Details列，但这种方式对XML格式的依赖极强，一旦XML结构（比如标签换行、顺序调整）发生变化，查询就会失效，需要更可靠的解决方案。

Event表结构：

• Name：事件名称，登录记录的值为Login
• User：操作用户
• Timestamp：事件时间
• Details：存储XML格式的事件详情，登录记录的XML示例：

<Parameter>
    <Name>Type</Name>
    <Value>21CFR11</Value>
</Parameter>
<Parameter>
    <Name>Success</Name>
    <Value>True</Value>
</Parameter>

其中<Name>Success</Name>对应的<Value>为False时，表示登录失败。

现有实现的问题 #

当前代码通过LIKE '%Success%' AND LIKE '%False%'匹配失败登录，但这种方法存在明显缺陷：

• 若XML中Success和False分属不同上下文（比如有其他字段包含Success），会出现误匹配
• XML标签格式调整（比如增加空格、换行、属性）会导致匹配失效

现有代码：

import os
import sqlite3

def query_db_files(folder_path):
    count=0
    for file in os.listdir(folder_path):
        if file.endswith(".db"):
            connection = sqlite3.connect(os.path.join(folder_path, file))
            cursor = connection.cursor()
            query = "SELECT Name, User, TimeStamp FROM Event WHERE Name = 'Login' AND Details LIKE '%Success%' AND Details LIKE '%False%'"
            cursor.execute(query)
            results = cursor.fetchall()
            if results != []:
                for tuples in results:
                    print(tuples)
                    count=count+1
    print(count)
query_db_files("C:\Users\David\Downloads\Optel\Audit Trail")

优化方案 #

方案1：Python端解析XML（推荐，兼容性强） #

把所有登录事件查询出来后，在Python中用标准库xml.etree.ElementTree解析XML，精准判断Success的值。这种方式不依赖SQLite的扩展，适配任何合法的XML格式。

修改后的代码：

import os
import sqlite3
import xml.etree.ElementTree as ET

def query_db_files(folder_path):
    count = 0
    for file in os.listdir(folder_path):
        if file.endswith(".db"):
            conn = sqlite3.connect(os.path.join(folder_path, file))
            cursor = conn.cursor()
            # 先查询所有登录事件
            cursor.execute("SELECT Name, User, TimeStamp, Details FROM Event WHERE Name = 'Login'")
            for row in cursor.fetchall():
                name, user, timestamp, details = row
                try:
                    # 包裹根节点，避免无单一根的XML解析报错
                    root = ET.fromstring(f"<Root>{details}</Root>")
                    # 遍历Parameter节点，查找Success对应的值
                    for param in root.findall(".//Parameter"):
                        param_name = param.find("Name").text.strip()
                        param_value = param.find("Value").text.strip()
                        if param_name == "Success" and param_value == "False":
                            print((name, user, timestamp))
                            count += 1
                            break  # 找到目标参数后跳出循环
                except ET.ParseError:
                    # 处理XML解析失败的异常
                    print(f"XML解析失败，内容片段: {details[:50]}...")
            conn.close()
    print(f"总计找到 {count} 条失败登录记录")

# 注意路径前加r避免转义字符问题
query_db_files(r"C:\Users\David\Downloads\Optel\Audit Trail")

方案2：使用SQLite的XML扩展（需环境支持） #

如果你的SQLite环境支持XML扩展（如sqlite3-xml），可以直接在SQL中用XPath查询，将过滤逻辑放在数据库层完成。

示例SQL查询：

SELECT Name, User, TimeStamp 
FROM Event 
WHERE Name = 'Login' 
AND xml_xpath(Details, '//Parameter[Name/text()="Success"]/Value/text()') = 'False'

注意：这种方式需要确保SQLite已加载XML扩展，不同环境的配置方式不同，兼容性不如方案1。

方案优势对比 #

• Python解析方案：无需修改SQLite配置，适配所有合法XML结构，容错性强，适合大多数场景
• SQLite XML扩展方案：查询效率更高（过滤在数据库层完成），但依赖特定环境配置，对XML格式的严谨性要求较高

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