我有2张桌子-Sales和Product。Sales可以将产品存储为 Idn 或 Name (旧式设计),并且该Type列指定与之关联的实际 类型 。Product等等是一个子表,该子表被 合并到 该表中以获取真实数据。(在此示例中,Product是一个表,用于存储 Idn 来演示问题。)
Sales
Product
Type
Sales |------------|--------------------|----------------| | Idn | Product Idn/Name | Type | |------------|--------------------|----------------| | 1 | 1 | Number | |------------|--------------------|----- ----------| | 2 | Colgate | Word | |------------|--------------------|----------------| Product (Idn) |------------|------------------| | Idn | Some Info | |------------|------------------| | 1 | ... | |------------|------------------|
通常,您不应将这些表连接Product Idn在一起,因为它包含混合数据。但是,如果您选择LHS与RHS匹配的行,则可以正常工作 (1) 。例如,如果Product是一个存储 Idn 的表,则以下查询将失败:
Product Idn
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转换存在一些问题。它是在尝试 猜测 编码并做错了什么吗?我可以更明确地解决此问题吗?
u'number'
b'number'
收到的错误是:
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(因为它在以前的版本中有效)?
convert
这是一个可用于重现此问题而没有副作用(需要SQLAlchemy和PyODBC)的查询:
SQLAlchemy
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的执行计划优先考虑这种情况作为解释在这里。它变成时并没有这样做NVARCHAR。
NVARCHAR
SQLAlchemy使用您的非参数化查询(select_adhoc)生成此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中进行了检查。)
select_parametrized
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?
@P1
varchar
sales.type
如果您像这样更改查询,它将可以正常工作;或您需要将的类型更改sales.type为nvarchar。
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 (?); """