CODEWITHSUNDEEP
pythonsqlsql-serverpyodbc
ChangeMyName
ChangeMyName

Reputation: 7408

PyODBC fetch result from a simple SELECT clause

I have a SQL script like follows:

DECLARE @AGE INT = ?

, @NAME VARCHAR(20) = ?

INSERT INTO [dbo].[new_table] (AGE, NAME)

SELECT @AGE, @NAME

SELECT ID = CAST(SCOPE_IDENTITY() AS INT)

In the table, there is an IDENTITY column ID which is defined as INT. Thus the values of the ID column is increasing as new rows are inserted. And my goal is to take out the ID that just inserted.

The above code works fine in SQL.

Then I tried to run it in python, using following code:

conn = pyodbc.connect("driver={SQL Server}; server= MyServer; database= MyDatabase"; trusted_connection=true") 

cursor = conn .cursor()

SQL_command = """
                 DECLARE @AGE INT = ? 
                     ,  @NAME VARCHAR(20) = ?
 
                 INSERT INTO [dbo].[new_table] (AGE, NAME) 
                 SELECT @AGE, @NAME
 
                 SELECT ID = CAST(SCOPE_IDENTITY() AS INT)
              """

cursor.execute(SQL_command, 23, 'TOM')

result = cursor.fetchall()

However, I've got following error message:

Traceback (most recent call last):

File "C:\Users\wwang\Documents\Aptana Studio 3 Workspace\ComparablesBuilder\test.py", line 119, in 

result = cursor.fetchall()

pyodbc.ProgrammingError: No results. Previous SQL was not a query.

So, may I know why the same code cannot work in python? Is my usage of pyodbc is incorrect?

Many thanks.

Upvotes: 1

Views: 2327

Answers (1)

StuartLC
StuartLC

Reputation: 107247

It's possible that the multiple statements in your Sql Batch are being interpreted as separate result sets to the Python driver - the first row-count returning statement is the INSERT statement, which could be the culprit.

Try adding SET NOCOUNT ON; before your statements, to suppress row counts from non-queries:

 SET NOCOUNT ON;
 DECLARE @AGE INT = ? 
      ,  @NAME VARCHAR(20) = ?

 ... 
 SELECT ID = CAST(SCOPE_IDENTITY() AS INT);

Edit

IIRC, some drivers are also dependent on Sql Server's ROW COUNT to parse result sets correctly. So if the above fails, you might also try:

  SET NOCOUNT ON;
  DECLARE ...;
  INSERT ...;

  SET NOCOUNT OFF; -- i.e. Turn back on again so the 1 row count can be returned.
  SELECT CAST(SCOPE_IDENTITY() AS INT) AS ID;

Upvotes: 1

Related Questions