CODEWITHSUNDEEP
pythonsql-serverpython-3.xflaskpyodbc
user18311772
user18311772

Reputation:

Python PYODBC: Previous SQL was not a query

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?

Upvotes: 1

Views: 1365

Answers (1)

Parfait
Parfait

Reputation: 107767

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

Upvotes: 2

Related Questions