pyodbc 失败而没有错误 [英] pyodbc fails without error

问题描述

如果有人能指出我正确的方向，我会很感兴趣.

Would be interested if someone can point me in the right direction.

我有一个很长的存储过程(其中还包含对其他存储过程的调用)，用于更新各种表.

I have one very long stored proc (that also contains calls to other stored procs within it) that updates various tables.

如果我在管理工作室中运行存储过程，它运行良好.如果我从 pyodbc 调用它，则:

If I run the stored proc in management studio it runs fine. If I call it from pyodbc then:

1. 它的运行时间与通常相同
2. 我没有发现任何错误
3. 它要更新的表都没有更新

我一直运行 pyodbc 来执行存储过程并且没有任何问题 - 我知道我的连接或调用没有任何问题，就好像我将较短的存储过程替换到 Python 代码中的同一个地方，它可以正常工作.

I run pyodbc to execute stored procedures all the time and have no problems - I know there is nothing wrong with my connections or calls as if I substitute a shorter stored proc into the python code in the same place it works fine.

存储过程确实会生成一些警告:空值被聚合或其他 SET 操作消除"消息，我认为这些可能会导致问题，但每当我尝试 SET ANSI_WARNINGS { ON |OFF } 无论是在存储过程中还是在存储过程之外我都得到了一个 pyodbc.ProgrammingError

The stored proc does generate a few "Warning: Null value is eliminated by an aggregate or other SET operation" messages, I thought that these might be causing problems but whenever I try to SET ANSI_WARNINGS { ON | OFF } either inside the stored proc or outside the stored proc I got a pyodbc.ProgrammingError

对问题有任何猜测吗?

Python 3.4 (have the same problem in 2.7), MSSQL, Windows 7

更新:

import pyodbc as p

def getconn():
    server='insertsqlservername'
    dbase='insertdbasename'
    connStr=('Driver={SQL Server};SERVER=' +
              server + ';DATABASE=' + dbase + ';' +
              'trusted=1')
    conn = p.connect(connStr)
    return conn

def runSQL():
    conn=getconn()
    cursor=conn.cursor()

    try:
        cursor.execute('exec InsertStoredProcName')
        conn.commit()
    except:
        print('sys.exc_info()[0])

    cursor.close()
    conn.close()

为了 100% 解决这个问题，似乎有两个组成部分:

Just to be 100% on the problem there seem to be two components:

• Python 代码:如果我使用相同的 Python 代码但将存储的 proc 更改为不同的代码，则 Python 代码有效
• SQL 代码:问题"存储过程在 T-SQL 中有效，但在 pyodbc 中运行时无效

这似乎是两者的结合.

推荐答案

你能包括你的 pyodbc 连接字符串吗?听起来您在连接时没有设置自动提交，这会导致您所做的更改在连接关闭时回滚.如果是这种情况，您有两种选择.首先，您可以在连接时设置自动提交:

Can you include your pyodbc connection string? It sounds like you're not setting autocommit when you connect, which causes the changes you make to be rolled back when the connection is closed. You have two options, if this is the case. First, you can set autocommit to be on when you connect:

conn = pyodbc.connect(connection_str, autocommit=True)

或者，在您关闭连接之前:

Or, before you close the connection:

conn.commit()

这篇关于pyodbc 失败而没有错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！