CODEWITHSUNDEEP
python-3.x
Manish Pal
Manish Pal

Reputation: 371

Convert SQL data into JSON in Python

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};"
                      "Server=server_name;"
                      "Database=database_name;"
                      "UID=user;"
                      "PWD=@password;"
                      "Trusted_Connection=no;")

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)))

print(results)

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?

Upvotes: 1

Views: 10599

Answers (2)

Shuvojit
Shuvojit

Reputation: 1470

You can do that by: 

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

# Print rows in json format
print(json.dumps(rows))

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)

Upvotes: 2

Venkataraman R
Venkataraman R

Reputation: 13009

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

Upvotes: 2

Related Questions