推送公交站点至供应商API后，SQL Server交叉表更新异常求助

排查关键点与修复建议 #

1. Python代码核心问题：未提交事务+重复创建连接 #

• 未提交事务：pyodbc默认不会自动提交事务，循环中执行存储过程后未调用connMyCompanyData.commit()，导致大部分更新操作未写入数据库，仅最后几个连接关闭时可能意外提交。
• 重复创建连接：每次循环新建数据库连接，不仅效率极低，还可能因连接池耗尽导致部分请求被中断，未执行更新。

修复后的Python代码：

import pyodbc as db 
import requests

url = "<vendor URL>"

def GetData():
    # 初始化连接，放在循环外复用
    connVendorData = db.connect(<Driver Info>)
    connVendorData.setdecoding(db.SQL_CHAR, encoding='latin1')
    connVendorData.setencoding('latin1')
    
    connMyCompanyData = db.connect(<Driver Info>)
    connMyCompanyData.setdecoding(db.SQL_CHAR, encoding='latin1')
    connMyCompanyData.setencoding('latin1')
    
    cursor_Vendor = connVendorData.cursor()
    cursor_Vendor.execute('''exec get_addressBookUpdates''')

    cursor_MyCompany = connMyCompanyData.cursor()
    
    for row in cursor_Vendor: 
        payload = {
            "geofence": { "circle": {
                    "latitude": str(row.latitude),
                    "longitude": str(row.longitude),
                    "radiusMeters": str(row.radius)
                } },
            "formattedAddress": row.formattedAddress,
            "longitude": str(row.longitude),
            "latitude": str(row.latitude),
            "name": row.addressBookTitle
        }
        headers = {
            "accept": "application/json",
            "content-type": "application/json",
            "authorization": "<vendor token>"
        }

        try:
            response = requests.post(url, json=payload, headers=headers, timeout=10)
            response.raise_for_status()  # 捕获HTTP请求错误
            # 执行存储过程
            cursor_MyCompany.execute('''exec update_stopIDs_xref_AfterProcessing ?, ?''', row.status, response.text)
            connMyCompanyData.commit()  # 提交事务
        except Exception as e:
            print(f"处理记录失败: {row.formattedAddress}, 错误信息: {str(e)}")
            connMyCompanyData.rollback()  # 出错回滚，避免脏数据
    
    # 关闭资源
    cursor_Vendor.close()
    connVendorData.close()
    cursor_MyCompany.close()
    connMyCompanyData.close()
        
GetData()

2. 存储过程核心问题：匹配条件容错性差 #

• formattedAddress匹配问题：直接用原始字符串匹配，容易因前后空格、全角/半角差异、编码不一致导致JOIN失败，这是批量更新仅13条成功的核心原因。
• 无执行日志：无法追踪每条更新的匹配行数，排查问题无依据。

优化后的存储过程：

alter PROC update_stopIDs_xref_AfterProcessing
@status VARCHAR(25),
@json NVARCHAR(MAX)

AS

SET NOCOUNT ON 

-- 先创建日志表（仅需执行一次）：
-- CREATE TABLE ProcessLog (
--     LogID INT IDENTITY(1,1) PRIMARY KEY,
--     LogTime DATETIME DEFAULT GETDATE(),
--     Status VARCHAR(25),
--     JsonData NVARCHAR(MAX),
--     MatchedCount INT
-- )

DECLARE @matchedCount INT = 0

-- 加载参数到临时表，去除地址前后空格
DROP TABLE IF EXISTS #temp 
SELECT  JSON_VALUE(@json, '$.data.id') AS vendorID,
        LTRIM(RTRIM(JSON_VALUE(@json, '$.data.formattedAddress'))) AS formattedAddress,
        JSON_VALUE(@json, '$.data.createdAtTime') AS createdTime
INTO    #temp 

-- 根据状态执行对应操作，匹配时两边都去除空格
IF @status = 'New'
BEGIN
    UPDATE  sx
    SET     vendorId = t.vendorID,
            status = 'Processed'
    FROM    stopIDs_xref sx
    JOIN    #temp t ON LTRIM(RTRIM(sx.formattedAddress)) = t.formattedAddress
    SET @matchedCount = @@ROWCOUNT
END
ELSE IF @status = 'Update'
BEGIN
    UPDATE  sx
    SET     status = 'Processed'
    FROM    stopIDs_xref sx
    JOIN    #temp t ON LTRIM(RTRIM(sx.formattedAddress)) = t.formattedAddress
    SET @matchedCount = @@ROWCOUNT
END
ELSE IF @status = 'Delete'
BEGIN
    DELETE sx
    FROM    stopIDs_xref sx
    JOIN    #temp t ON LTRIM(RTRIM(sx.formattedAddress)) = t.formattedAddress
    SET @matchedCount = @@ROWCOUNT
END

-- 记录处理日志，方便排查
INSERT INTO ProcessLog (Status, JsonData, MatchedCount)
VALUES (@status, @json, @matchedCount)

-- 返回匹配行数，供Python端验证
SELECT @matchedCount AS MatchedRows

3. 额外排查项 #

• API响应异常：批量推送时供应商可能触发限流，导致部分请求返回错误（如429、500），此时response.text不是预期的JSON结构，JSON_VALUE取不到vendorID，需在Python中捕获并记录。
• 编码一致性：确保Python中latin1编码与SQL Server中formattedAddress字段的编码一致，避免字符转义导致匹配失败。

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