pyodbc执行变量变为@ P1 [英] pyodbc execute variable becomes @P1

问题描述

我正在做类似的事情:

# pyodbc extension
cursor.execute("select a from tbl where b=? and c=?", x, y)

-变量提供的查询中的某些值.但有时在查询中变量会被解释为 @ P1 .

-- some values in the query in provided by variables. But sometimes the variable is interpreted as @P1 in the query.

例如:

import pyodbc

ch = pyodbc.connect('DRIVER={SQL Server};SERVER=xxxx;DATABASE=xxx;Trusted_Connection=True')
cur = ch.cursor()

x = 123

cur.execute('''
CREATE TABLE table_? (
  id int IDENTITY(1,1) PRIMARY KEY,
  obj varchar(max) NOT NULL
)
''', x).commit()

这将导致一个名为 table_ @ P1 的新表(我想要 table_123 )

This results in a new table named table_@P1 (I want table_123)

另一个例子:

x = 123

cur.execute('''
CREATE TABLE table_2 (
  id int IDENTITY(1,1) PRIMARY KEY,
  obj varchar(?) NOT NULL
)
''', x).commit()

它报告错误:

ProgrammingError :("42000"，"[42000] [Microsoft] [ODBC SQL Server驱动程序] [SQL Server]'@ P1'附近的语法不正确.(102)(SQLExecDirectW))

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW)")

同样，该变量被解释为 @ P1 .

Again, the variable is interpreted as @P1.

有人知道如何解决此问题吗?任何帮助表示赞赏.谢谢-

Anyone know how to fix this? Any help's appreciated. Thanks-

推荐答案

在第一种情况下，参数替换不适用于表/列名称.这对于绝大多数(如果不是全部)数据库平台都是常见的.

In your first case, parameter substitution does not work for table/column names. This is common to the vast majority of (if not all) database platforms.

在第二种情况下，SQL Server似乎不支持DDL语句的参数替换.SQL Server ODBC驱动程序将pyodbc参数占位符(?)转换为T-SQL参数占位符( @ P1 ， @ P2 ，...)，因此传递给SQL Server的语句是

In your second case, SQL Server does not appear to support parameter substitution for DDL statements. The SQL Server ODBC driver converts the pyodbc parameter placeholders (?) to T-SQL parameter placeholders (@P1, @P2, ...) so the statement passed to SQL Server is

CREATE TABLE table_2 (id int IDENTITY(1,1) PRIMARY KEY, obj varchar(@P1) NOT NULL

具体

exec sp_prepexec @p1 output,N'@P1 int',N'CREATE TABLE table_2 (id int IDENTITY(1,1) PRIMARY KEY, obj varchar(@P1) NOT NULL',123

，当SQL Server尝试准备该语句时，它期望使用文字值，而不是参数占位符.

and when SQL Server tries to prepare that statement it expects a literal value, not a parameter placeholder.

因此，在两种情况下，您都需要使用动态SQL(字符串格式)来插入适当的值.

So, in both cases you will need to use dynamic SQL (string formatting) to insert the appropriate values.

这篇关于pyodbc执行变量变为@ P1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！