Python 3.6 pyodbc to SQL How to execute SP

Question

I've created a Python script to execute an SP using my cursor with connection odbc. I've tried everything I found but I can not execute the desired procedure. My code below:

import pyodbc
conn = pyodbc.connect( 'DRIVER={SQL Server};'
                           'SERVER=XXXXXX;'
                           'DATABASE=XXX;UID=XXXX;'
                           'PWD=XXX')
cursor = conn.cursor()
cmd_prod_executesp = 'EXECUTE DC_SAS_EvaluationUpdate'
cursor.execute(cmd_prod_executesp)
conn.close()

I have tried:

cmd_prod_executesp = '{call DC_SAS_EvaluationUpdate}'
cmd_prod_executesp = 'exec DC_SAS_EvaluationUpdate'
cmd_prod_executesp = '{CALL DC_SAS_EvaluationUpdate}'

I appreciate your help

thanks so much

Answer 1

Thanks so much everyone for your comments. Finally, in a quick comment from @GordThompson I applied the changes below and that worked.

import pyodbc

conn = pyodbc.connect( 'DRIVER={SQL Server};'
                       'SERVER=XXXX;'
                       'DATABASE=XX;UID=XXXX;'
                       'PWD=XXXX')
cursor = conn.cursor()

cmd_prod_executesp = """EXEC DC_SAS_EvaluationUpdate """
conn.autocommit = True
cursor.execute(cmd_prod_executesp)

conn.close()

Answer 2

Here are two examples that work for me. I'll show with parameters, because I'm guessing you'll need to know how to do that as well.

With named parameters:

cursor.execute('EXEC usp_get_user_data @name = ?, @username = ?', 'tim', 'flipperpa')

With positional parameters:

cursor.execute('EXEC usp_get_user_data ?, ?', None, 'flipperpa')

Good luck!

Answer 3

I think your code looking fine as you did follow the guideline from pyodbc

//SQL Server format

cursor.execute("exec sp_dosomething(123, 'abc')")

//ODBC format

cursor.execute("{call sp_dosomething(123, 'abc')}")

From your comment you said you see the message

`Process finished with exit code 0'

That means that everything worked ok.If any exception/error happened in your program your program should generate an exit code with non-zero argument.

So I think your program worked fine and the SP did get executed. However, I suspect you don't see the changes take effect in your DB could be because you didn't commit() your changes after you execute your sp and before you close your connection to your DB.

so add conn.commit() like this:

cursor.execute(cmd_prod_executesp)
conn.commit()

EDIT: try to use the newer driver for MsSQL as well

 conn = pyodbc.connect(driver = '{SQL Server Native Client 10.0}', server = 'xxxxxx',database = 'xxxx', uid = 'xx', pwd = 'xxxx')