python - Python3 sqlite3从json数据加载表
问题描述
背景故事
我有一台 Billion PowerTracker SG6200NXL，我的配电盘中有 3 个传感器。直到最近，我写了一段 python 来从这个跟踪器下载一个 sqlite 数据库，这样我就可以绘制我的电源使用情况。不幸的是，最近，USB 端口停止工作，而这正是 PowerTracker 存储 Sqlite 数据库的地方 :( 更糟糕的是，PowerTracker 上传数据的网站已经关闭。这两个事件都使 PowerTracker 几乎无用。在无奈之下，有一个 API 可以轮询和检索 JSON 格式的传感器数据。我可以创建 sqlite 数据库和表，没有问题。我可以轮询 PowerTracker，没有问题。
我遇到的困难是将 JSON 数据加载到数据库表中。该表包含列，其中许多未使用，但那些与名称中的 JSON 字段匹配。因此，我收到一个包含 [版本、cmd、状态和设备] 的 dict 设备条目是（设备）列表。
每个设备都有一个字段字典，我想从中将数据值传输到数据库数据表中。
我可以循环并插入每个作为插入表 (column,column) 值 (dict[field], dict[field]) 但我认为必须有一种更 Pythony 的方式来执行此操作。
请参阅下面的一些调试输出，这可能会澄清这个问题。
CREATE TABLE DATA (
time_index INTEGER,
TimeStamp INTEGER,
EUI64 TEXT,
modelident TEXT,
modelname TEXT,
alias TEXT, -- added this - check R script for compatability
devicetype INTEGER,
voltage REAL,
current REAL,
frequency REAL,
powerfactory REAL,
activepower REAL,
apparentpower REAL,
mainenergy REAL,
voltage2 REAL,
current2 REAL,
frequency2 REAL,
powerfactory2 REAL,
activepower2 REAL,
apparentpower2 REAL,
mainenergy2 REAL,
voltage3 REAL,
current3 REAL,
frequency3 REAL,
powerfactory3 REAL,
activepower3 REAL,
apparentpower3 REAL,
mainenergy3 REAL,
voltage4 REAL,
current4 REAL,
frequency4 REAL,
powerfactory4 REAL,
activepower4 REAL,
apparentpower4 REAL,
mainenergy4 REAL,
Dimming_L1 REAL,
Dimming_L2 REAL,
Dimming_L3 REAL,
Dimming_L4 REAL,
Temperature REAL,
Humidity REAL,
onoffstatus INTEGER,
rssi INTEGER,
lqi INTEGER,
nenergy REAL,
rssi1 INTEGER,
CT_ratio INTEGER
);
CREATE TABLE index_Table (
num INTEGER,
HS_index INTEGER,
UpDateIndex INTEGER
);
Device ID Usage
000D6F0005A5D77E Power Points
000D6F0005A5BCAE Shed
000D6F0005A5BE9D Grid
So, the cmd=list_metering generates json output similar to the following
{
"devices": [
{
"signalstrength1": "N/A",
"negativeenergy": "0.072",
"mainenergy": "11282.062",
"apparentpower": "805.22",
"activepower": "698.76",
"powerfactor": "87",
"frequency": "50.02",
"current": "3.38",
"voltage": "238.43",
"timestamp": "1609152925",
"signalstrength": "40",
"dimming": "false",
"sense": "false",
"supportIdentify": "1",
"state": "N/A",
"metering3phase": "false",
"metering": "true",
"alias": "Power Points",
"modid": "gpm-hazr-4.2",
"model": "SG3015-T3(100A)",
"deviceid": "000D6F0005A5D77E"
},
{
"signalstrength1": "N/A",
"negativeenergy": "8272.385",
"mainenergy": "11695.240",
"apparentpower": "562.74",
"activepower": "-32.76",
"powerfactor": "-6",
"frequency": "50.02",
"current": "2.36",
"voltage": "238.32",
"timestamp": "1609152925",
"signalstrength": "44",
"dimming": "false",
"sense": "false",
"supportIdentify": "1",
"state": "N/A",
"metering3phase": "false",
"metering": "true",
"alias": "Grid",
"modid": "gpm-hazr-4.2",
"model": "SG3015-T3(100A)",
"deviceid": "000D6F0005A5BE9D"
},
{
"signalstrength1": "N/A",
"negativeenergy": "50.763",
"mainenergy": "11534.174",
"apparentpower": "228.53",
"activepower": "112.86",
"powerfactor": "49",
"frequency": "50.02",
"current": "0.96",
"voltage": "238.05",
"timestamp": "1609152925",
"signalstrength": "40",
"dimming": "false",
"sense": "false",
"supportIdentify": "1",
"state": "N/A",
"metering3phase": "false",
"metering": "true",
"alias": "Shed",
"modid": "gpm-hazr-4.2",
"model": "SG3015-T3(100A)",
"deviceid": "000D6F0005A5BCAE"
}
],
"status": "success",
"cmd": "list_metering",
"version": "1.1"
}
解决方案
我不是 python 专家，所以对改进 python3 编码风格的建议表示赞赏。
以下代码有效，不整洁，没有任何合理类型的错误处理，但它是概念证明。
with requests.Session() as session:
#print("Retrieving readings from Power Gateway...")
cmd_list_metering_json_result = session.get("http://" + host + "/api_json.asp?cmd=list_metering&auth="+userPasswordBase64)
#print("Retrieved.")
cmd_list_metering_dict = cmd_list_metering_json_result.json()
#print("Number of results: " , len(cmd_list_metering_dict))
#print("cmd:list_metering:returned:", cmd_list_metering_dict)
#print(json.dumps(cmd_list_metering_dict, indent=4, sort_keys=True))
#print("All keys ", cmd_list_metering_dict.keys())
if cmd_list_metering_dict['status'] != 'success':
syslog.syslog("FAILURE to retrieve cmd_list_metering from power gateway")
exit(1)
#
## Only interested in the 'devices' key
## which is a list of dictionaries, one dictionary per device
##
#
#print("All Devices = ", cmd_list_metering_dict['devices'])
#print("Type = ", type( cmd_list_metering_dict['devices']) )
numberOfDevices = len(cmd_list_metering_dict['devices'])
#print("Number of devices = ", numberOfDevices)
if numberOfDevices != 3:
syslog.syslog("PowerTracker requires reboot - expect 3 devices")
exit(1)
devIx = 0
timeOfDayInSeconds = int(datetime.datetime.today().timestamp())
#print("timeOfDayInSeconds = ", timeOfDayInSeconds)
for devIx in range(0, numberOfDevices):
device_dict = cmd_list_metering_dict['devices'][devIx]
#print("Storing device ", device_dict['deviceid'], " alias ", device_dict['alias'])
data_tuple = (
timeOfDayInSeconds
,device_dict['timestamp']
,device_dict['deviceid']
,device_dict['modid']
,device_dict['model']
,device_dict['alias']
,15
,device_dict['voltage']
,device_dict['current']
,device_dict['frequency']
,device_dict['powerfactor']
,device_dict['activepower']
,device_dict['apparentpower']
,device_dict['mainenergy']
,1
,device_dict['signalstrength']
,1
,device_dict['negativeenergy']
,1
)
rowsInserted = dbCursor.execute("""insert into DATA (
time_index
,TimeStamp
,EUI64
,modelident
,modelname
,alias
,devicetype
,voltage
,current
,frequency
,powerfactory
,activepower
,apparentpower
,mainenergy
,onoffstatus
,rssi
,lqi
,nenergy
,CT_ratio
) values (
?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
);""", data_tuple)
#print("Committing transaction...")
dbConn.commit()
#print("Closing cursor...")
dbCursor.close()
#if dbConn:
# print("Disconnecting from database...")
# dbConn.close()
推荐阅读
- python - 如何修复 sqlalchemy.exc.InvalidRequestError：一个或多个映射器无法初始化...原始异常是：viewonly
- python - 从 Reading 类型的对象中提取特定值（Reading 是加载 NEM13 文件时返回的对象）
- c# - CS1061：MyClass 不包含 X 的定义，并且找不到接受 MyClass 类型的第一个参数的扩展方法 X
- c# - SearchResultCollection 类：对象属性值比较
- azure-devops - Azure DevOps 和发布流程，热修复时处理版本控制？
- visual-studio - 在新的 git 体验 VS 16.8 中找不到旧的“同步”
- typescript - 如何推断外部函数的参数类型
- list - Agda 列出自然数的通用列表的最后一个，并与 1 的列表连接
- c++ - 2D FFT 将两个矩阵都转换为 FFT 格式后该怎么办？
- web-scraping - 如何将数据从多页存储到json？