I have fetch the data by calling stored procedure with arguments by using Python. Now I want my data should come in JSON format. How could I do this ?

import pyodbc

connection = pyodbc.connect("Driver={SQL Server};"
                      "[email protected];"

cursor = connection.cursor()

pno = 3 
psize = 2 

sql = "{call [NorthPointCore].[client].[test_python](?, ?)}"
values = (pno, psize)

cursor.execute(sql, (values))

columns = [column[0] for column in cursor.description]
results = []
for row in cursor.fetchall():
    results.append(dict(zip(columns, row)))


After running the above code, I get the following result:

[{'AsOfDate': '2019-03-27', 'PortfolioName': 'VMFC', 'Strategy': 'CANADA', 'PMStrategy': 'CAD CONVERTS', 'PMTeam': 'CANADA (MANOS)', 'SubStrategy': 'TRE.CVCA', 'SecurityType': 'Equity', 'InvestmentType': None, 'SecurityName': 'EMEXF-USAA', 'InvestmentDescription': 'EMERALD PLANTATION HOLDINGS', 'Ticker': 'EMEXF', 'PositionDirection': 'Long', 'Qty': Decimal('1528493.0000000000000000'), 'MV_Base': Decima03-27', 'PortfolioName': 'VMFC', 'Strategy': 'CANADA', 'PMStrategy': 'CAD CONVERTS', 'PMTeam': 'CANADA (MANOS)', 'SubStrategy': 'BB.CVCA', 'SecurityType': 'Bond', 'InvestmentType': 'Convertible', 'SecurityName': 'BBCN/320SF-CNAA', 'InvestmentDescription': 'BLACKBERRY LTD', 'Ticker': 'BBCN', 'PositionDirection': 'Long', 'Qty': Decimal('19093.0000000000000000'), 'MV_Base': Decimal('0.0000'), 'LongMV': Decimal('0.0000'), 'StartPrice': Decimal('99.5000000000'), 'Price': Decimal('99.5000000000')}]

Now I want to convert this above data into JSON, but it keeps saying error : raise TypeError(f'Object of type {o.class.name} ' TypeError: Object of type Decimal is not JSON serializable

How could I remove error and get 99.50 instead of Decimal('99.50), in all JSON formats, and get a simple JSON data?

2 Answers

Shuvojit On Best Solutions

You can do that by:

# first import json
import json
# returns a dict containing all rows
rows = cursor.fetchall() 

# Print rows in json format

Edit: Object of type Decimal is not JSON serializable can be fixed with an serializer

import decimal

def dec_serializer(o):
    if isinstance(o, decimal.Decimal):
        return float(o)

json.dump(results, default=dec_serializer)
Venkataraman R On

If you are using SQL Server 2016 or later versions, you have got FOR JSON clause to return data as JSON document. Read more about FOR JSON. It is more efficient to do in the SQL Side, instead of doing in the python side.

SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth as dob FROM People FOR JSON PATH