python - MSSQL2008 - Pyodbc - Previous SQL was not a query

Question

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));")

It fails with:

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.

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

question from:https://stackoverflow.com/questions/7753830/mssql2008-pyodbc-previous-sql-was-not-a-query

Answer 1

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.

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

It'll work just fine :)

The Python code :

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

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

The 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