How can I input a python variable into a stored procedure?

I need to query a SQL Db using a specific value from my python script, here is what i have so far:

conn = pyodbc.connect('<SQL-CREDENTIALS-HERE>')

# Create a cursor from the connection

crsr = conn.cursor()
sql = """
DECLARE @DATA1 AS var_num;
DECLARE @DATA2 OUTPUT;

EXEC EXT_GetDATA
        @DATA1,
        @DATA2;

"""
crsr.execute(sql)

For this i get an error syaing DATA1 is an invalid data type, am i writing the sqL query right? Note that var_num is pulled earlier in the script and is not shown here.

EDIT: I have also tried the following method and still no luck:

conn = pyodbc.connect(”)

# Create a cursor from the connection

crsr = conn.cursor()
sql = """
DECLARE @DATA1 CHAR(20);
SET DATA1 = var_num;

DECLARE @DATA2 NVARCHAR;

EXEC EXT_GetDATA
        @DATA1,
        @DATA2 OUTPUT;

"""
crsr.execute(sql)

However this still doesn’t work and returns the fault:

Exception has occurred: ProgrammingError
(Invalid column name 'var_num'. (207) (SQLExecDirectW)")

can anyone tell me what i am doing wrong, i do not want to set var_num as the column name??

Answer

As illustrated in the pyodbc Wiki, something like this should work:

crsr = conn.cursor()
sql = """
DECLARE @out NVARCHAR(max);
EXEC EXT_GetDATA
        @DATA1 = ?,
        @DATA2 = @out OUTPUT;
SELECT @out AS output_value;
"""
crsr.execute(sql, var_num)  # pass `var_num` as input parameter value
the_output = crsr.fetchval()  # retrieve output parameter value

Advertisement

Answer