python篇 003-- influxdb 2.x 查询时将influxdb的数据整合成表，并转换成dataframe

influx -python -client 2.x 版本中， 放弃了  influxsql写法 采用 自家influx语法，使用python 使用pandas 获取数据的时候如果要转换成dataframe格式，可以采用read_dataframe方法，但是默认会dataframe是一个的时候会返回为list 在这里 就需要自己手动拼接， 可以采用内置方法进行转换。

    with InfluxDBClient(url=dbconf.HOST, token=dbconf.TOKEN, org=dbconf.ORG, debug=False) as client:
        global stopstamp,startstamp
        startstamp, stopstamp = dbconf.getlocaltime()
        query_api = client.query_api()
        query_str = 'import "timezone" option location = timezone.location(name: "Asia/Shanghai") '
        query_str += 'from(bucket: "' + dbconf.BUCKET + '")'
        query_str += ' |> range(start: ' + \
                     str(startstamp) + ', stop: ' + str(stopstamp) + ') '
        query_str += '|> filter(fn: (r) => r["_measurement"] == "' + \
                     dbconf.MEASUREMENTONE + '")'
        query_str += '|> filter(fn: (r) => r["_field"] == "RotateSpeed2_decimal" or  r["_field"] == "RotateSpeed1_decimal" or  r["_field"] == "Torque_decimal" or  r["_field"] == "EnvironmentTemp_decimal" or  r["_field"]  == "Mot2_speed_rpm_gui_int" or  r["_field"] == "AI_PhaseWCurrent2_gui_decimal" or  r["_field"] == "AI_PhaseWCurrent2_RMS_gui_decimal" or  r["_field"] == "AI_PhaseUCurrent2_RMS_gui_decimal" or  r["_field"] == "AI_PhaseUCurrent2_gui_decimal" or  r["_field"] == "AI_BusNegCurrent_gui_decimal" or r["_field"]== "AI_Motor2Temp1_gui_decimal")'
        query_str += '|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")'
        query_str += '|> yield()'
        print(query_str)
        tables = query_api.query(query_str, org=dbconf.ORG)
        data = pd.DataFrame()
        list_time = []
        RotateSpeed2_decimal_list = []
        RotateSpeed1_decimal_list = []
        Torque_decimal_list = []
        EnvironmentTemp_decimal_list = []
        Mot2_speed_rpm_gui_int_list = []
        AI_PhaseWCurrent2_gui_decimal_list = []
        AI_PhaseWCurrent2_RMS_gui_decimal_list = []
        AI_PhaseUCurrent2_RMS_gui_decimal_list = []
        AI_PhaseUCurrent2_gui_decimal_list = []
        AI_BusNegCurrent_gui_decimal_list = []
        AI_Motor2Temp1_gui_decimal_list = []
        for table in tables:
            for row in table.records:
                list_time.append(row.__getitem__(
                    '_time').strftime('%Y-%m-%dT%H:%M:%S.%fZ'))
                RotateSpeed2_decimal_list.append(
                    row.__getitem__('RotateSpeed2_decimal'))
                RotateSpeed1_decimal_list.append(
                    row.__getitem__('RotateSpeed1_decimal'))
                Torque_decimal_list.append(row.__getitem__('Torque_decimal'))
                EnvironmentTemp_decimal_list.append(
                    row.__getitem__('EnvironmentTemp_decimal'))
                Mot2_speed_rpm_gui_int_list.append(
                    row.__getitem__('Mot2_speed_rpm_gui_int'))
                AI_PhaseWCurrent2_gui_decimal_list.append(
                    row.__getitem__('AI_PhaseWCurrent2_gui_decimal'))
                AI_PhaseWCurrent2_RMS_gui_decimal_list.append(
                    row.__getitem__('AI_PhaseWCurrent2_RMS_gui_decimal'))
                AI_PhaseUCurrent2_RMS_gui_decimal_list.append(
                    row.__getitem__('AI_PhaseUCurrent2_RMS_gui_decimal'))
                AI_PhaseUCurrent2_gui_decimal_list.append(
                    row.__getitem__('AI_PhaseUCurrent2_gui_decimal'))
                AI_BusNegCurrent_gui_decimal_list.append(
                    row.__getitem__('AI_BusNegCurrent_gui_decimal'))
                AI_Motor2Temp1_gui_decimal_list.append(
                    row.__getitem__('AI_Motor2Temp1_gui_decimal'))
        data['time'] = list_time
        data['RotateSpeed2_decimal'] = RotateSpeed2_decimal_list
        data['RotateSpeed1_decimal'] = RotateSpeed1_decimal_list
        data['Torque_decimal'] = Torque_decimal_list
        data['EnvironmentTemp_decimal'] = EnvironmentTemp_decimal_list
        data['Mot2_speed_rpm_gui_int'] = Mot2_speed_rpm_gui_int_list
        data['AI_PhaseWCurrent2_gui_decimal'] = AI_PhaseWCurrent2_gui_decimal_list
        data['AI_PhaseWCurrent2_RMS_gui_decimal'] = AI_PhaseWCurrent2_RMS_gui_decimal_list
        data['AI_PhaseUCurrent2_RMS_gui_decimal'] = AI_PhaseUCurrent2_RMS_gui_decimal_list
        data['AI_PhaseUCurrent2_gui_decimal'] = AI_PhaseUCurrent2_gui_decimal_list
        data['AI_BusNegCurrent_gui_decimal'] = AI_BusNegCurrent_gui_decimal_list
        data['AI_Motor2Temp1_gui_decimal'] = AI_Motor2Temp1_gui_decimal_list

其中 通过 

|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

就可以将influxdb的查询数据整合到一个表中。

然后 通过 pd.DataFrame() 新建一个dataframe 包裹该list 就可以了 。 

官方的 query_data_frame 中 其实有过判断 如果是 单个dataframe 会自动返回 一个list

如果对速度要求不是很快的话可以采用上述的这种写法。 

下面是 官方的代码 

        from ..extras import pd

        _generator = self.query_data_frame_stream(query, org=org, data_frame_index=data_frame_index, params=params)
        _dataFrames = list(_generator)

        if len(_dataFrames) == 0:
            return pd.DataFrame(columns=[], index=None)
        elif len(_dataFrames) == 1:
            return _dataFrames[0]
        else:
            return _dataFrames