为什么我的查询在参数化后会中断？

我有 2 张桌子 -Sales and Product. Sales可以将产品存储为Idn or Name（传统设计）和Type列指定实际type与之相关。Product等是连接的子集表into这个表来获取真实的数据。 （在这个例子中，Product是一个存储的表Idn来证明这个问题。）

Sales

|------------|--------------------|----------------|
|    Idn     |  Product Idn/Name  |     Type       |
|------------|--------------------|----------------|
|     1      |          1         |     Number     |
|------------|--------------------|----- ----------|
|     2      |       Colgate      |      Word      |
|------------|--------------------|----------------|

Product (Idn)

|------------|------------------|
|    Idn     |    Some Info     |
|------------|------------------|
|     1      |       ...        |
|------------|------------------|

Normally, you should not join these tables on Product Idn because it has mixed data; but if you select the rows where LHS matches RHS, it works fine ⁽¹⁾. For example, if Product is a table that stores Idns, the following query fails:

SELECT * from sales JOIN product on sales.pid = product.idn

但以下查询有效：

SELECT * from sales JOIN product on sales.pid = product.idn WHERE type = 'Number'

这在 Python 2 + SQLAlchemy + PyODBC 中也按预期工作。但是，当我在 Python 3 + SQLAlchemy + PyODBC 中尝试此操作时，它会给我一个数据类型转换错误，并且仅当查询为参数化的!

现在如果我成功了u'number'在Python 2中，它也在那里中断；和b'number'在 Python 3 中工作！我猜测 Unicode 转换存在一些问题。是否试图guess编码并做错了什么？我可以通过更明确地解决这个问题吗？

收到的错误是：

Traceback (most recent call last):
  File "reproduce.py", line 59, in <module>
    print(cursor.execute(select_parametrized, ('number', 1)).fetchall())
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error converting data type varchar to numeric. (8114) (SQLFetch)

这里可能是什么问题，有没有什么好的方法可以回避这个问题而不做类似的事情convert（因为它在以前的版本中有效）？

这是一个可用于重现此问题且没有副作用的查询（需要SQLAlchemy and PyODBC):

import sqlalchemy
import sqlalchemy.orm

create_tables = """
CREATE TABLE products(
    idn NUMERIC(9) PRIMARY KEY
);
CREATE TABLE sales(
    idn NUMERIC(9) PRIMARY KEY,
    pid VARCHAR(50) NOT NULL,
    type VARCHAR(10) NOT NULL
);
"""

check_tables_exist = """   
SELECT * FROM products;
SELECT * FROM sales;
"""

insert_values = """
INSERT INTO products (idn) values (1);
INSERT INTO sales (idn, pid, type) values (1, 1, 'number');
INSERT INTO sales (idn, pid, type) values (2, 'Colgate', 'word');
"""

select_adhoc = """
SELECT * FROM products
JOIN sales ON products.idn = sales.pid
AND sales.type = 'number'
WHERE products.idn in (1);
"""

select_parametrized = """
SELECT * FROM products
JOIN sales ON products.idn = sales.pid
AND sales.type = ?
WHERE products.idn in (?);
"""

delete_tables = """
DROP TABLE products;
DROP TABLE sales;
"""

engine = sqlalchemy.create_engine('mssql+pyodbc://user:password@dsn')
connection = engine.connect()
cursor = engine.raw_connection().cursor()

Session = sqlalchemy.orm.sessionmaker(bind=connection)
session = Session()

session.execute(create_tables)

try:
    session.execute(check_tables_exist)
    session.execute(insert_values)
    session.commit()
    print(cursor.execute(select_adhoc).fetchall())
    print(cursor.execute(select_parametrized, ('number', 1)).fetchall())
finally:
    session.execute(delete_tables)
    session.commit()

1.这是一个错误的假设。它的工作是偶然的 - SQL 的执行计划优先考虑这个条件，如所解释的here https://dba.stackexchange.com/questions/267843/why-does-sql-server-say-it-cant-convert-varchar-to-numeric/。当它变成NVARCHAR.

SQLAlchemy 使用您的非参数化查询生成此 SQL 脚本（select_adhoc):

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 to 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 (?);
"""