ClimateDataCalculation

[krambriw]

Nice, I think your script is ok. But there is one thing. Saving directly this way, I realize we are short-cutting the control of last saved data, means it will be a thread put into the queue EVERY time the event is coming in. So either you build your own control in the script or we instead consider using the first alternative.

Let me think about this a bit, what would be simplest and best

[Mastiff]

I must say that I like the way this is done now, without calling another macro.

Did you see my final edit of the previous message, or did you post before that? For some reason or another the queue doesn't seem to pile up like it did before either. I don't know why. Also there's the thing about changing the name of the table.

[krambriw]

Ok, I read it now. Then we leave it as is, I suggest.

Regarding renaming tables, I have no clue right now, if there is a sql command in sqlite3 that allows it, it would not be difficult to write a small script that does the job.

[Mastiff]

Yes, there is! I know all about sqlite3...well, all I can find out by googling, at least...

Code: Select all

ALTER TABLE oldname RENAME TO newname

[bongolf]

Hi!

I just found this plugin and it looks really interesting. I have installed it and added the action for TempDataCapture, but I dont think anything is logged right now, because I only get an error message when I try the command:
print eg.plugins.ClimateDataCalculation.plugin.lastTempData

What should I put in the "Set the correct device ID"-box?
The event from Tellstick says:
"17:37:28 TellStickDuo.fineoffset.temperature.184.Temperature: '23.2|2016-01-17 17:37:28'"

[Mastiff]

Hi! Yeah, it's a very cool plugin, with all the graphs and stuff! You have a different type of Tellstick setup than me (I run it via OpenNetHome), but I'm pretty sure that would be 184 as the sensor ID.

[bongolf]

Thanks for the answer, but "184" was the first thing I tested and it didn't seem to work.
I have also tried the whole string "TellStickDuo.fineoffset.temperature.184.Temperature:", but no luck.

[krambriw]

@Mastiff

Yes, there is! I know all about sqlite3...well, all I can find out by googling, at least...
CODE: SELECT ALL
ALTER TABLE oldname RENAME TO newname
Mastiff
Experienced User

Posts: 558
Joined: Thu May 03, 2012 12:43 pm
Top

That will not work since typically 'Stua' is a record in the column tableId. So what is needed is to update all records where 'Stua' is present and to replace the name with 'Stue'

The following script will do it (remember to run the action "refresh table names" or restart the plugin before you see the changes)

Code: Select all

import sqlite3

conn = sqlite3.connect('tempData.db')
c = conn.cursor()
uc = "UPDATE tempData SET tableId = 'Stue' WHERE tableId = 'Stua'"
print uc
c.execute(uc)
conn.commit()
conn.close()
print 'Renaming finished'    
        

[Mastiff]

Walter, Aha, thanks a lot! I'll get to that in the weekend, when I'm changing the full system. Btw I want to backup the stuff before I do. Are there anymore files than the db-files in EG's root directory that I have to backup?

Bongolf, it's not the full string, that's for sure. I'm pretty sure it's that 184. And if it's any consolation I get an error message as well trying to run that command. Try this code:

Code: Select all

import sqlite3

def GetAllData(
    dbName,
    dbTableId
):
    dbName = str(dbName)
    conn = sqlite3.connect(dbName)
    c = conn.cursor()
    t = ((dbTableId),)
    try:
        c.execute('SELECT * FROM '+dbName.split('.')[0]+' WHERE tableId=?', t)
        coll = c.fetchall()
        conn.close()
        return coll
    except:
        conn.close()
        return None

rows = GetAllData('tempData.db', 'YOUR-TABLW-NAME-HERE')

for row in rows:
    print row

In YOUR-SENSOR-NAME-HERE you put the name you've given the table, which is the first line in the capture command. See if that gives you anything. With me it works, but I have to kill EG when I do it because I have a gazillion entries, so ti would probably take a day to write them out...

[krambriw]

@Bongolf

Thanks for the answer, but "184" was the first thing I tested and it didn't seem to work.
I have also tried the whole string "TellStickDuo.fineoffset.temperature.184.Temperature:", but no luck.

Hi, it works here. I have simulated your event and it is captured if you use id=184. I named the table 184 as well.

But to see this in a report you need to do the following at least once

1) Verify that the capturing is correctly configured and has started (you have to drag the event to the macro like in the picture below)

2) Run the action 'RefreshTableNames' or restart the plugin

3) Create a combo report and you should find your table there in the list (184 in my case)

[krambriw]

Are there anymore files than the db-files in EG's root directory that I have to backup?

If you backup the .db files, you are fine!

[Mastiff]

Thanks! Then I'll do that this weekend. It's going to be an interesting weekend, I will have to sort 15 or 16 temperature zones, 12 audio zones and 8 video zones and cange two web pages to reflect that, and I hope to build two new web pages as well, one for audio and one for video!

[bongolf]

@krambriw

Perfekt, thank you!
Now I will see what I can do with the WebSockets.

[bongolf]

I┬┤ve tested the plugin for a couple of days now and its working very good.
But I have a couple of questions.
1. I would like to generate a new report every time a new value is entered to the database. Is that possible? (or would it perhaps be possible to generate an event when a value is entered?)
2. What happens if the number of collected values during one logging interval is lower than the setting for "number of readings for the average calculation"? (One of my sensors is a bit unreliable so i dont really know how often it will send values...)
3. What is the "Use 10 minutes data point spread" supposed to do? If I uncheck it all of my reports becomes empty.

[krambriw]

Hello, nice to hear!

1. I would like to generate a new report every time a new value is entered to the database. Is that possible? (or would it perhaps be possible to generate an event when a value is entered?)

Yes, I do that myself using an event and a macro that creates the report after a short 1 second pause (to allow the database to be closed, maybe not needed but anyway...). The configuration look like this:

2. What happens if the number of collected values during one logging interval is lower than the setting for "number of readings for the average calculation"? (One of my sensors is a bit unreliable so i dont really know how often it will send values...)

It uses the readings you have collected, means if you have gaps, the average will still be calculated on those that exists. If you set the number of readings to use = 10, it will pick the last ten from the database. If only 8 readings exists, it will use those 8.

3. What is the "Use 10 minutes data point spread" supposed to do? If I uncheck it all of my reports becomes empty.

The purpose is to speed up the creation of reports. If you have a lot of data readings from each minute and generate a report for a longer period, it will take considerable long time to create the report compared with using only one reading per 10 minute.
In a normal situation, a 10 minute interval is most of the time acceptable unless you really want to measure fast changes (but then you should also select a shorter period for the report itself).
I do not yet understand why your reports are empty. When I try it works as expected. I have saved a few readings with 10 min interval and then generated reports with and without the check box ticked. I do get the reports with data, only difference is the horizontal scale for the time, in the 10 min variant the scale is with 10 min, the 1 min is with 1 min and in the 1 min chart there are no lines between the data points.
What I could imagine is that you are logging at 10 min interval. If you create a 10 min report, you will see lines between the data points (unless there are gaps). If you instead select a 1 min report to be created, the lines will disappear since the report generator is seeing gaps in your data collection (expecting data every minute but only seeing every 10 min)

See Pictures below