Fetch stored procedure result as JSON

Admin 1
Viewed 762

My code:

cursor = conn.cursor()
cursor.execute("EXEC prcFetch")  # Fetching Json data from database via stored procedure
res = cursor.fetchall()
conn.close()
print(res)                      # type(res) is list

Stored procedure:

GO
CREATE PROCEDURE prcFetch AS
BEGIN
    SELECT * 
    FROM JsonData 
    FOR JSON PATH
END

Output:

[('[{"Name":"abc","Age":"25","Gender":"M"}]',)]

I tried to deserialize the above output.

print(json.loads(res))

Error:

TypeError: the JSON object must be str, bytes or bytearray, not list

pyodbc sql-server python json
2 Answers

@Martijn Pieters's answer in correct but SQL Server splits the result of FOR JSON into multiple rows if it is more than 8000 bytes. To get all rows as JSON in one row, try this in your stored procedure:

    CREATE PROCEDURE prcFetch AS
    BEGIN
     
     DECLARE @result NVARCHAR(max) = (SELECT * FROM JsonData FOR JSON AUTO);
        
     SELECT @result;

    END
user_60231730

You fetched all rows, by using cursor.fetchall()

res = cursor.fetchall()

For a stored procedure, that's just a single row, with one column. The result is a list of rows with one element, and that one element is a tuple with a single column, with your JSON.

Don't use cursor.fetchall(), use cursor.fetchval(), which returns the first column of the first row:

json_data = cursor.fetchval()

cursor.fetchval() is specific to the pyodbc library, and not part of the Python DB-API 2.0 specification. If you are using some other library, use cursor.fetchone() instead, and either use indexing (json_data = cursor.fetchone()[0]) or tuple assignment (json_data, = cursor.fetchone()).

user_1002970
Related