使用pyodbc向Azure SQL数据仓库插入8K+字符字符串遇阻

问题场景 #

在Python Notebook中使用pyodbc向Azure Synapse Analytics SQL池（原SQL数据仓库）插入长度超过8000字符的字符串时失败，已更新至最新ODBC驱动，截断字符串至4000字符则可正常插入。

已尝试方案及报错 #

1. 直接插入 #

代码：

import pyodbc
connectionString = "Driver={ODBC Driver 18 for SQL Server};Server=myServerInfo;Database=myDB;Uid=user;Pwd={pw};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;LongAsMax=1;"
cnxn = pyodbc.connect(connectionString)

cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setencoding(encoding='utf-8')

cursor = cnxn.cursor()

sql = "insert into myTable (id, veryLongText) values (?, ?)" 
params = (1, 'a' * 8001) 

cursor.execute(sql, params) 

报错：

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]104220;Cannot find data type 'text'. (100000) (SQLExecDirectW)")

2. 强制转换为varchar(max) #

代码：

sql = "insert into myTable (id, veryLongText) values (?, cast(? as varchar(max)))" 

报错：

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Insert values statement can contain only constant literal values or variable references. (104334) (SQLExecDirectW)')

3. 使用pyodbc.Binary转换 #

代码：

# 尝试用pyodbc.Binary包装长字符串
params = (1, pyodbc.Binary(('a' * 8001).encode('utf-8')))
cursor.execute(sql, params)

报错：

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Unsupported data type error. Statement references a data type that is unsupported in Parallel Data Warehouse, or there is an expression that yields an unsupported data type. Modify the statement and re-execute it. (104051) (SQLExecDirectW)')

4. 使用存储过程 #

存储过程代码：

CREATE PROCEDURE InsertLongText
    @id int,
    @veryLongText varchar(max)
AS
BEGIN
    INSERT INTO myTable (id, veryLongText) VALUES (@id, @veryLongText)
END

Python调用代码：

sql = "EXEC InsertLongText ?, ?"
params = (1, 'a' * 8001)
cursor.execute(sql, params)

报错信息与直接插入一致。

解决方案 #

关键调整点 #

1. 确认表字段类型：确保veryLongText字段类型为varchar(max)或nvarchar(max)（Synapse SQL池不支持text类型）。
2. 修改连接字符串：添加UseFMTONLY=No参数，避免ODBC驱动错误推断数据类型为TEXT。
3. 显式指定参数类型：通过setinputsizes或直接在execute中声明参数类型，强制驱动将长字符串识别为varchar(max)（长度设为0表示max）。

修正后的代码示例 #

方案一：使用setinputsizes指定参数类型

import pyodbc
# 连接字符串添加UseFMTONLY=No
connectionString = "Driver={ODBC Driver 18 for SQL Server};Server=myServerInfo;Database=myDB;Uid=user;Pwd={pw};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;LongAsMax=1;UseFMTONLY=No;"
cnxn = pyodbc.connect(connectionString)

cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setencoding(encoding='utf-8')

cursor = cnxn.cursor()

sql = "insert into myTable (id, veryLongText) values (?, ?)" 
params = (1, 'a' * 8001) 

# 显式设置输入参数类型：第一个为整数，第二个为VARCHAR(max)（长度0表示max）
cursor.setinputsizes([(pyodbc.SQL_INTEGER, 0, 0), (pyodbc.SQL_VARCHAR, 0, 0)])
cursor.execute(sql, params)

# 提交事务
cnxn.commit()

方案二：在execute中直接指定参数类型

import pyodbc
connectionString = "Driver={ODBC Driver 18 for SQL Server};Server=myServerInfo;Database=myDB;Uid=user;Pwd={pw};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;LongAsMax=1;UseFMTONLY=No;"
cnxn = pyodbc.connect(connectionString)

cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setencoding(encoding='utf-8')

cursor = cnxn.cursor()

sql = "insert into myTable (id, veryLongText) values (?, ?)" 
# 格式：(参数值, SQL类型, 参数值, 长度)，长度0表示max
cursor.execute(sql, (1, pyodbc.SQL_INTEGER, 'a' * 8001, pyodbc.SQL_VARCHAR, 0)) 

cnxn.commit()

原理说明 #

Azure Synapse SQL池（原Parallel Data Warehouse）对ODBC参数的处理逻辑与普通SQL Server不同：

• UseFMTONLY=No参数会禁用驱动的元数据预检查，避免错误将长字符串映射到已废弃的TEXT类型。
• 显式指定参数类型为SQL_VARCHAR且长度为0，会告诉驱动将该参数识别为varchar(max)，符合Synapse SQL池的支持类型。

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