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

@OLIVER.KOO not buddy is just a simple execute but it is driving me crazy!! — Andres Urrego Angel, Jul 20 '17 at 19:28
try putting the sql directly in the `execute()` like this `cursor.execute(" exec DC_SAS_EvaluationUpdate")` also do you get any errro messages? — OLIVER.KOO, Jul 20 '17 at 19:34
Possible duplicate of [Python - pyodbc call stored procedure with parameter name](https://stackoverflow.com/questions/30964608/python-pyodbc-call-stored-procedure-with-parameter-name) — OLIVER.KOO, Jul 20 '17 at 19:43
any error at all just a happy message 'Process finished with exit code 0' but when i go to my DB nothing was executed :( — Andres Urrego Angel, Jul 20 '17 at 19:46
What is your way of checking what got executed in your DB? I am wondering maybe if it did get execute but maybe not the same DB you are checking. that happened to me before. — OLIVER.KOO, Jul 20 '17 at 19:48
The most weird thing guys is that I have already performed Selects and inserts successfully in the same server, it's just the execution for SP that it doesnt work — Andres Urrego Angel, Jul 20 '17 at 19:50
but if you execute the sp directly in the database it works as expected? — OLIVER.KOO, Jul 20 '17 at 19:57
@OLIVER.KOO Yes buddy when I go to DB and execute manually that works thats so so weird. I will create my scripts in a GitHub repository to show you guys more details about my script — Andres Urrego Angel, Jul 20 '17 at 20:00
ok just out of curiosity can you change your driver to the newer one for MsSQL 2008. also is your server using `local`? if not try that too . so do `pyodbc.connect(driver = '{SQL Server}', server = '(local)', database = 'xxx', uid = 'xx', pwd = 'xxx')` and run the program again. and see if this affects your DB — OLIVER.KOO, Jul 20 '17 at 20:06
I was wondering maybe you don't have the correct driver. But another reason could be you didn't commit after you execute your sp. so put `conn.commit()` after `cursor.execute()` — OLIVER.KOO, Jul 20 '17 at 20:11
You could try doing `conn.autocommit = True` before calling your SP to see if that helps any. — Gord Thompson, Jul 20 '17 at 20:55
Buddy figured it out doing conn.autocommit = True. Even if I did a commit within my SP thanks buddy. How can reach you on LinkedIn or something like that. thanks @GordThompson — Andres Urrego Angel, Jul 20 '17 at 21:26
does adding `conn.commit()` not work for you? I would have thought that does the same trick as setting autocommit to true. — OLIVER.KOO, Jul 20 '17 at 21:47

score 9 · Accepted Answer · edited Mar 12 '23 at 10:00

9

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

edited Mar 12 '23 at 10:00

RF1991

2,037
4
8
17

answered Jul 20 '17 at 21:29

Andres Urrego Angel

1,842
7
29
55

you could also set auto commit to true when you create the connection. It is seen more common that way. `conn = pyodbc.connect( 'DRIVER={SQL Server};' 'SERVER=XXXX;' 'DATABASE=XX;UID=XXXX;' 'PWD=XXXX;' 'autocommit=True;')` remember In pyodbc.connect() autocommit is disabled by default – OLIVER.KOO Jul 20 '17 at 21:44

score 1 · Answer 2 · answered Jul 20 '17 at 19:40

1

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!

answered Jul 20 '17 at 19:40

FlipperPA

13,607
4
39
71

I have tried even CALL and it doesnt work :( OMG. The most weird thing guys is that I have already performed Selects and inserts successfully in the same server, it's just the execution for SP that it doesnt work – Andres Urrego Angel Jul 20 '17 at 19:51
IIRC, `CALL` is for MySQL. Can you try creating a really simple stored procedure that just returns something simple like `SELECT 'HELLO WORLD'` and try executing that? – FlipperPA Jul 20 '17 at 19:53
buddy when I go to DB and execute manually that works thats so so weird. I will create my scripts in a GitHub repository to show you guys more details about my script – Andres Urrego Angel Jul 20 '17 at 20:00
yep you want to use `exec`? since `exec` is for SQL server format `cursor.execute("exec sp_dosomething(123, 'abc')")` and `call` is for ODBC format `cursor.execute("{call sp_dosomething(123, 'abc')}") ` – OLIVER.KOO Jul 20 '17 at 20:00
Are you connecting through SSMS as the same user you're connecting from Python as? – FlipperPA Jul 20 '17 at 20:28
`{call ...}` (within curly brackets) is ODBC standard syntax, so it should work fine. (It does for me.) – Gord Thompson Jul 20 '17 at 20:56

OLIVER.KOO · Answer 3 · 2017-07-20T20:47:21.687

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

Python 3.6 pyodbc to SQL How to execute SP

3 Answers3