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

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 

Answer 2

Using the "SET NOCOUNT ON" value at the top of the script will not always be sufficient to solve the problem.

In my case, it was also necessary to remove this line:

Use DatabaseName; 

Database was SQL Server 2012, Python 3.7, SQL Alchemy 1.3.8

Hope this helps somebody.