MSSQL2008 - Pyodbc - 以前的 SQL 不是查询 [英] MSSQL2008 - Pyodbc - Previous SQL was not a query

问题描述

我无法弄清楚以下代码有什么问题，语法没问题(用 SQL Management Studio 检查)，我可以访问我应该这样也可以工作..但是出于某种原因，一旦我尝试通过 PyODBC 创建一个表，它就会停止工作.

I can't figure out what's wrong with the following code, The syntax IS ok (checked with SQL Management Studio), i have access as i should so that works too.. but for some reason as soon as i try to create a table via PyODBC then it stops working.

import pyodbc

def SQL(QUERY, target = '...', DB = '...'):
    cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + target + DB+';UID=user;PWD=pass')
    cursor = cnxn.cursor()
    cursor.execute(QUERY)
    cpn = []

    for row in cursor:
        cpn.append(row)
    return cpn

print SQL("CREATE TABLE dbo.Approvals (ID SMALLINT NOT NULL IDENTITY PRIMARY KEY, HostName char(120));")

它失败了:

Traceback (most recent call last):
  File "test_sql.py", line 25, in <module>
    print SQL("CREATE TABLE dbo.Approvals (ID SMALLINT NOT NULL IDENTITY PRIMARY KEY, HostName char(120));")
  File "test_sql.py", line 20, in SQL
    for row in cursor:
pyodbc.ProgrammingError: No results.  Previous SQL was not a query.

有人知道这是为什么吗?我安装了SQL Server"驱动程序(默认)，在 Windows 2008 SQL Server 环境(不是快速数据库)上运行 Windows 7.

Anyone have any idea to why this is? I got a "SQL Server" driver installed (it's default), running Windows 7 against a Windows 2008 SQL Server environment (Not a express database).

推荐答案

以防万一一些孤独的网络游民遇到这个问题，Torxed 的解决方案对我不起作用.但以下对我有用.

Just in case some lonely net nomad comes across this issue, the solution by Torxed didn't work for me. But the following worked for me.

我正在调用一个 SP，它将一些值插入到一个表中，然后返回一些数据.只需将以下内容添加到 SP 中:

I was calling an SP which inserts some values into a table and then returns some data back. Just add the following to the SP :

SET NOCOUNT ON

它会工作得很好:)

Python 代码:

    query = "exec dbo.get_process_id " + str(provider_id) + ", 0"
    cursor.execute(query)

    row = cursor.fetchone()
    process_id = row[0]

SP:

USE [DBNAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GET_PROCESS_ID](
    @PROVIDER_ID INT,
    @PROCESS_ID INT OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO processes(provider_id) values(@PROVIDER_ID)
    SET @PROCESS_ID= SCOPE_IDENTITY()
    SELECT @PROCESS_ID AS PROCESS_ID
END

这篇关于MSSQL2008 - Pyodbc - 以前的 SQL 不是查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！