(Python/JSON/MySQL) Advice on financial data in JSON that won't transfer to db

Question

I have a JSON file with raw financial data, including OHLC, volume, trades, etc. All of the data except the OHLC will correctly transfer to the MySQL database I setup. The OHLC data only as zeros after the transfer to the database. The raw json data looks like this:

[[1512086400000, "0.00001204", "0.00001209", "0.00001161", "0.00001183", "771721.00000000", 1512086699999, "9.10638040", 126, "359700.00000000", "4.22792312", "52516956.22676400"], [1512086700000, "0.00001189", "0.00001194", "0.00001183", "0.00001189", "119120.00000000", 1512086999999, "1.41575664", 44, "49016.00000000", "0.58377518", "52659721.84287900"], [1512087000000, "0.00001191", "0.00001196", "0.00001183", [1512087600000, "0.00001187", "0.00001190", "0.00001171", "0.00001174", "312477.00000000", 1512087899999, "3.69618651", 63, "155121.00000000", "1.84118817", "53289721.44287900"], … …

Where the first value is time, the next four values in quotation marks are the OHLC data, and the next value in quotations is volume. I originally suspected the quotations prevented the transfer for some reason, though oddly volume is in quotes too and transfers to my database with no issue.

I considered iterating over the file and copying it to another json file, taking out the quotation marks. I'm not sure however if the effort will pan out, so I want to see what the advice of those who might know better would be.

@Mufeed, apologies. I edited the question to clarify the issue. The OHLC data transfers to the database as 0's while the rest of the data shows up with the correct values. — Mikey Mike, Jun 29 '18 at 19:06
This does not look like a json string. Do you want to decode the decimal values? — mad_, Jun 29 '18 at 19:23

score 0 · Accepted Answer · answered Jun 29 '18 at 19:27

Well this seems to be list of list so you can convert the string values to decimal values using numpy as below:

a=[[1512086400000, "0.00001204", "0.00001209", "0.00001161", "0.00001183", "771721.00000000", 1512086699999, "9.10638040", 126, "359700.00000000", "4.22792312", "52516956.22676400"],
   [1512086700000, "0.00001189", "0.00001194", "0.00001183", "0.00001189", "119120.00000000", 1512086999999, "1.41575664", 44, "49016.00000000", "0.58377518", "52659721.84287900"]]

Converting this list to numpy array:

import numpy as np
a=np.array(a)
a.astype(np.float)
print(a)

Output:

[[1.51208640e+12 1.20400000e-05 1.20900000e-05 1.16100000e-05
  1.18300000e-05 7.71721000e+05 1.51208670e+12 9.10638040e+00
  1.26000000e+02 3.59700000e+05 4.22792312e+00 5.25169562e+07]
 [1.51208670e+12 1.18900000e-05 1.19400000e-05 1.18300000e-05
  1.18900000e-05 1.19120000e+05 1.51208700e+12 1.41575664e+00
  4.40000000e+01 4.90160000e+04 5.83775180e-01 5.26597218e+07]]

(Python/JSON/MySQL) Advice on financial data in JSON that won't transfer to db

1 Answers1