CODEWITHSUNDEEP
pythonsqlsql-serverpyodbc
EcSync
EcSync

Reputation: 860

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??

Upvotes: 0

Views: 1488

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

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

Upvotes: 1

Related Questions