Using SQL Server Stored Procedures from Python (Pyodbc)

Using SQL Server stored procedures from Python (pyodbc)

From the pyodbc documentation

To call a stored procedure right now, pass the call to the execute method using either a format your database recognizes or using the ODBC call escape format. (The ODBC driver will then reformat the call for you to match the given database.)

For SQL Server you would use something like this:

# SQL Server format
cursor.execute("exec sp_dosomething(123, 'abc')")

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

So to call your procedure

id_ = 'test' 
pw = '12345' 
depart = 'none' 
class_ = 'GM' 
name = 'name' 
birthday = 'None' 
grade = 3 
subgrade = 2 

sql = 'exec [my_database].[dbo].[my_table](?, ?, ?, ?, ?, ?, ?, ?)'
values = (id_, pw, depart, class_, name, birthday, grade, subgrade)

cursor.execute(sql, (values))

Python 3.6 pyodbc to SQL How to execute SP

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

Having trouble calling a stored procedure in SQL Server from Python pyodbc

Setting NOCOUNT ON is not always enough. To be sure, loop until pyodbc thinks there are no more resultsets, or you may cancel the execution before it's complete. eg

cursor.execute(sql)
while cursor.nextset():
    pass 
cursor.close()

Executing stored procedures in SQL Server using Python client

Calling .execute() directly on an Engine object is an outdated usage pattern and will emit deprecation warnings starting with SQLAlchemy version 1.4. These days the preferred approach is to use a context manager (with block) that uses engine.begin():

import sqlalchemy as sa

# …

with engine.begin() as conn:  # transaction starts here
    conn.execute(sa.text("EXEC [dbo].[appDoThis] 'MYDB';"))

# On exiting the `with` block the transaction will automatically be committed
#   if no errors have occurred. If an error has occurred the transaction will
#   automatically be rolled back.

Notes:

1. When passing an SQL command string it should be wrapped in a SQLAlchemy text() object.
2. SQL Server stored procedures (and anonymous code blocks) should begin with SET NOCOUNT ON; in the overwhelming majority of cases. Failure to do so can result in legitimate results or errors getting "stuck behind" any row counts that may have been emitted by DML statements like INSERT, UPDATE, or DELETE.

Python: Pyodbc execute stored procedure with parameters

I found that the problem was caused by not having autocommit turned on. Pyodbc has autocommit set to False by default.

To enable autocommit, you can declare the connection like:

cnxn = pyodbc.connect(driver="<driver>", server="<server>", database="<database>", uid="<username>", pwd="<password>", autocommit=True)

---

Related Topics