Server-py/sri-pysdk/xclient/xinflux.py

# update: 2021-8-23-14
# see: https://github.com/influxdata/influxdb-python - pip install influxdb
# see: https://pypi.org/project/influxdb/#history
# see: https://registry.hub.docker.com/_/influxdb
# see: https://influxdb-python.readthedocs.io/en/latest/include-readme.html
# see: https://github.com/unaizalakain/qinfluxdb - influxdb orm
# see: https://blog.csdn.net/liuming690452074/article/details/115719884 - delete
# see: https://blog.csdn.net/xiligey1/article/details/104530893/
# see: https://blog.csdn.net/yue530tomtom/article/details/82688453 - func
"""
"""
from influxdb import InfluxDBClient
import datetime
import time


class Client(InfluxDBClient):

    def __init__(self, host='fra-middleware-influx', port=8086, database='vms'):
        """
        内部访问:
            host: fra-middleware-influx
            port: 8086
        远程访问:
            host: 192.168.30.59
            port: 7080
        """
        """
        def filter
        def remove
        def append
        def add

        def write
        def read
        def save
        def add
        def set
        def get
        def put

        def craete
        def delete
        def update
        def select
        """
        self.database = database
        super().__init__(host, port, 'admin', 'admin', database)
        self.create_database(database)

    def add_item(self, table, key_dict=dict(), value_dict=dict()):
        """保存单条"""
        _points = [
            {
                'measurement': table,  # 表名
                'time': datetime.datetime.now().isoformat('T'),  # iso标准格式
                'tags': key_dict,
                'fields': value_dict
            }
        ]
        return self.write_points(_points, database=self.database)

    def add_items(self, table, key_list=list(), value_list=list()):
        """保存多条"""
        _points = list()
        for key, value in zip(key_list, value_list):
            _point = {
                'measurement': table,  # 表名
                'time': datetime.datetime.now().isoformat('T'),  # iso标准格式
                'tags': key,
                'fields': value
            }
            _points.append(_point)
        return self.write_points(_points, database=self.database)

    def filter_by_time_range(self, table, start_ts, end_ts):
        """按时时间筛选"""
        start_at = time.strftime('%Y-%m-%dT%H:%M:%SZ', time.localtime(start_ts))
        end_at = time.strftime('%Y-%m-%dT%H:%M:%SZ', time.localtime(end_ts))
        sql = f"select * from {table} "
        sql += f"where time >= '{start_at}' and time <= '{end_at}'"
        sql += f'order by time desc;'
        result = self.query(sql, database=self.database)
        return result.get_points()

    def remove_one(self, table, k, v):  # todo 不可用
        """按时时间筛选"""
        sql = f"delete from {table} where \"{k}\"=\"{v}\";"
        return self.query(sql, database=self.database)

    def delete_table(self, table):
        return self.drop_measurement(table)


if __name__ == '__main__':
    """
    sudo python3 -m pip install influxdb
    sudo python3 /home/server/projects/taiwuict/cscec-8bur-vms/supplement-python/clients/db_influx.py
    """
    import time

    # --- init ---
    # xdb = Client(host='192.168.30.59', port=7080, database='vms')
    # xdb = Client(host='192.168.30.169', port=7080, database='vms')
    # xdb = Client(host='192.168.30.13', port=7080, database='vms')
    # xdb = Client(host='192.168.20.231', port=7080, database='vms')
    # xdb = Client(host='192.168.0.15', port=7080, database='vms')
    xdb = Client(host='192.168.1.242', port=7080, database='vms')  # 第4现场

    # --- test ---
    s = time.time() - (86400 * 100)
    e = time.time()
    out = xdb.filter_by_time_range('FaceLog', s, e)
    for i in out:
        print(i)
        break

    # --- test ---
    # s = 1649606400
    # e = 1650297600
    # out = xdb.filter_by_time_range('FaceLog', s, e)
    # for i in out:
    #     print(i)
    #     break

    # --- test filter_by_time_range ---
    # s = time.mktime(time.strptime('2020-01-09 13:45:00', '%Y-%m-%d %H:%M:%S'))
    # e = time.mktime(time.strptime('2022-01-01 17:00:00', '%Y-%m-%d %H:%M:%S'))
    # out = xdb.filter_by_time_range('FaceLog', s, e)
    # for i in out:
    #     print(i)
    #     break

    # --- test 添加数据 ---
    # out = xdb.add_item('FaceLog', {}, {'aaa': 111, 'bbb': 222})
    # print(out)

    # --- test delete_table ---
    # xdb.delete_table('FaceLog')

    # --- test remove_one ---
    # xdb.remove_one('FaceLog', 'face_uuid', 'aaabbbccc')

    # --- test 获取数据库列表 ---
    # out = xdb.get_list_database()
    # print(out)

    # --- test 创建数据库 ---
    # xdb.create_database('vms')
    # out = xdb.get_list_database()
    # print(out)

    # --- test 删除数据库 ---
    # out1 = xdb.drop_database('vms1')
    # out = xdb.get_list_database()
    # print(out1, out)

    # --- test 显示数据库中的表 ---
    # out = xdb.query('show measurements;', database=xdb.database)
    # print(out)

    # --- test 添加数据 ---
    # points = [
    #     {
    #         # "time": datetime.datetime.utcnow().isoformat('T'),  # iso标准时间
    #         "time": datetime.datetime.now().isoformat('T'),  # iso标准时间
    #         "measurement": "table23",  # 表名
    #         "tags": {
    #             "key1": "server01",
    #             "key2": "us-west"
    #         },
    #         "fields": {
    #             "field1": 0.64,
    #             "field2": 0.42,
    #         }
    #     }
    # ]  # 待写入数据库的点组成的列表
    # out = xdb.write_points(points, database=xdb.database)  # 将这些点写入指定database
    # print(out)

    # --- test 删除表数据 ---
    # table_name = 'students'
    # xdb.query(f"drop measurement {table_name}")  # 删除表

    # --- test query ---
    # sql = f"select <字段名> from <数据库.表名> "
    # sql += f"where time >= '<开始时间>' and time <= '<结束时间>' "
    # sql += f"TZ('Asia/Shanghai')"
    # out = xdb.query(sql, database='vms')
    # print(out)

    # --- test ---
    # now = datetime.datetime.utcnow().isoformat('T')
    # print(now, type(now))

    # --- test ---
    # _sql = f"select field1, field2 from table23 "
    # _sql += f"where time >= '{start_at}' and time <= '{end_at}' "
    # _sql += f"TZ('Asia/Shanghai');"
    # out = xdb.query(_sql, database=xdb.database)
    # print(out)

    # --- test add_item ---
    # _key_dict = {
    #     "key1": "server01",
    #     "key2": "server02",
    # }
    # _value_dict = {
    #     "field1": 0.64,
    #     "field2": 0.55,
    # }
    # out = xdb.add_item('table123', _key_dict, _value_dict)
    # print(out)

    # --- test add_item ---
    # _key_dict = {
    #     'face_uuid': 'aaabbbccc',
    # }
    # _value_dict = {
    #     'count': 1,
    # }
    # xdb.add_item('FaceLog', _key_dict, _value_dict)