How to parse json output into SQL Server in python

Question

How to parse json output into SQL Server in python

Treesa George 21

I have a python script where the json output is stored in "data". I need to store that output in SQL Server wherein each time when i run the python script the data shout automatically be saved in SQL server.

GeethaThatipatri-MSFT 17,211 Reputation points Microsoft Employee

2022-02-09T23:05:02.49+00:00

Hi @Treesa George Thanks for posting your query in the Microsoft forum

We understand that you are trying to store JSON output to Azure SQL using Python script, correct me if my understanding is not accurate.
For working with Python I would suggest using pyodbc and try if it helps.
We do have some examples of queries using pyodbc on this site please check Here

Please let us know if you face any issues.

Regards
Geetha
Treesa George 21 Reputation points

2022-02-10T08:00:12.463+00:00

Hi @GeethaThatipatri-MSFT Thanks for your reply.

Yes your right! I wanted to store json output into SQL Server 2019 database.

In my case the json file which i need to insert into database is already stored in variable named "data"(screenshot shared previously i.e data = res.read()).

I need to insert that "data" into the database directly where my database name called Rest and table called bms. It is getting connected to database. But i am getting the below error.

Traceback (most recent call last):
File "C:\Users\trees\RESTAPI_Python_Code\Test2.py", line 70, in <module>
cursor.execute(('INSERT INTO dbo.bms([Value],[Unit],[WatchName])'),(json.dumps(data)))
File "C:\Python310\lib\json_init.py", line 231, in dumps
return default_encoder.encode(obj)
File "C:\Python310\lib\json\encoder.py", line 199, in encode
chunks = self.iterencode(o, one_shot=True)
File "C:\Python310\lib\json\encoder.py", line 257, in iterencode
return iterencode(o, 0)
File "C:\Python310\lib\json\encoder.py", line 179, in default
raise TypeError(f'Object of type {o.class.name} '
TypeError: Object of type bytes is not JSON serializable
GeethaThatipatri-MSFT 17,211 Reputation points Microsoft Employee

2022-02-10T20:57:15.263+00:00

Hi @Treesa George Data is of type bytes, can’t run the json.dump() on it as it takes a parameter of type json

Alter json.dumps(data) to json.dumps(data.json()) and give it a try.

Regards
Geetha
GeethaThatipatri-MSFT 17,211 Reputation points Microsoft Employee

2022-02-14T15:49:13.16+00:00

Hi, @Treesa George We haven't heard back from you. Just wanted to check if you are still facing the issue?

Regards
Geetha

GeethaThatipatri-MSFT 17,211 Reputation points Microsoft Employee

2022-02-09T23:05:02.49+00:00

Hi @Treesa George Thanks for posting your query in the Microsoft forum

We understand that you are trying to store JSON output to Azure SQL using Python script, correct me if my understanding is not accurate.
For working with Python I would suggest using pyodbc and try if it helps.
We do have some examples of queries using pyodbc on this site please check Here

Please let us know if you face any issues.

Regards
Geetha
Treesa George 21 Reputation points

2022-02-10T08:00:12.463+00:00

Hi @GeethaThatipatri-MSFT Thanks for your reply.

Yes your right! I wanted to store json output into SQL Server 2019 database.

In my case the json file which i need to insert into database is already stored in variable named "data"(screenshot shared previously i.e data = res.read()).

I need to insert that "data" into the database directly where my database name called Rest and table called bms. It is getting connected to database. But i am getting the below error.

Traceback (most recent call last):
File "C:\Users\trees\RESTAPI_Python_Code\Test2.py", line 70, in <module>
cursor.execute(('INSERT INTO dbo.bms([Value],[Unit],[WatchName])'),(json.dumps(data)))
File "C:\Python310\lib\json_init.py", line 231, in dumps
return default_encoder.encode(obj)
File "C:\Python310\lib\json\encoder.py", line 199, in encode
chunks = self.iterencode(o, one_shot=True)
File "C:\Python310\lib\json\encoder.py", line 257, in iterencode
return iterencode(o, 0)
File "C:\Python310\lib\json\encoder.py", line 179, in default
raise TypeError(f'Object of type {o.class.name} '
TypeError: Object of type bytes is not JSON serializable
GeethaThatipatri-MSFT 17,211 Reputation points Microsoft Employee

2022-02-10T20:57:15.263+00:00

Hi @Treesa George Data is of type bytes, can’t run the json.dump() on it as it takes a parameter of type json

Alter json.dumps(data) to json.dumps(data.json()) and give it a try.

Regards
Geetha
GeethaThatipatri-MSFT 17,211 Reputation points Microsoft Employee

2022-02-14T15:49:13.16+00:00

Hi, @Treesa George We haven't heard back from you. Just wanted to check if you are still facing the issue?

Regards
Geetha

Answer 1

Treesa George 21

Hi @GeethaThatipatri-MSFT sorry for late reply. The solution that you advised did not work. Instead I converted it to a list and later used dataframe through which I could insert it into database.
Below is the steps.

Thanks for your help and follow up. Appreciate it.

GeethaThatipatri-MSFT 17,211 Reputation points Microsoft Employee

2022-02-14T23:01:36.687+00:00

Hi @ TreesaGeorge-4691 Thanks for providing your solution I am converting this to an answer.

Thank you for visiting Microsoft QA forums! Have a great day!

Regards
Geetha

How to parse json output into SQL Server in python

1 answer

Activity