从 pyodbc 调用过程时出错 [英] Error when calling a procedure from pyodbc

问题描述

这是我在这里的第一个问题.所以，如果重复或格式关闭，我很抱歉.我搜索了其他问题，错误很常见，但出现在多种情况下.

This is my first question here. So, I am sorry if it is repeated or the formatting is off. I searched through other questions and the error is common but appears on multiple situations.

我有一个非常简单的python代码，我想在其中从pyodbc在MSSQL中执行一个过程.

I have a very simple python code where I want to execute a procedure in MSSQL from pyodbc.

import pyodbc
conn = pyodbc.connect(r'DSN=myDSN')
cursor = conn.cursor()
query = r'{call myproc}'
cursor.execute(query)

在阅读了 ODBC 使用 call 在 MSSQL 中执行过程后，我使用 call 而不是 exec.

I am using call instead of exec after reading that ODBC uses call for executing procedures in MSSQL.

我得到的错误如下:

Traceback (most recent call last):
File "myscript.py", line 26, in <module>
cursor.execute(query)
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The current transaction has aborted, and any pending changes have been rolled back. Cause: A transaction in a rollback-only state was not explicitly rolled back before a DDL, DML or SELECT statement.  (111233) (SQLExecDirectW)')

感谢您的帮助

推荐答案

以防有人遇到同样的问题.我能够找出问题所在.当您打开与 DSN 的连接时，自动提交设置为 False.出于某种原因，这应该是 True 代码才能工作(这在很大程度上取决于我在 MSSQL 上所做的事情).

In case someone is having the same issue. I was able to find out what the problems was. When you open a connection with DSN, the autocommit is set to False. For some reason, this should be True for the code to work (this depends largely on what I was doing on MSSQL).

import pyodbc
conn = pyodbc.connect(r'DSN=myDSN', autocommit=True)
cursor = conn.cursor()
query = r'{call myproc}'
cursor.execute(query)

这运行良好！

这篇关于从 pyodbc 调用过程时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！