Torxed Torxed - 4 months ago 99
Python Question

MSSQL2008 - Pyodbc - Previous SQL was not a query

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

Answer

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