def main(): """Declare variables""" db = 'SoilSensors' server = 'SQL DB IP' uname = 'SQLDB USERNAME' pwd = 'SQLDB PASSWORD' FTPAdr = 'FTP IP' FTPUn = 'FTP Uname' FTPpwd = 'FTP Pass' querySensors = "SELECT sensor FROM SoilSensors.Sensors" queryReadings = "SELECT Sensors.sensor, Readings.reading, Readings.inserted \ Readings.sensor_id \ FROM SoilSensors.Readings INNER JOIN SoilSensors.Sensors \ ON Readings.sensor_id = Sensors.id;" dirc = os.path.join('save dirc path') UploadPath = os.path.join('//files//') """connect to Datasources to gather data""" cnx = conct.CFSQLConnect(db, uname, pwd, server) Data = cnx.queryMySQL(queryReadings) sensors = cnx.queryMySQL(querySensors) sensors = list(sensors.sensor.unique()) """Produce plot and save to HTML file to display in HASS""" files = [] truncate = "TRUNCATE TABLE SoilSensors.Plots;" cnx.ExecuteMySQL(truncate) for sensor in sensors: fname = 'Sensor' + str(sensor) + 'Plot.html' files.append(dirc + fname) df = Data[Data.sensor == sensor] plot=plotLine(df,'inserted','reading','Moisture',\ fname,'Datetime','Moisture Pct',dirc,'blue') WritetoHTML(plot, fname, dirc) """Add URL to DB Table""" sensor_id = Data[Data.sensor == sensor] sensor_id = sensor_id.sensor_id.iloc[0] URL = "http://HOST/" + fname queryInsertURL = "INSERT INTO SoilSensors.Plots (sensor_id, sensor, URL) \ VALUES (%s, '%s', '%s')" cnx.ExecuteMySQL(queryInsertURL % (sensor_id, sensor, URL)) """connect to HASS Pi to upload plot via FTP""" FTPObj = FTPConnt.FTPConnectMod(FTPAdr, FTPUn, FTPpwd) FTPObj.UploadFile(fname, dirc + fname, UploadPath) for file in files: if os.path.exists(file): os.remove(file) """Remove readings older then a month""" query = "DELETE FROM SoilSensors.Readings WHERE \ inserted < (NOW() - INTERVAL 30 DAY);" cnx.ExecuteMySQL(query)
def main(): """Declare variables""" db = 'SoilSensors' server = 'SQL DB IP' uname = 'SQLDB USERNAME' pwd = 'SQLDB PASSWORD' querySensors = "SELECT id as sensor_id FROM SoilSensors.Sensors" queryReadings = "SELECT Readings.reading, Readings.inserted \ FROM SoilSensors.Readings INNER JOIN SoilSensors.Sensors \ ON Readings.sensor_id = Sensors.id \ WHERE Sensors.id = %s \ ORDER BY Readings.inserted DESC limit 1;" queryPlantFacts = "SELECT lastWatered, plant_id from SoilSensors.FactPlants \ WHERE sensor_id = %s" queryPlants = "SELECT minSoilMoisture, maxSoilMoisture \ from SoilSensors.DimPlants WHERE id = %s" updateQuery = "UPDATE SoilSensors.FactPlants SET lastWatered = NOW() \ WHERE sensor_id = %s AND plant_id = %s;" PinQuery = "SELECT * FROM SoilSensors.Pins WHERE sensor_id = %s;" """connect to Datasources to gather data""" cnx = conct.CFSQLConnect(db, uname, pwd, server) sensors = cnx.queryMySQL(querySensors) sensors = list(sensors.sensor_id) """Check each Sensors last moisture reading""" for sensor in sensors: plantFacts = cnx.queryMySQL(queryPlantFacts % (sensor)) Reading = cnx.queryMySQL(queryReadings % (sensor)) plant_id = plantFacts.plant_id.iloc[0] lastWatered = plantFacts.lastWatered.iloc[0] lastReading = Reading.inserted.iloc[0] moisture = Reading.reading.iloc[0] moistureMinMax = cnx.queryMySQL(queryPlants % (plant_id)) minMoisture = moistureMinMax.minSoilMoisture.iloc[0] maxMoisture = moistureMinMax.maxSoilMoisture.iloc[0] pin = cnx.queryMySQL(PinQuery % (sensor)) pin = pin.pin.iloc[0] pin = int(pin) relay = gpio.LED(pin, active_high=False) if lastReading > lastWatered: if (moisture <= minMoisture): relay.on() time.sleep(5) relay.off() cnx.ExecuteMySQL(updateQuery % (sensor, plant_id)) else: sys.exit()