pymssql执行存储过程但没有返回结果 [英] pymssql executes stored procedure but returns no results

问题描述

我正在尝试在 SQL 服务器上执行存储过程并使用 python 保存结果——我决定使用 pymssql，因为它似乎是最简单的解决方案.

I am trying to execute a stored procedure on a SQL server and save the results using python -- I decided to use pymssql because it seemed like the simplest solution.

print pymssql.__version__

server = 'MY\SERVER'
user = 'user'
password = 'password'
database = 'db'
proc = 'checkin'

with pymssql.connect(server, user, password, database) as conn:
    with conn.cursor() as cursor:

        cursor.callproc(proc, (eha, ip, url, alias, location, rotation))
        conn.commit()

        f = open('/var/wwwdata/locations.txt', 'w')
        for row in cursor:
            print(row['Alias'])
            f.write(row['Alias'] + '\n')
        f.close()

SQL 查询执行一些插入/更新并以

The SQL Query performs some insert/update and ends with

SELECT Alias FROM MyTable

从 SSMS 运行 SP 可以正常工作，但是从 Python 运行 SP 会执行插入/更新功能，但不会返回任何结果.

Running the SP from SSMS works correctly, however running the SP from Python performs the insert/update functionality however returns no results.

根据 pymssql 文档，这是一个已知问题.但是，我找不到有效的解决方案.

According to pymssql documentation, this is a known problem. However, I cannot find a solution that works.

我尝试了一些在网上找到的不同建议，包括

I tried a few different suggestions I found around the web, including

• 检查我的 pymssql 版本 (2.1.1)
• 使用 dict=true
声明游标
• 在cursor.commit()
之后使用cursor.nextset()
• 使用 cursor.fetchall() 或 cursor.fetchone() 获取结果，两者都会导致类似的异常:

• Checked my pymssql version (2.1.1)
• Declare cursor with dict=true
• Use cursor.nextset() after cursor.commit()
• Fetch results using cursor.fetchall() or cursor.fetchone(), both of which result in similar exceptions:

OperationalError:语句未执行或执行的语句没有结果集

OperationalError: Statement not executed or executed statement has no resultset

有谁知道解决这个特定问题的方法吗?或者是否有更稳定的 Python 与 SQL 服务器接口的解决方案(尤其是调用存储过程)?我想我也应该问一下，我这样做是完全错误的吗?

Does anyone know a fix for this particular problem? Or perhaps is there a more stable solution for python interfacing with SQL server (especially for calling stored procedures)? I guess I should also ask, am I going about this entirely wrong?

还认为可能值得注意的是:操作系统是在 Raspberry Pi 2 Model B 上运行的 Raspbian

Also thought it might be worth noting: OS is Raspbian running on Raspberry Pi 2 Model B

推荐答案

我已经设法解决了这个问题.调用 conn.commit() 会使游标丢失结果.我可能误读了一些 pymssql 文档并错误地添加了该行 - 没有它代码也能完美运行.

I have managed to work out the problem. Calling conn.commit() was making the cursor lose its results. I probably misread some of the pymssql documentation and added that line by mistake - code works perfect without it.

EDIT:我注意到在我做了这个改变之后，存储过程会返回结果但是过程的插入/更新部分没有保存.现在我很清楚 conn.commit() 在做什么.如果您的存储过程返回结果并对数据库进行了更改，则需要在获取它们后调用 conn.commit().

EDIT: I noticed after I made this change, the stored procedure would return the results however the insert/update parts of the procedure were not saving. Now it's clear to me what conn.commit() was doing. If your stored procedure returns results AND makes changes to the database, you need to call conn.commit() after you fetch them.

server = 'MY\SERVER'
user = 'user'
password = 'password'
database = 'db'
proc = 'checkin'

with pymssql.connect(server, user, password, database) as conn:
    with conn.cursor() as cursor:
        cursor.callproc(proc, (eha, ip, url, alias, location, rotation))
        cursor.nextset()
        results = cursor.fetchall()
        conn.commit()
        f = open('/var/wwwdata/locations.txt', 'w')
        for result in results:
            print result[0]
            f.write(result[0])
        f.close()

这篇关于pymssql执行存储过程但没有返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！