Python InfluxDBClient - How to read a single measurement and nothing else (SOLVED)

[MD500_Pilot]

I am new to InfluxDB moving from MySQL for this particular project. I have an energy and pool management system that I wrote in Python and I have successfully been able to write my data to InfluxDB via the InfluxDBClient for Python but I am having a problem reading the data back out again.

Basically, I need to be able to generate a query that returns a single number and I keep getting (what looks like) json back as my result that includes a bunch of other information. I say “what looks like” json because the result that I am getting back fails the json validator.

Here is my query from the cli:

> SELECT XXX340_ch4_w FROM energy ORDER by time DESC LIMIT 1
name: energy
time                          XXX340_ch4_w
----                          ------------
1522164296000000000           -1101.375

Here is my python code:

def read_energy_data(db, measurement, device):
    client = InfluxDBClient(pooldb.influx_host, pooldb.influx_port, pooldb.influx_user, pooldb.influx_password, db)
    results = client.query(("SELECT %s from %s ORDER by time DESC LIMIT 1") % (device, measurement))
    return results

Here are the results returned with that query:

 ResultSet({'(u'energy', None)': [{u'XXX340_ch4_w': -1101.375, u'time': u'2018-03-27T15:28:00Z'}]})

What I need to be returned is just the -1101.375 and nothing else. When I attempt to feed it into the python json parser it fails and attempting to validate it via https://jsonlint.com/ also fails.

I also attempted this:

def read_energy_data(db, measurement, device):
    client = InfluxDBClient(pooldb.influx_host, pooldb.influx_port, pooldb.influx_user, pooldb.influx_password, db)
    results = client.query(("SELECT %s from %s ORDER by time DESC LIMIT 1") % (device, measurement))
    watts_in_use = list(results.get_points(measurement=measurement))
    return watts_in_use

Which resulted in this:

[{u'XXX340_ch4_w': -1101.375, u'time': u'2018-03-27T15:39:12Z'}]

So a little less data, but still not what I am looking for and still not properly formatted json preventing me from running it through the python json library to get my data.


I am hoping someone can point me in the right direction.

Thank You

[mark]

@MD500_Pilot

ResultSet({‘(u’energy’, None)‘: [{u’XXX340_ch4_w’: -1101.375, u’time’: u’2018-03-27T15:28:00Z’}]})

InfluxDB returns valid JSON. You’re in a Python REPL or something that’s printing out leading us to indicate you’re working with Unicode strings: 3. An Informal Introduction to Python — Python 2.7.18 documentation

If you query with curl from the command line, you’ll see valid JSON.

@MD500_Pilot

What I need to be returned is just the -1101.375 and nothing else.

You’ll need to parse that out of the result. I’m not familiar with the Python client library and I don’t know if it offers convenience methods to extract the value.

[noahcrowley]

If you take a look at the influxdb-python API documentation, the query() function of an InfluxDBClient object returns a ResultSet object.

https://influxdb-python.readthedocs.io/en/latest/api-documentation.html#influxdbclient
https://influxdb-python.readthedocs.io/en/latest/api-documentation.html#resultset

Checking the type of this object using type(results), you should see <class 'influxdb.resultset.ResultSet'>.

That object has a get_points() method that returns a generator with all the points, which in Python behaves much like an iterator. For example:

>>> points=result.get_points()
>>> type(points)
<class 'generator'>

You can then use for x in points to loop through the values, or you can use next(points) to return the next value. The individual items returned by the generator are Python dictionaries, and should be accessed as such.

This snippet should print out the value you’re looking for:

results = client.query(("SELECT %s from %s ORDER by time DESC LIMIT 1") % (device, measurement))
points = results.get_points()
for item in points:
    print(item[device])

[MD500_Pilot]

Noah - THANK YOU! I did a much more brute force method to get the same results, but now I can do it the right way!!

[MD500_Pilot]

Thanks Mark - I am using the InfluxDBClient for Python to get this data and not doing anything else to it, so I am not sure why I am not getting valid json back.

[mark]

Suppress the u'prefix indicating unicode' in python strings

[MD500_Pilot]

Noah –

I tried your example and I am getting the following error:

 print(item['value'])
 TypeError: list indices must be integers, not str

Any ideas as to why?

[MD500_Pilot]

Yikes…does not look promising (from the link you posted):

In short, no. You cannot turn this off.

Is there anyway to turn this off in the InfluxDBClient which is what is grabbing the data?

[MD500_Pilot]

@noahcrowley and @mark -

I did finally get it to work but I am sure there has to be a better way. This is how I was finally able to make it work:

def get_current_solar():
    current_solar_results = str(influx_data.read_energy_data("electrical_monitoring", "energy", "XXX340_ch4_w"))
    current_solar_search = re.search("[-](?:\d*\.)\d+", current_solar_results)
    if current_solar_search == "None":
        current_solar = 0
    else:
        current_solar = (int(float(current_solar_search.group(0)) * -1))
    return current_solar

[noahcrowley]

@MD500_Pilot

I left out a step where you have to get the points from the result, but I’ve updated my previous answer. It should work now.

As I mentioned before, the query() function of an InfluxDBClient object returns a ResultSet object, not JSON. It might look like JSON but what you are seeing are string representations of Python objects, dictionaries, and lists.

This bit of output that you shared, for instance, describes a Python object:

ResultSet({'(u'energy', None)': [{u'XXX340_ch4_w': -1101.375, u'time': u'2018-03-27T15:28:00Z'}]})

When you wrote this code:

watts_in_use = list(results.get_points(measurement=measurement))
return watts_in_use

You were applying the list() function to a generator object to create a list of the values the generator returns, in this case Python dictionaries. The output:

[{u'XXX340_ch4_w': -1101.375, u'time': u'2018-03-27T15:39:12Z'}]

is a list, denoted by the brackets, with only a single Python dictionary inside it, denoted by the braces.

You don’t need to convert this data into a string and then parse it; you can use Python to parse the data types directly. That also means you don’t need to worry about the u prefix for Unicode.

If for some reason you wanted to access the JSON that is returned by InfluxDB, you can use the .raw property of the ResultSet object, but you shouldn’t need to do this for your use case.

[MD500_Pilot]

@noahcrowley

OK, I think I understand better now, thank you for explaining. However, I guess I do not really understand it since I am still getting errors. Here is my code:

def read_energy_data(db, measurement, device):
    client = InfluxDBClient(pooldb.influx_host, pooldb.influx_port, pooldb.influx_user, pooldb.influx_password, db)
    results = client.query(("SELECT %s from %s ORDER by time DESC LIMIT 1") % (device, measurement))
    points = results.get_points()
    for item in points:
        energy_results = (item['value'])
    return energy_results 

and in my main program:

def get_current_solar_test():
    current_solar_results = influx_data.read_energy_data("electrical_monitoring", "energy", "XXX340_ch4_w")
    if current_solar_results == "None":
        current_solar = 0
    else:
        current_solar = (int(float(current_solar_results)) * -1)
    return current_solar

And I now get this error:

 File "/var/www/utilities/influx_data.py", line 55, in read_energy_data
    energy_results = (item['value'])
 KeyError: 'value'

[noahcrowley]

Ah, yes, ‘value’ was from my test code. I think the correct key for you would be the value in device, which in your example is ‘XXX340_ch4_w’?

You should be able to just return the value of the key in device in the dict “item” as follows:

def read_energy_data(db, measurement, device):
    client = InfluxDBClient(pooldb.influx_host, pooldb.influx_port, pooldb.influx_user, pooldb.influx_password, db)
    results = client.query(("SELECT %s from %s ORDER by time DESC LIMIT 1") % (device, measurement))
    points = results.get_points()
    for item in points:
        return item[device]

[MD500_Pilot]

Yeah!!

This works:

def read_energy_data(db, measurement, device):
    client = InfluxDBClient(pooldb.influx_host, pooldb.influx_port, pooldb.influx_user, pooldb.influx_password, db)
    results = client.query(("SELECT %s from %s ORDER by time DESC LIMIT 1") % (device, measurement))
    points = results.get_points()
    for item in points:
        return item[device]

Thank you for your patience

[saurav_srivastav]

Hi I saw your query as JSON always prefer format with double quote “” not single quote so json refuse to parse. for converting this to json format you need to pass this data in json.dumps() function.
e.g data = [{u’XXX340_ch4_w’: -1101.375, u’time’: u’2018-03-27T15:39:12Z’}]
json.dumps(data)

I tried in terminal
import json
data = [{u’XXX340_ch4_w’: -1101.375, u’time’: u’2018-03-27T15:39:12Z’}]

parsed_value = json.dumps(data)
print(parsed_value)

[{“XXX340_ch4_w”: -1101.375, “time”: “2018-03-27T15:39:12Z”}]