1NCE（ワンス）IoT SIMのAPIを使ってデータ残量をAWS QuickSightで可視化してみる

INSERT INTO sim_data ICCID, IMSI, PIN, PIN2, PUK, PUK2, data_volume, sms_volume, data_total_volume, sms_total_volume) VALUES (値1, 値2,...);
INSERT INTO sim_data ICCID, IMSI, PIN, PIN2, PUK, PUK2, data_volume, sms_volume, data_total_volume, sms_total_volume) VALUES (値1, 値2,...);
INSERT INTO sim_data ICCID, IMSI, PIN, PIN2, PUK, PUK2, data_volume, sms_volume, data_total_volume, sms_total_volume) VALUES (値1, 値2,...);
INSERT INTO sim_data ICCID, IMSI, PIN, PIN2, PUK, PUK2, data_volume, sms_volume, data_total_volume, sms_total_volume) VALUES (値1, 値2,...);

$ yum install gcc openssl-devel bzip2-devel libffi-devel wget sudo tar make
$ cd /opt 
$ sudo wget https://www.python.org/ftp/python/3.9.6/Python-3.9.6.tgz
$ sudo tar xzf Python-3.9.6.tgz
$ cd Python-3.9.6 
$ sudo ./configure --enable-optimizations 
$ sudo make altinstall
$ sudo rm -f /opt/Python-3.9.6.tgz
$ python3.9 -V
$ echo 'alias python=python3.9' >> ~/.bashrc
$ source ~/.bashrc

$ pip install requests
$ pip install mysql.connector

## sim_data_api.py

import requests
import sys
import mysql.connector as mydb

# 環境に合わせて修正する
rds_host  = <rds_access_point_name>
name = <rds_user>
password = <rds_password>
db_name = <rds_database_name>

try:
    conn = mydb.connect(host=rds_host, user=name, passwd=password, database=db_name)
except:
    sys.exit()

conn.autocommit = False
cur = conn.cursor()

## Get_access_token　認証コードの取得

token_url = "https://api.1nce.com/management-api/oauth/token"

payload = {"grant_type": "client_credentials"}
headers = {
    "accept": "application/json",
    "content-type": "application/json",
    "authorization": "Basic <1nce_api_user+password_hash>" # ポータルで作成したAPIユーザー＆パスのハッシュ値に差替える
}

response = requests.post(token_url, json=payload, headers=headers)
jsonData = response.json() # Access_token

authorization_token = 'Bearer' + ' ' + jsonData['access_token']

## ICCIDリストの取得　もしDBから取得する場合はここを修正する

iccid_list = [
        8xxxxxxxxxxxxxxxxxxx3,
        8xxxxxxxxxxxxxxxxxxx4
        ]

for iccid in iccid_list:

    ## データ残量の取得とデータ更新

    url = "https://api.1nce.com/management-api/v1/sims/" + str(iccid) + "/quota/data"

    headers = {
            "accept": "application/json",
             "authorization": authorization_token
        }

    response = requests.get(url, headers=headers)

    usage_data = response.json()

    try:
        sql = "update sim_data set data_volume = " + str(usage_data['volume'])  + ", data_total_volume = " + str(usage_data['total_volume']) +  " where ICCID = " + str(iccid) + ";"
        cur.execute(sql)
        conn.commit()

    except:
        conn.rollback()

    ## SMS残量の取得とデータ更新

    url = "https://api.1nce.com/management-api/v1/sims/" + str(iccid) + "/quota/sms"

    headers = {
            "accept": "application/json",
             "authorization": authorization_token
        }

    response = requests.get(url, headers=headers)

    usage_sms = response.json()

    try:
        sql = "update sim_data set sms_volume = " + str(usage_sms['volume'])  + ", sms_total_volume = " + str(usage_sms['total_volume']) +  " where ICCID = " + str(iccid) + ";"
        cur.execute(sql)
        conn.commit()
    except:
        conn.rollback()

cur.close()
conn.close()

＄python3 ~/.sim_data_api.py

$ sudo crontab -e
59 23 * * * /usr/bin/python3 /home/xxxxxx/sim_data_api.py