首页 > python > (Python / JSON / MySQL)关于JSON中不会传输到db的财务数据的建议

(Python / JSON / MySQL)关于JSON中不会传输到db的财务数据的建议 ((Python/JSON/MySQL) Advice on financial data in JSON that won't transfer to db)

问题

我有一个包含原始财务数据的JSON文件,包括OHLC,交易量,交易等。除OHLC之外的所有数据都将正确传输到我设置的MySQL数据库。OHLC数据在转移到数据库后仅作为零。原始json数据如下所示:

[[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“],......

如果第一个值是时间,则引号中接下来的四个值是OHLC数据,引用中的下一个值是volume。我最初怀疑引用由于某种原因阻止了转移,虽然奇怪的卷也在引号中并且转移到我​​的数据库没有问题。

我考虑迭代文件并将其复制到另一个json文件,取出引号。我不确定这项努力是否会成功,所以我想看看那些可能更了解的人的建议是什么。

解决方法

那么这似乎是列表的列表,所以你可以使用numpy将字符串值转换为十进制值,如下所示:

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"]]

将此列表转换为numpy数组:

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

输出:

[[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]]

问题

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.

解决方法

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]]
相似信息