Convert SQL data into JSON in Python

Question

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?

I have updated my answer according to the edit you added to your question, take a look. — Shuvojit
– Shuvojit, Commented Apr 11, 2019 at 11:51

Shuvojit · Accepted Answer · 2019-04-11 11:51:20Z

2

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)

edited Apr 11, 2019 at 11:51

answered Apr 11, 2019 at 11:14

Shuvojit

1,4709 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Venkataraman R · Accepted Answer · 2019-04-11 11:26:04Z

2

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

answered Apr 11, 2019 at 11:26

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

2 Comments

Enderson Menezes Over a year ago

Thx, you help me! :)

Venkataraman R Over a year ago

@EndersonMenezes, you are welcome

Collectives™ on Stack Overflow

Convert SQL data into JSON in Python

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related