如何使用Python将JSON数据文件映射到SQLIte数据库

Question

我需要将JSON数据文件中的数据转换为SQLite数据库。JSON数据文件包含以下内容：

{
"CHILLER_2": {
"event": {
"payloadData": {
"_timestamp": 1573228140000, 
"CHILLER___TDCOMPB": 0, 
"CHILLER___TDCOMPA": 82.06, 
"CHILLER___PEB": 0, 
"COMPA___E": 83624.896, 
"CHILLER___TEVDXA": 65.86, 
"CHILLER___PEA": -4.21, 
"CHILLER___TEVDXB": 0, 
"CHILLER___PCA": -4.36, 
"CHILLER___PCB": 0, 
"COMPB___P": 0, 
"CHILLER___TSAC": 88.14, 
"data_timestamp": "2019-11-08T10:49:00Z", 
"CHILLER___TEAE": 65.41, 
"CHILLER___ST": 0, 
"CHILLER___TSCOMPB": 0, 
"COMPB___E": 0, 
"CHILLER___TEAC": 87.24, 
"COMPA___P": 0.0, 
"CHILLER___TSCOMPA": 80.15, 
"CHILLER___TSAE": 60.91
}
}
}, 
"CHILLER_1": {
"event": {
"payloadData": {
"_timestamp": 1573228140000, 
"CHILLER___TDCOMPB": 0, 
"CHILLER___TDCOMPA": 115.25, 
"CHILLER___PEB": 0, 
"COMPA___E": 54329.2, 
"CHILLER___TEVDXA": 55.17, 
"CHILLER___PEA": -8.54, 
"CHILLER___TEVDXB": 0, 
"CHILLER___PCA": 6.74, 
"CHILLER___PCB": 0, 
"COMPB___P": 0, 
"CHILLER___TSAC": 95.9, 
"data_timestamp": "2019-11-08T10:49:00Z", 
"CHILLER___TEAE": 45.72, 
"CHILLER___ST": 2, 
"CHILLER___TSCOMPB": 0, 
"COMPB___E": 0, 
"CHILLER___TEAC": 87.12, 
"COMPA___P": 198.5, 
"CHILLER___TSCOMPA": 43.59, 
"CHILLER___TSAE": 41.67
}
}
}, 
"BOMBAS": {
"event": {
"payloadData": {
"BAC___P": 27.46, 
"_timestamp": 1573228140000, 
"BAF___E": 23893.98, 
"data_timestamp": "2019-11-08T10:49:00Z", 
"BAC___E": 23055.22, 
"BAF___P": 28.48
}
}
}
}

因此，我需要一种方法来为CHILLER_1、CHILLER_2和BOMBAS数据集创建一个具有相应数据的表。

就我个人而言，我是Python的新手，所以任何帮助都会被感激。

关于如何实现这一点，有什么建议吗？

Answer 1

您可以简单地使用pd.DataFrame传递json:的正确路径

import pandas as pd
chiller_2 = pd.DataFrame(data['CHILLER_2']['event']['payloadData'],index=[0])
chiller_1 = pd.DataFrame(data['CHILLER_1']['event']['payloadData'],index=[0])
bombas = pd.DataFrame(data['BOMBAS']['event']['payloadData'],index=[0])
print(chiller_2)
print(chiller_1)
print(bombas)

输出：

[1 rows x 21 columns]
C:UsersIvan LDesktopPython>soo.py
_timestamp  CHILLER___TDCOMPB  CHILLER___TDCOMPA  CHILLER___PEB       ...        CHILLER___TEAC  COMPA___P  CHILLER___TSCOMPA  CHILLER___TSAE
0  1573228140000                  0              82.06              0       ...                 87.24        0.0              80.15           60.91
[1 rows x 21 columns]
_timestamp  CHILLER___TDCOMPB  CHILLER___TDCOMPA  CHILLER___PEB       ...        CHILLER___TEAC  COMPA___P  CHILLER___TSCOMPA  CHILLER___TSAE
0  1573228140000                  0             115.25              0       ...                 87.12      198.5              43.59           41.67
[1 rows x 21 columns]
BAC___P     _timestamp   BAF___E        data_timestamp   BAC___E  BAF___P
0    27.46  1573228140000  23893.98  2019-11-08T10:49:00Z  23055.22    28.48

一旦在RDB结构中有了它们，就可以将其插入到表中，或者在sql中创建表。