如何在Python中显示并更新Oracle SQL标签内的数据？

一、提取标签内的内容 #

1. SQL层面直接提取（推荐，减少客户端处理） #

利用数据库字符串函数直接提取目标标签内的内容，避免返回整条冗余数据：

提取ServerName

SELECT 
  TRIM(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(tr_data, '</ServerName>', 1),
      '<ServerName>', -1
    )
  ) AS ServerName
FROM blahblahblah.transport 
WHERE tr_type = '51' 
  AND tr_data LIKE '%<ServerName>%' 
  AND tr_data LIKE '%</ServerName>%'

提取Path

替换标签即可：

SELECT 
  TRIM(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(tr_data, '</Path>', 1),
      '<Path>', -1
    )
  ) AS Path
FROM blahblahblah.transport 
WHERE tr_type = '51' 
  AND tr_data LIKE '%<Path>%' 
  AND tr_data LIKE '%</Path>%'

2. Pandas层面提取（适配SQL不支持复杂操作的场景） #

先修正原代码的缩进错误（df = pd.read_sql的缩进需与query保持一致），再用正则提取目标内容：

print(f"Which transfer type do you want to see? 
 (1) - ServerName 
 (2) - Path")
choice_type = int(input("Enter transfer type: "))

if choice_type == 1:
    query = "SELECT tr_data FROM blahblahblah.transport WHERE tr_type = '51' AND tr_data LIKE '%<ServerName>%' AND tr_data LIKE '%</ServerName>%'"
    df = pd.read_sql(query, connection)
    # 提取ServerName标签内的内容
    df['ServerName'] = df['tr_data'].str.extract(r'<ServerName>(.*?)</ServerName>', expand=False).str.strip()
    # 仅显示提取后的结果
    print(df['ServerName'])
elif choice_type == 2:
    query = "SELECT tr_data FROM blahblahblah.transport WHERE tr_type = '51' AND tr_data LIKE '%<Path>%' AND tr_data LIKE '%</Path>%'"
    df = pd.read_sql(query, connection)
    # 提取Path标签内的内容
    df['Path'] = df['tr_data'].str.extract(r'<Path>(.*?)</Path>', expand=False).str.strip()
    print(df['Path'])

二、更新标签内的内容 #

1. SQL批量更新（高效，适合大批量数据） #

直接用REPLACE函数替换标签内的目标值（假设每条数据仅含一个目标标签）：

更新ServerName

UPDATE blahblahblah.transport
SET tr_data = REPLACE(
    tr_data,
    CONCAT('<ServerName>', '旧服务器名', '</ServerName>'),
    CONCAT('<ServerName>', '新服务器名', '</ServerName>')
)
WHERE tr_type = '51'
  AND tr_data LIKE CONCAT('%<ServerName>', '旧服务器名', '</ServerName>%')

更新Path

同理替换标签和值即可：

UPDATE blahblahblah.transport
SET tr_data = REPLACE(
    tr_data,
    CONCAT('<Path>', '旧路径', '</Path>'),
    CONCAT('<Path>', '新路径', '</Path>')
)
WHERE tr_type = '51'
  AND tr_data LIKE CONCAT('%<Path>', '旧路径', '</Path>%')

2. Python处理后更新（适合复杂逻辑场景） #

先提取数据、修改内容，再批量更新回数据库：

# 1. 读取需要更新的行（需包含唯一标识列，比如id）
query = "SELECT id, tr_data FROM blahblahblah.transport WHERE tr_type = '51' AND tr_data LIKE '%<ServerName>%'"
df = pd.read_sql(query, connection)

# 2. 替换标签内的内容（示例：将"old_server"改为"new_server"）
df['updated_tr_data'] = df['tr_data'].str.replace(
    r'<ServerName>old_server</ServerName>',
    '<ServerName>new_server</ServerName>',
    regex=True
)

# 3. 批量更新回数据库
for _, row in df.iterrows():
    update_sql = "UPDATE blahblahblah.transport SET tr_data = %s WHERE id = %s"
    connection.execute(update_sql, (row['updated_tr_data'], row['id']))
connection.commit()

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