How do I get structured data when querying data from influxdb in python?

Question

I've been entering data as fields to the influxdb. when i'm querying data, I"m not getting them as a table like I would when I query in SQL. Is there any way i can get a list of dictionaries like i did when i inserted data to the db.

for insertion,

for i in range(len(df)-1):
    point=influxdb_client.Point(switch_id).field(list(df[i].keys())[0],list(df[i].values())[0]).field(list(df[i].keys())[1],list(df[i].values())[1]).field(list(df[i].keys())[2],list(df[i].values())[2]).field(list(df[i].keys())[3],list(df[i].values())[3]).field(list(df[i].keys())[4],list(df[i].values())[4]).field(list(df[i].keys())[5],list(df[i].values())[5]).field(list(df[i].keys())[6],list(df[i].values())[6]).field(list(df[i].keys())[7],list(df[i].values())[7]).field(list(df[i].keys())[8],list(df[i].values())[8])
    points.append(point)
write_status=write_api.write(bucket, org, points)

when I'm querying data, I'm sending the query as per documentation:

query_api = client.query_api()
query = 'from(bucket: "SDN-IDS")\
  |> range(start: -24h)\
  |> filter(fn: (r) => r["_measurement"] == "S1")\
  |> filter(fn: (r) => r["_field"] == "actions" or r["_field"] == "cookie" or r["_field"] == "dl_dst" or r["_field"] == "duration" or r["_field"] == "in_port" or r["_field"] == "n_bytes" or r["_field"] == "n_packets" or r["_field"] == "priority" or r["_field"] == "table")'
try:
    result = query_api.query(org=org,query=query)
    results = []
    for table in result:
        for record in table.records:
            results.append((record.get_field(), record.get_value()))

    print(results)
    client.close()
except Exception as e:
    print(e)

the resulting output is as follows:

[('actions', 'output:"s1-eth4"'), ('actions', 'output:"s1-eth4"'), ('cookie', '0x0'), ('cookie', '0x0'), ('dl_dst', '4a:b8:04:37:ed:48'), ('dl_dst', '4a:b8:04:37:ed:48'), ('duration', '36.505s'), ('duration', '36.505s'), ('in_port', '"s1-eth3"'), ('in_port', '"s1-eth3"'), ('n_bytes', '378'), ('n_bytes', '378'), ('n_packets', '5'), ('n_packets', '5'), ('priority', '1'), ('priority', '1'), ('table', '0'), ('table', '0')]

I need this data to be structured in such a way where i can also see the timestamp along with the data coming in as rows instead of a list of column-value pairs. Is it possible?

Answer 1

The timestamp can be achieved by record.get_time(). You may shape it then as you like, e.g. as JSON:

json_data=[]
...
for record in table.records:
                    json_data.append({
                        "measurement": record.get_measurement(),
                        "fields":{
                            "tag": record.get_field(),
                            "value": record.get_value()
                        },
                        "time": record.get_time() # can be shaped as you like, e.g. ISO with .replace(tzinfo=None).isoformat()+'Z'
})

or

results.append((record.get_time(), record.get_field(), record.get_value()))

Or if you're already using Pandas put it in a dataframe.