正则匹配数据库JSON中Confidence值失败，如何正确提取？

从数据库解析数据时，已完成数据库连接与查询，尝试遍历每行数据提取Confidence值，但运行后输出全为空列表，需要得到每行对应的所有Confidence值列表。

当前实现细节 #

1. 待解析的JSON片段（部分） #

{"HealthPiQturePotentialConditions":{"PotentialCondition":[{"Condition":"Beign a great guy","HeaderCode":"111","ContributingFactors":"Free X, Orange, GreatGuy factor, aPTT","Confidence":"99.0%","ProviderGenerated":true,"DemographicPrevalence":"0.2%"},{"Condition":"Some Disease","HeaderCode":"E11","ContributingFactors":"High Ego","Confidence":"99.0%","ProviderGenerated":true,"DemographicPrevalence":"64.6%"},{"Condition":"Polywag","HeaderCode":"R11","ContributingFactors":"Master mind","Confidence":"99.0%","ProviderGenerated":false,"DemographicPrevalence":"Yellow"},{"Condition":"Muscles","HeaderCode":"M11","ContributingFactors":"Master of None","Confidence":"99.0%","ProviderGenerated":true,"DemographicPrevalence":"61.3%"}]}}

2. SQL连接与查询代码 #

import mysql.connector

conn = mysql.connector.connect(
    host = 'X',
    port = 'X',
    user = 'X',
    passwd = 'X',
    database = 'product'
)

cursor = conn.cursor()

cursor.execute("""
SELECT a.ID, a.lab_data from `product`.LAB_data a
inner join `Analytical`.Decline_lab b
on a.id = b.ID;
""")

3. 处理函数代码 #

def reason_decline(row):
    Bs_data = BS(row)

    ID = Bs_data.find_all("p")

    if re.findall("{Condition:(.*?)}", ID[0].string.replace('"', '')) != []:
            nodes4 = re.findall(',"Confidence":\s*"([^"]+),', ARCID[0].string.replace('"', ''))
            Confidence = nodes4
            Conf_lvls = []
            for c in Confidence:
                Conf_lvls.append(c)


    else:
        return 0

    return Conf_lvls

当前输出 #

Confidence_lvl_lst = []

for row in cursor:
    Conf_lvls1 = reason_decline(row[1])
    if Conf_lvls1 != 0:
        Confidence_lvl_lst.append(Conf_lvls1)

Confidence_lvl_lst
[[],
 [],
...
 [],
 [],
 []]

尝试过的正则表达式 #

"Confidence":\s*"([^"]+),"
"Confidence":"([^"]+)"

期望输出 #

[["99.0%", "99.0%", "99.0%", "99.0%"], .....]

（省略号代表数据库其他行的结果）

解决方案 #

问题根源 #

1. 错误解析方式：用BeautifulSoup处理JSON字符串完全错误，BeautifulSoup是解析HTML/XML的工具，JSON应该用Python内置的json模块解析。
2. 变量错误：代码中ARCID[0].string是未定义变量，应为ID[0].string，但即使修正，用正则解析JSON也容易出现格式匹配问题。
3. 正则失效：代码中提前执行了.replace('"', '')，导致正则表达式中的双引号匹配规则完全失效。
4. 逻辑冗余：提取结果后循环添加到列表属于冗余操作，可直接生成列表。

修正后的代码 #

import json
import mysql.connector

def reason_decline(row):
    try:
        # 解析JSON字符串
        data = json.loads(row)
        # 逐层获取PotentialCondition列表
        potential_conditions = data.get("HealthPiQturePotentialConditions", {}).get("PotentialCondition", [])
        # 提取所有非空的Confidence值
        conf_lvls = [cond.get("Confidence") for cond in potential_conditions if cond.get("Confidence")]
        # 有结果返回列表，无结果返回0
        return conf_lvls if conf_lvls else 0
    except json.JSONDecodeError:
        # 处理JSON格式错误的情况
        return 0

# 数据库连接与查询
conn = mysql.connector.connect(
    host = 'X',
    port = 'X',
    user = 'X',
    passwd = 'X',
    database = 'product'
)

cursor = conn.cursor()

cursor.execute("""
SELECT a.ID, a.lab_data from `product`.LAB_data a
inner join `Analytical`.Decline_lab b
on a.id = b.ID;
""")

# 遍历提取数据
Confidence_lvl_lst = []
for row in cursor:
    Conf_lvls1 = reason_decline(row[1])
    if Conf_lvls1 != 0:
        Confidence_lvl_lst.append(Conf_lvls1)

print(Confidence_lvl_lst)

说明 #

• 用json.loads()直接解析JSON字符串，避免正则匹配的不确定性。
• 通过字典的get()方法逐层取值，避免键不存在导致的报错。
• 列表推导式直接生成Confidence值列表，简化代码逻辑。
• 添加JSONDecodeError捕获，处理数据库中JSON格式错误的情况。

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