Category Archives: python

Controlling a dumb floor heating pump with Tado smart thermostat and a Philips Hue power plug

My home has a floor heating system on the main floor, which basically has a pump that normally runs 24/7 and has no smart controls or connectivity. If hot water comes from the Central Heating, it’ll heat the floor, otherwise it’ll cool it.

I wanted to save on;

  1. not having the pump running at all times (~35€ / year savings)
  2. not heating the floor when ONLY heating other rooms in the house , like upstairs in the evening/morning (~110€/year savings)

All rooms in my house have a Tado smart thermostat which measures the temperature and humidity, and which can turn on the Central Heating unit if temperatures drop below a preset value. Using a Tado in your home and if have a ‘dumb’ pump like me, you could automate and save a lot of money 🙂 Kitlist:

  1. Philips Hue Bridge
  2. Philips Hue Smart Plug
  3. Tado Thermostat
  4. Raspberry pi zero

First, set up your Pi Zero so it has connectivity (same subnet as the Hue Bridge, and outbound internet access), and install Python3 on it.

Then set up your Hue Bridge and ensure the Hue Smart Plug shows up in your Hue app.

Put the Smart Plug in between your wall outlet and the Floor Heating pump:

Yes, the color of the light makes one wonder what else happens in this room, I’ll blog about my Hydroponics hobby some other time 🙂

Create a Hue API username and determine the plug’s ID

Create a Tado account and set up a heating profile for the room. If you don’t have an old radiator in the room, just plug it onto another one to set it up, then remove it and place it somewhere in the kitchen to make it think it is controlling a radiator, as these plugs are actually mechanical and meant to rotate:

A tado thermostat tricked into controlling the floor heating

Customize my script and run! I scheduled it to run every minute using a cronjob on the pi.

Since we already had Tado and Hue, the total cost of the project was only 35€ (raspberry pi + smart plug), so the investment was well worth it.

Get the code at my git lab or below:

Git lab Tado Floor Heating Pump Controller with Philips Hue Smart Plug

import requests 
import json
import os
import datetime
import time

data = {'grant_type':'password', 
        'scope':'home.user',
        'client_id':'tado-web-app', 
        'client_secret':'wZaRN7rpjn3FoNyF5IFuxg9uMzYJcvOoQ8QWiIqS3hfk6gLhVlG57j5YNoZL2Rtc', #see https://shkspr.mobi/blog/2019/02/tado-api-guide-updated-for-2019/ on how to get this client secret
        'username':'Test@test.com', #your tado login/email
        'password':'MyPassword' #your tado password
        }
hueBaseURL = "http://HUEBRIDGEIPHERE/api/USERNAMEHERE" #the IP and username of your HUE, see https://github.com/tigoe/hue-control for instructions on how to get the username
hueLightID = '6' #the ID of the Hue Power Plug in which you connected your floor heating pump, see https://github.com/tigoe/hue-control for instructions on how to get all ID's
tadoHomeId = '12345' #the home ID of your tado account, see https://shkspr.mobi/blog/2019/02/tado-api-guide-updated-for-2019/ on how to get the ID
tadoHeatingZoneId = '6' #the ID of the tado zone you are heating using a floor heating system, see https://shkspr.mobi/blog/2019/02/tado-api-guide-updated-for-2019/ on how to get the ID
r = requests.post('https://auth.tado.com/oauth/token', data = data) 
j = json.loads(r.text)
TOKEN = j["access_token"]
headers={'Authorization': "Bearer " + TOKEN}
r = requests.get('https://my.tado.com/api/v2/homes/'+tadoHomeId+'/zones/'+tadoHeatingZoneId+'/state', headers=headers)
j = json.loads(r.text)
desiredTemp = j["setting"]["temperature"]["celsius"]
currentTemp = j["sensorDataPoints"]["insideTemperature"]["celsius"]
currentPower = j["activityDataPoints"]["heatingPower"]["percentage"]
print("Status zone "+tadoHeatingZoneId+" : "+str(j["setting"]["power"]))
print("Heating power for zone "+tadoHeatingZoneId+" : "+str(currentPower)+"%")
print("Current temperature in zone "+tadoHeatingZoneId+" : "+str(currentTemp))
print("Target temp in zone "+tadoHeatingZoneId+" : "+str(desiredTemp))
r = requests.get(hueBaseURL+'/lights/'+hueLightID)
j = json.loads(r.text)
pumpState = j["state"]["on"]
print("Floor heating pump state: "+str(pumpState))
if currentPower > 0 and pumpState == False :
    print("PUMP SHOULD BE TURNED ON")
    r = requests.put(hueBaseURL+'/lights/'+hueLightID+'/state', data = '{"on":true}') 
    print(r.content)
    print("PUMP TURNED ON")
elif currentPower <= 0 and pumpState == True :
    print("PUMP SHOULD BE OFF")
    r = requests.put(hueBaseURL+'/lights/'+hueLightID+'/state', data = '{"on":false}')  
    print(r.content)
    print("PUMP TURNED OFF")



Using Azure Function MSI to connect to Azure SQL in Python

Since I had to spend a few hours figuring this out, and all examples/docs are wrong, here’s an example of how to use Python in an Azure Function to connect to an Azure PaaS database without credentials by utilizing the managed identity of the azure function app.

__init__.py:

import logging
import os
import pyodbc
import requests 
import struct
import sys
import azure.functions as func

resource_uri="https://database.windows.net/"
sql_server="XXXXXX.database.windows.net"
sql_database="primary"

def get_bearer_token(resource_uri):
    identity_endpoint = os.environ["IDENTITY_ENDPOINT"]
    identity_header = os.environ["IDENTITY_HEADER"]
    logging.info('identity_endpoint: {}'.format(identity_endpoint))
    logging.info('identity_header : {}'.format(identity_header))
    token_auth_uri = f"{identity_endpoint}?resource={resource_uri}&amp;api-version=2017-09-01"
    head_msi = {'X-IDENTITY-HEADER':identity_header}
    resp = requests.get(token_auth_uri, headers=head_msi)
    access_token = resp.json()['access_token']
    logging.info('response received from token endpoint: {}'.format(access_token))
    return access_token  

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Function Starting')
    try:
        access_token = get_bearer_token(resource_uri)
        accessToken = bytes(access_token, 'utf-8')
        exptoken = b""
        for i in accessToken:
                exptoken += bytes({i})
                exptoken += bytes(1)
        tokenstruct = struct.pack("=i", len(exptoken)) + exptoken  
        conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=tcp:{},1433;Database={}".format(sql_server,sql_database), attrs_before = { 1256:bytearray(tokenstruct) })
        logging.info('connected to {} on {}'.format(sql_server,sql_database))
        cursor = conn.cursor()
        cursor.execute("select @@version")
        row = cursor.fetchall()
        logging.info('sql data: {}'.format(row[0])) 
        logging.info('finished')              
    except BaseException as error:
        logging.info('An exception occurred: {}'.format(error))   
    return func.HttpResponse("done!")

requirements.txt:

astroid==2.4.2
azure-functions==1.2.1
certifi==2020.6.20
chardet==3.0.4
colorama==0.4.3
idna==2.9
isort==4.3.21
lazy-object-proxy==1.4.3
mccabe==0.6.1
pylint==2.5.3
pyodbc==4.0.30
requests==2.24.0
six==1.15.0
toml==0.10.1
urllib3==1.25.9
wrapt==1.12.1

Uploading a file to onedrive for business with Python

For a Raspberry Pi project that’ll take a number of pictures of my house for an as of yet unknown period of time I’m sharing my very first Python script with you.

All it has to do is upload all files from a given folder to a given Onedrive for Business path, as obviously the Pi can’t store much data on its tiny SD card. You’ll need to register an azure ad app and give it the appropriate permissions. You’ll have to consent to the application once (url format = https://login.microsoftonline.com/common/adminconsent?client_id={client-id}).

Then the schedule below Python script on your Pi, it will retrieve an Azure token without the need for external libraries, parse the directory and upload everything in it to the given onedrive for business URL, simple as that! It can also be used for Sharepoint or Teams by adjusting the path.

import requests 
import json
directory = r"c:\temp\uploads"
data = {'grant_type':"client_credentials", 
        'resource':"https://graph.microsoft.com", 
        'client_id':'XXXXX', 
        'client_secret':'XXXXX'} 
URL = "https://login.windows.net/YOURTENANTDOMAINNAME/oauth2/token?api-version=1.0"
r = requests.post(url = URL, data = data) 
j = json.loads(r.text)
TOKEN = j["access_token"]
URL = "https://graph.microsoft.com/v1.0/users/YOURONEDRIVEUSERNAME/drive/root:/fotos/HouseHistory"
headers={'Authorization': "Bearer " + TOKEN}
r = requests.get(URL, headers=headers)
j = json.loads(r.text)
print("Uploading file(s) to "+URL)
for root, dirs, files in os.walk(directory):
    for filename in files:
        filepath = os.path.join(root,filename)
        print("Uploading "+filename+"....")
        fileHandle = open(filepath, 'rb')
        r = requests.put(URL+"/"+filename+":/content", data=fileHandle, headers=headers)
        fileHandle.close()
        if r.status_code == 200 or r.status_code == 201:
            #remove folder contents
            print("succeeded, removing original file...")
            os.remove(os.path.join(root, filename)) 
print("Script completed")
raise SystemExit