sqlAlchemy使用您的非参数化查询(select_adhoc
)生成此sql脚本:
SELECT * FROM products
JOIN sales ON products.idn = sales.pid
AND sales.type = 'number'
WHERE products.idn in (1);
但是使用参数化查询(select_parametrized
),它会生成以下内容:(我从sql Server Profiler中进行了检查。)
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 nvarchar(12),@P2 int',N'
SELECT * FROM products
INNER JOIN sales ON products.idn = sales.pid
AND sales.type = @P1
WHERE products.idn in (@P2);
',N'number',1
select @p1
如果您在sql Server上尝试此操作,则会得到相同的异常:
消息8114,级别16,状态5,第32行将数据类型varchar转换为数值时出错。
问题出在@P1
参数声明上-它隐式转换为varchar
(的类型sales.type
),从而导致此问题。可能是Python 2生成了varchar?
如果您像这样更改查询,它将可以正常工作;或您需要将的类型更改sales.type
为nvarchar
。
select_parametrized = """
SELECT * FROM products
INNER JOIN sales ON products.idn = sales.pid
AND sales.type = CAST(? AS VARCHAR(50))
WHERE products.idn in (?);
"""