How to transfer JSON data to SQL and put the appropriate data into a specific table

Question

I am trying to transfer a JSON database to SQL: https://data.cityofnewyork.us/api/views/6bzx-emuu/rows.json?accessType=DOWNLOAD

But the problem I have is that how can I insert the data in the appropriate table when the only thing that separates each data is just a comma.

Thanks

MySQL or SQL Server? Also post some sample JSON along with the desired results. The link is not helpful / dead. — John Cappelletti, Dec 01 '16 at 20:52
What language are you using? just SQL? or is there a script or app of some type? — Sinthia V, Dec 01 '16 at 20:55
@SinthiaV I am using Python on Jypiter to transfer the data to a MySQL server. — CheeseCube, Dec 01 '16 at 21:26

score 0 · Answer 1 · answered Dec 01 '16 at 22:04

This will break the string into an array at the commas and trim whiteespace.

my_string = "blah, lots  ,  of ,  spaces, here "
[x.strip() for x in my_string.split(',')]

Then you loop through the array elements in Python and insert each in turn using a parameterized SQL statement. The idea is to do all the string-manipulation work in Python until you have a record ready to insert. then use Python to build and exeecute your SQL statement.

How to transfer JSON data to SQL and put the appropriate data into a specific table

1 Answers1