Reference to energy costs in Domoticz config

[Broomer23]

Hi,

In the configuration section of domoticz you can enter costs for energy (T1, R1 and gas) Is there anyway to refer to these values in a bash or python script? Should be a variable name.

When I write a script for daily energy costs I do not want to put the costs inside the script but instead refer to the values in the config section.

I hope I made my point clear. I can not find the answer in this forum or elsewhere.

[waaren]

In the configuration section of domoticz you can enter costs for energy (T1, R1 and gas) Is there anyway to refer to these values in a bash or python script? Should be a variable name.

One way to do this (in bash):

Code: Select all

sqlite=/usr/local/bin/sqlite3              # should be installed on your system (sudo apt install sqlite3)
domoticzDirectory=/opt/domoticz      # change to your domoticz dir
domoticzDatabase=domoticz.db

costEnergy=$(sudo $sqlite $domoticzDirectory/$domoticzDatabase "select nvalue from preferences where key = 'CostEnergy'")
costEnergyT2=$(sudo $sqlite $domoticzDirectory/$domoticzDatabase "select nvalue from preferences where key = 'CostEnergyT2'")
costEnergyR1=$(sudo $sqlite $domoticzDirectory/$domoticzDatabase "select nvalue from preferences where key = 'CostEnergyR1'")
costEnergyR2=$(sudo $sqlite $domoticzDirectory/$domoticzDatabase "select nvalue from preferences where key = 'CostEnergyR2'")
costGas=$(sudo $sqlite $domoticzDirectory/$domoticzDatabase "select nvalue from preferences where key = 'CostGas'")
costWater=$(sudo $sqlite $domoticzDirectory/$domoticzDatabase "select nvalue from preferences where key = 'CostWater'")


echo costEnergy   : € $(echo "$costEnergy 10000" | awk '{printf "%.4f \n", $1/$2}')
echo costEnergyT2 : € $(echo "$costEnergyT2 10000" | awk '{printf "%.4f \n", $1/$2}')
echo costEnergyR1 : € $(echo "$costEnergyR1 10000" | awk '{printf "%.4f \n", $1/$2}')
echo costEnergyR2 : € $(echo "$costEnergyR2 10000" | awk '{printf "%.4f \n", $1/$2}')
echo costGas      : € $(echo "$costGas 10000" | awk '{printf "%.4f \n", $1/$2}')
echo costWater    : € $(echo "$costWater 10000" | awk '{printf "%.4f \n", $1/$2}')

[Broomer23]

Ok that is a smart approach via SQL query.
I didn't think of that because I thought there would be a defined variable,
but this is very helpful, thanks very much. thanks Waaren!!

[Broomer23]

some additional remarks:

The path to SQLITE3 is different in my environment /etc/bin/sqlite3, so no local in my path.

In the script sudo is used, So I have to find a way to approach the domoticz DB without SUDO, because the script will run unattended.

[waaren]

The path to SQLITE3 is different in my environment /etc/bin/sqlite3, so no local in my path.

That's why I made it configurable

In the script sudo is used, So I have to find a way to approach the domoticz DB without SUDO, because the script will run unattended.

Unattended script can also use sudo. It is just a command like many other. If you can access the domoticz database without it; even better but it will not harm anything if you need it.

[Broomer23]

Indeed, I also use as much as possible variables in my scripts so it is flexible.

that is also why I wanted to avoid putting the costs directly in my script.

Your help is really appreciated Waaren, thanks again!

[aleph0]

I've had the same need in lua, so I wrote the following function :

Code: Select all

function getPref(dbName,key)
    -- Retourne une valeur depuis les préférences de domoticz
    -- Key doit correspondre à l'une des lignes de la table preference
    sql='sqlite3 '..dbName..' "select nValue,sValue from Preferences where Key=\''..tostring(key)..'\'"'

    nV,sV=string.match(os.capture(sql),"(.*)|(.*)")
    -- print("getpref",sql,nV,sV)
    return tonumber(nV),tostring(sV)
end

it returns the nValue and sValue of the preference specified in "Key", where key can be one of the following :
DB_Version LightHistoryDays MeterDividerEnergy MeterDividerGas MeterDividerWater RandomTimerFrame ElectricVoltage CM113DisplayType 5MinuteHistoryDays SensorTimeout SensorTimeoutNotification UseAutoUpdate UseAutoBackup CostEnergy CostEnergyT2 CostGas CostWater UseEmailInNotifications EmailPort EmailAsAttachment DoorbellCommand SmartMeterType EnableTabLights EnableTabTemp EnableTabWeather EnableTabUtility EnableTabCustom EnableTabScenes EnableTabFloorplans NotificationSensorInterval NotificationSwitchInterval RemoteSharedPort Language DashboardType MobileType WindUnit TempUnit SecStatus SecOnDelay AuthenticationMethod ReleaseChannel RaspCamParams UVCParams AcceptNewHardware ZWavePollInterval ZWaveEnableDebug ZWaveNetworkKey ZWaveEnableNightlyNetworkHeal BatteryLowNotification AllowWidgetOrdering ActiveTimerPlan HideDisabledHardwareSensors WebTheme FloorplanPopupDelay FloorplanFullscreenMode FloorplanAnimateZoom FloorplanShowSensorValues FloorplanShowSwitchValues FloorplanShowSceneNames FloorplanRoomColour FloorplanActiveOpacity FloorplanInactiveOpacity TempHome TempAway TempComfort DegreeDaysBaseTemperature HTTPURL ShowUpdateEffect ShortLogInterval DisplayPowerUsageInkWhGraph Location ClickatellEnabled ClickatellAPI ClickatellFrom ClickatellPassword ClickatellTo ClickatellUser EmailFrom EmailServer EmailTo EmailPassword EmailUsername HTTPEnabled HTTPField1 HTTPField2 HTTPField3 HTTPField4 HTTPTo KodiIPAddress KodiEnabled KodiPort KodiTimeToLive LmsPlayerMac LmsDuration LmsEnabled NMAAPI NMAEnabled ProwlAPI ProwlEnabled PushALotAPI PushALotEnabled PushbulletAPI PushbulletEnabled PushoverAPI PushoverUser PushoverEnabled WebUserName WebPassword WebLocalNetworks SecPassword ProtectionPassword MyDomoticzUserId MyDomoticzSubsystems OneWireSensorPollPeriod OneWireSwitchPollPeriod HTTPPostContentType HTTPPostData SendErrorNotifications SendErrorsAsNotification HTTPPostHeaders PushsaferAPI PushsaferEnabled LogEventScriptTrigger Title CostEnergyR1 CostEnergyR2 GCMEnabled PushsaferImage WeightUnit DisableDzVentsSystem DzVentsLogLevel IFTTTEnabled EmailEnabled IFTTTAPI EnableEventScriptSystem TelegramAPI TelegramChat TelegramEnabled WebRemoteProxyIPs EventSystemLogFullURL

It does not answer directly to your question, but maybe it'll be usefull to somebody

[waaren]

Code: Select all

    nV,sV=string.match(os.capture(sql),"(.*)|(.*)")

I don't think os.capture is included in the Lua os.functions so you will have to include that function as well before it will work.

[aleph0]

You're right !!

Here is the full code :

Code: Select all

function os.capture(cmd, raw)
    local f = assert(io.popen(cmd, 'r'))
    local s = assert(f:read('*a'))
    f:close()
    if raw then return s end
    s = string.gsub(s, '^%s+', '')
    s = string.gsub(s, '%s+$', '')
    s = string.gsub(s, '[\n\r]+', ' ')
    return s
end

function getPref(dbName,key)
    -- Retourne une valeur depuis les préférences de domoticz
    -- Key doit correspondre à l'une des lignes de la table preference
    sql='sqlite3 '..dbName..' "select nValue,sValue from Preferences where Key=\''..tostring(key)..'\'"'

    nV,sV=string.match(os.capture(sql),"(.*)|(.*)")
    -- print("getpref",sql,nV,sV)
    return tonumber(nV),tostring(sV)
end