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
– Andres Urrego Angel, Commented Jul 20, 2017 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
– OLIVER.KOO, Commented Jul 20, 2017 at 19:34
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
– OLIVER.KOO, Commented Jul 20, 2017 at 20:11
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
– Andres Urrego Angel, Commented Jul 20, 2017 at 21:26

RF1991 · Accepted Answer · 2023-03-12 10:00:35Z

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, 2023 at 10:00

RF1991

2,2634 gold badges12 silver badges20 bronze badges

answered Jul 20, 2017 at 21:29

Andres Urrego Angel

1,9628 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OLIVER.KOO Over a year ago

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

FlipperPA · Accepted Answer · 2017-07-20 19:40:56Z

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, 2017 at 19:40

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

6 Comments

Andres Urrego Angel Over a year ago

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

FlipperPA Over a year ago

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?

Andres Urrego Angel Over a year ago

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

OLIVER.KOO Over a year ago

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

FlipperPA Over a year ago

Are you connecting through SSMS as the same user you're connecting from Python as?

|

OLIVER.KOO · Accepted Answer · 2017-07-20 20:47:21Z

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

Collectives™ on Stack Overflow

Python 3.6 pyodbc to SQL How to execute SP

3 Answers 3

1 Comment

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related