使用Python从SQL Server获取XML数据时内容被截断问题

问题描述 #

使用pyodbc从SQL Server查询XML格式数据并写入唯一命名的XML文件，小数据量时正常，但数据长度超过2037字符时，仅返回前2037字符。尝试pymssql、fetchval()、fetchone()、fetchall()均存在截断问题。

环境信息 #

• SQL Server版本：15.0.2000.5(SQL Server 2019 Express)
• 驱动：ODBC Driver 17 for SQL Server
• Python版本：3.11.1
• pyodbc版本：4.0.35
• 运行环境：Windows Server 2016 Standard

用于查询XML的SQL语句 #

SELECT 
  C.BLKODU AS "BLKODU", 
  C.CARIKODU AS "CARIKODU", 
  C.TICARI_UNVANI AS "TICARI_UNVANI", 
  C.ADI_SOYADI AS "ADI_SOYADI", 
  C.VERGI_NO AS "VERGI_NO", 
  C.TC_KIMLIK_NO AS "TC_KIMLIK_NO", 
  C.VERGI_DAIRESI AS "VERGI_DAIRESI", 
  C.CEP_TEL AS "CEP_TEL", 
  C.ILI AS "ILI", 
  C.ILCESI AS "ILCESI", 
  C.ADRESI_1 AS "ADRESI", 
  (SELECT 
      CHR.BLKODU AS "BLKODU", 
      CHR.EVRAK_NO AS "EVRAK_NO", 
      CHR.MAKBUZNO AS "MAKBUZ_NO", 
      CAST(CHR.TARIHI AS DATE) AS "TARIHI", 
      CAST(CHR.VADESI AS DATE) AS "VADESI",  
      CHR.MUH_DURUM AS "MUH_DURUM", 
      CAST(CHR.KPB_ATUT AS DECIMAL(10, 2)) AS "KPB_ATUT", 
      CAST(CHR.KPB_BTUT AS DECIMAL(10, 2)) AS "KPB_BTUT" 
   FROM CARIHR AS CHR 
   WHERE CHR.BLCRKODU = C.BLKODU 
   ORDER BY CHR.TARIHI
   FOR XML PATH('CARIHR'), TYPE) 
FROM CARI AS C 
WHERE C.CARIKODU = 'CR00001'
FOR XML PATH ('CARI')

最初的Python代码 #

import pyodbc
import uuid
import codecs

import query
import core

conn = pyodbc.connect(core.connection_string, commit=True)
cursor = conn.cursor()
cursor.execute(query.ctr)
row = cursor.fetchval()
id = uuid.uuid4()
xml_file = "./temp/"+str(id)+".xml"
xml = codecs.open(xml_file, "w", "utf-8")
xml.write(row)
xml.close()

尝试过的其他方法 #

import pyodbc
import uuid
import codecs

conn = pyodbc.connect(connect_string, commit=True)
cursor = conn.cursor()
cursor.execute(query.ctr)
row = cursor.fetchall()
data = ','.join(row[0])
id = uuid.uuid4()
xml_file = "./temp/"+str(id)+".xml"
xml = codecs.open(xml_file, "w", "utf-8")
xml.write(data)
xml.close()

解决方案 #

方法一：读取完整的大对象内容 #

SQL Server返回的XML类型在pyodbc中被视为Large Object（LOB），默认仅返回前2037字符。需要调用read()方法获取完整内容：

import pyodbc
import uuid
import codecs

import query
import core

conn = pyodbc.connect(core.connection_string, commit=True)
cursor = conn.cursor()
cursor.execute(query.ctr)
row = cursor.fetchone()
# 对XML类型字段调用read()获取全部内容
xml_content = row[0].read()
id = uuid.uuid4()
xml_file = "./temp/"+str(id)+".xml"
with codecs.open(xml_file, "w", "utf-8") as xml:
    xml.write(xml_content)

方法二：将XML转换为NVARCHAR(MAX)返回 #

修改SQL语句，将查询结果强制转换为NVARCHAR(MAX)类型，避免LOB处理问题：

SELECT CAST(
(
    SELECT 
      C.BLKODU AS "BLKODU", 
      C.CARIKODU AS "CARIKODU", 
      C.TICARI_UNVANI AS "TICARI_UNVANI", 
      C.ADI_SOYADI AS "ADI_SOYADI", 
      C.VERGI_NO AS "VERGI_NO", 
      C.TC_KIMLIK_NO AS "TC_KIMLIK_NO", 
      C.VERGI_DAIRESI AS "VERGI_DAIRESI", 
      C.CEP_TEL AS "CEP_TEL", 
      C.ILI AS "ILI", 
      C.ILCESI AS "ILCESI", 
      C.ADRESI_1 AS "ADRESI", 
      (SELECT 
          CHR.BLKODU AS "BLKODU", 
          CHR.EVRAK_NO AS "EVRAK_NO", 
          CHR.MAKBUZNO AS "MAKBUZ_NO", 
          CAST(CHR.TARIHI AS DATE) AS "TARIHI", 
          CAST(CHR.VADESI AS DATE) AS "VADESI",  
          CHR.MUH_DURUM AS "MUH_DURUM", 
          CAST(CHR.KPB_ATUT AS DECIMAL(10, 2)) AS "KPB_ATUT", 
          CAST(CHR.KPB_BTUT AS DECIMAL(10, 2)) AS "KPB_BTUT" 
       FROM CARIHR AS CHR 
       WHERE CHR.BLCRKODU = C.BLKODU 
       ORDER BY CHR.TARIHI
       FOR XML PATH('CARIHR'), TYPE) 
    FROM CARI AS C 
    WHERE C.CARIKODU = 'CR00001'
    FOR XML PATH ('CARI')
) AS NVARCHAR(MAX))

之后使用原Python代码即可获取完整字符串内容。

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