pyodbc execute variable becomes @P1

Question

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-

Answer 1

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.

Answer 2

There is a way to do this sort of thing. What you need to do is dynamically build the command (ideally as a nvarchar( MAX), not varchar( MAX)) string variable and pass that variable to the cur.execute() - or any other - command. Modifying your first example accordingly:

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

x = 123

SQL_Commands = 'CREATE TABLE table_' +  str( x )  +  '''    
(
  id int IDENTITY(1,1) PRIMARY KEY,
  obj varchar(max) NOT NULL
) '
'''

cur.execute( SQL_Commands ).commit()

BTW, you shouldn't try to do everything in one line, if only to avoid problems like this one. I'd also suggest looking into adding "autocommit=True" to your connect string, that way you wouldn't have to append .commit() to cur.execute().