1

I have gone through:

However none of them have resolved the issue.

The snippet from my db.py file is as follows:

result = cursor.execute(self.sql,self.params)
if result is None:
    self.data = []
else:
    self.data = [dict(zip([key[0] for key in cursor.description], row)) for row in result.fetchall()]
cnxn.close()
return self.data

This works for every SQL and stored procedure I have thrown at it except for this one

seq = request.form['seq']
s = 'EXEC sp_add ?, ?'
p = (udf.get_username(), int(seq))
l = Conn.testing(db="testingDatabase",sql=s,params=p)

I get the error:

Previous SQL was not a query

The SQL:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE sp_add
    @user nvarchar(50),
    @seq int
AS
BEGIN
    SET NOCOUNT ON;

    insert into tblName (userCol,seqCol) VALUES (@user,@seq)
END
GO

The stored procedure runs and the row gets inserted but the error shows up.

What I did instead was:

result = cursor.execute(self.sql,self.params)
cnxn.close()
return str(result)

This returns:

EXEC sp_add ?, ?

Why does it return that? Why does it return the statement I just passed to it?

In my SP, if I tag on a SELECT statement then the issue goes away.

Any suggestions other than the hack just mentioned?

  • 2
    Aside - consider avoiding stored procedures in MSSQL starting with `sp_*` which signify **s**ystem **p**rocedures and even has [performance issues](https://stackoverflow.com/q/42640852/1422451). – Parfait Mar 04 '22 at 14:42
  • The error is telling you the problem, your procedure does not return a results set, yet you're trying to treat it like it does. – Thom A Mar 04 '22 at 14:43
  • 1
    Doesn't `if result is None:` take care of that? Why is it bringing back the query that I passed to it? Seems odd default behaviour. –  Mar 04 '22 at 14:46
  • 1
    Thank you @Parfait - I am actually going to rename them after I did this side first i.e. Flask. –  Mar 04 '22 at 14:47
  • 1
    @Zhorov this was just some dummy SQL as the actual statement is much longer. Have amended the example. –  Mar 04 '22 at 14:47
  • What is your desired result for this action query? What do you want in `str(result)`? Do you want rows affected reported back? – Parfait Mar 04 '22 at 15:09
  • 1
    `str(result)` was just me debugging what was going on. I want an empty list returned if no rows returned or a non `select` statement was run –  Mar 04 '22 at 18:07

1 Answers1

2

According to the Python Database API PEP 249 specification, the return value of cursor.execute is not defined. So DB-APIs like pyodbc do not need to define consistent return value.

However, specifically for pyodbc, cursor.execute() returns a <pyodbc.Cursor> object which maintains the description attribute if object contains a value but will be None if an action command:

result = cursor.execute(self.sql, self.params)

if result.descripton is None:
    self.data = []
else:
    self.data = [
        dict(zip([key[0] for key in cursor.description], row)) 
        for row in 
        result.fetchall() 
    ]
cnxn.close()

return self.data   # METHODS INSIDE CLASSES DO NOT REQUIRE RETURN

Consider even a ternary operator:

result = cursor.execute(self.sql, self.params)

self.data = (
    [
        dict(zip([key[0] for key in result.description], row)) 
        for row in result.fetchall()
    ]
    if result.descripton is not None
    else []
)

cnxn.close()

return self.data
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • 1
    +1 Perhaps worth noting that `.execute()` doesn't just return *a* Cursor object, it returns *the* Cursor object. After executing `result = cursor.execute("SELECT 1 AS foo")` notice that `result == cursor` returns `True`. That's why we can do `cursor.execute(…).fetchall()` in pyodbc whereas in other DBAPIs we have to do `cursor.execute(…)` followed by `cursor.fetchall()`. – Gord Thompson Mar 04 '22 at 23:56
  • 1
    Excellent note @GordThompson –one of the authors/contributors to the great package: `pyodbc`! Actually, I noticed that equality where above ternary operator example uses `result.description`. – Parfait Mar 05 '22 at 01:57