时间戳

首页 > 解决方案 > pyodbc execute variable becomes @P1

python - pyodbc execute variable becomes @P1

问题描述

Hi I'm doing something like:

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

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

For example:

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()

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

Another example:

x = 123

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

it reports error:

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

Again, the variable is interpreted as @P1.

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

标签： pythonsql-serverpyodbcexecute

解决方案

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.

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

specifically

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

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

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

推荐阅读