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()
