python - Python3 sqlite3 Loading table from json data

Question

Backstory

I have a Billion PowerTracker SG6200NXL, with 3 sensors in my switchboard. Up until recently, I had written a piece of python to download a sqlite database from this tracker so I could graph my power usage. Unfortunately, recently, the USB ports have stopped working and that is where the PowerTracker stores the Sqlite database :( Even worse, the web site to which the PowerTracker uploads data to, has closed down. Both these events make the PowerTracker virtually useless. In desperation, there is an API to which I can poll and retrieve the sensor data in JSON format. I can create the sqlite database and tables, no problems. I can poll the PowerTracker, no problems.

What I am having a touch of difficulty with is loading the JSON data into the database table. The table contains columns, many of which are unused, but the ones that are, match the JSON fields in name. So, I receive a dict which contains [version, cmd, status and devices] The devices entry is a list of (devices).

Each device has a dict of fields, from which I want to transfer the data values into the Database DATA table.

I could loop and insert each as insert table (column,column) values (dict[field], dict[field]) but I figure there must be a more pythony way of doing this.

See below for some debugging output, which may clarify this question.

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

Answer 1

I am no python expert, so suggestions for improvements in python3 coding style appreciated.

The following code works, not tidy, no error handling of any reasonable sort but it's a proof of concept.

    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()