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?

Improve this question
7
  • 2
    Aside - consider avoiding stored procedures in MSSQL starting with sp_* which signify system procedures and even has performance issues. Commented Mar 4, 2022 at 14:42
  • 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. Commented Mar 4, 2022 at 14:46
  • 1
    Thank you @Parfait - I am actually going to rename them after I did this side first i.e. Flask. Commented Mar 4, 2022 at 14:47
  • 1
    @Zhorov this was just some dummy SQL as the actual statement is much longer. Have amended the example. Commented Mar 4, 2022 at 14:47
  • 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 Commented Mar 4, 2022 at 18:07

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.