# /var/www/py/flora004.py ***************************************************** # Created by: HARB rboek2@gmail.com februar 2020 GPL copyrights # Prepaired and optimized for use with Python 3 # IMPORT LIBRARIES install like: pip install --upgrade psycopg2 --user ******** import platform #needed to check if we are using Python3 import psycopg2 #Needed to communicate with any PostgreSQL database import datetime #Needed to create a timestamp for the datarecord import time #Needed to be able to perform the request every 5 minutes import requests #Simplest internet reader for Python3 import urllib #Needed to load the contents of a webpage into a variable python_version=platform.python_version() #Actual version request print ("De gebruikte versie van Python is:", python_version) #Show to user # paswoord = input("Enter password voor de database: ") #To avoid exposure paswoord = "Ha-8-H+" while True: #Meaning this loop will run forever ******************************* print (" ") #Just print an empty line # This is a list of all the data that will be written into the database when # stations are not activated. All stations must be asked in one sequence now = datetime.datetime.now() #Read the current system time stampz = now.strftime("%Y-%m-%d %H:%M:%S") #Format to a database format #*** COUVEUSE 1 parameters **************************************** propKlok = "1999-12-31 23:59:59"; #DateTime DS1307 propSoilTemp = 101; #Soil temperature in Celsius DS18B20 propAirTemp = 101; #Air temperature degree Celcius DHT22 propAirHum = -1; #Air humidity percentage DHT22 propRelay1 = 0; #Status high=off, low=on WATER RELAY1 propRelay2 = 0; #Status 1=off, 0=on GROEILED1 RELAY2 propRelay3 = 0; #Status HI=off, LW=on VERWARMING RELAY3 propRelay4 = 0; #Status HI=off, LOW=on GROEILED2 RELAY4 propWaterProg = 4; #1=off 2=on 3=auto WATER RELAY1 propWaterON = 0; #If CAPAC reaches this treshold RELAY1 propWaterSecs = 0; #How many seconds to keep ON RELAY1 propLED1Prog = 4; #1=off 2=on 3=auto VERWARMING RELAY2 propLED1hours = 0; #Temperature/10 airheater ON RELAY2 propHeatProg = 4; #Heatcoil 1=off 2=on 3=auto RELAY3 propHeatON = 0; #Temperature/10 heatcoil ON RELAY3 propHeatOFF = 0; #Temperature/10 heatcoil OFF RELAY3 propLED2prog = 4; #GrowLED 1=off 2=on 3=auto RELAY4 propLED2hours = 0; #Number of hours around noon RELAY4 propCapac1 = 0; #Moisture measured by CAPAC1 propCapac1Min = 0; #Minimum moisture in sequence by CAPAC1 propCapac1Max = 0; #Maximum moisture in sequence by CAPAC1 propCapac2 = 0; #Moisture measured by CAPAC2 propCapac2Min = 0; #Minimum moisture in sequence by CAPAC2 propCapac2Max = 0; #Maximum moisture in sequence by CAPAC2 propCapac3 = 0; #Moisture measured by CAPAC3 propCapac3Min = 0; #Minimum moisture in sequence by CAPAC3 propCapac3Max = 0; #Maximum moisture in sequence by CAPAC3 #*** HOBBYKAS parameters ****************************************** hobbyklok = "1999-12-31 23:59:59"; #DateTime DS1307 hobbyPIR = 2; #ON=1=HIGH or OFF=0=LOW movement PIR hobbyLDR = 2222; #Light measurement, 0-1023 LDR hobbyTemp = 101; #Air temperature degree Celcius DHT22 hobbyHum = 101; #Air humidity percentage DHT22 hobbyRelay1 = 0; #WERKVERLICHTING high=off low=on RELAY1 hobbyRelay2 = 0; #VERWARMING high=off low=on RELAY2 hobbyRelay3 = 0; #GROEILED high=off low=on RELAY3 hobbyRelay4 = 0; #WATER high=off low=on RELAY4 hobbyRelay5 = 0; #AUDIO high=off low=on RELAY5 hobbyLightProg = 4; #WERKVERLICHTING 1=off 2=on 3=auto RELAY1 hobbyLightON = 0; #If LDR reaches this treshold * 10 RELAY1 hobbyLightSecs = 0; #Seconds*10 werkverlichting on RELAY1 hobbyHeatProg = 4; #VERWARMING: 1=off 2=on 3=auto RELAY2 hobbyHeatON = 0; #Celsius/10 verwarming switch on RELAY2 hobbyHeatOFF = 0; #Celsius/10 verwarming switch off RELAY2 hobbyLEDProg = 4; #GROEILED 1=off 2=on 3=auto RELAY3 hobbyLEDHours = 0; #Hours around noon to switch on RELAY3 hobbyWaterProg = 4; #WATER 1=off 2=on 3=auto RELAY4 hobbyWaterSecs = 0; #Seconds*10 to keep watering RELAY4 hobbyAudioProg = 4; #AUDIO 1=off 2=on 3=auto RELAY5 hobbyAudioMins = 0; #Minutes*10 to keep audio playing RELAY5 #*** VOORTUIN parameters ****************************************** tuinklok = "2003-01-01 00:00:01" tuingrondtemp = 99.99 tuinldr = -1 tuinrelay0 = 8 tuinrelay1 = 8 tuinrelay2 = 8 tuinrelay3 = 8 tuinrelay4 = 8 tuinldrprog = 1 tuinldrtreshold = 1 tuinwaterdelay = 1 tuinwater1prog = 1 tuinwater1spray = 1 tuinwater2prog = 1 tuinwater2spray = 1 tuinwater3prog = 1 tuinwater3spray = 1 tuinwater4prog = 1 tuinwater4spray = 1 tuintimer = 0 conn = psycopg2.connect(host="localhost",database="robotigs", user="richard", password=paswoord) #Create a database connection cur1 = conn.cursor() #Create a thread to the database cur1.execute("""SELECT * FROM florastations ORDER BY staid""") #Define database action rows = cur1.fetchall() #Perform database action for row in rows: #Scan all the stations listed in the file #*** COUVEUSE *********************************************************** if row[2]==True and row[0]==1: #Meaning this station is set to be activ link = "http://" + row[3] #Calculate the entire link try: #Make a provision for if the station does not answer properly r = requests.get(link, timeout=5.0000) myfile = (r.text) print (" ") #Just print an empty line print (myfile) datalist = myfile.split(' ') #Splice the answer into an array propKlok = datalist[0] + "-" + datalist[1] + "-" + datalist[2] + " " + datalist[3] + ":" + datalist[4] + ":" + datalist[5]; #DateTime DS1307 propSoilTemp = datalist[6]; #Soil temperature in Celsius DS18B20 propAirTemp = datalist[7]; #Air temperature degree Celcius DHT22 propAirHum = datalist[8]; #Air humidity percentage DHT22 propRelay1 = datalist[9]; #Status HIGH=off, LOW=on WATER RELAY1 propRelay2 = datalist[10]; #Status 1=off, 0=on AIRHEATER RELAY2 propRelay3 = datalist[11]; #Status HI=off, LW=on HEATCOIL RELAY3 propRelay4 = datalist[12]; #Status HI=off, LOW=on GROWLED RELAY4 propWaterProg = datalist[13]; #Watering: 1=off 2=on 3=auto RELAY1 propWaterON = datalist[14]; #If CAPAC reaches this treshold RELAY1 propWaterSecs = datalist[15]; #How many seconds to keep ON RELAY1 propLED1Prog = datalist[16]; #Air heater 1=off 2=on 3=auto RELAY2 propLED1hours = datalist[17]; #Temperature/10 airheater ON RELAY2 propHeatProg = datalist[18]; #Heatcoil 1=off 2=on 3=auto RELAY3 propHeatON = datalist[19]; #Temperature/10 heatcoil ON RELAY3 propHeatOFF = datalist[20]; #Temperature/10 heatcoil OFF RELAY3 propLED2prog = datalist[21]; #GrowLED 1=off 2=on 3=auto RELAY4 propLED2hours = datalist[22]; #Number of hours around noon RELAY4 propCapac1 = datalist[23]; #Moisture measured by CAPAC1 propCapac1Min = datalist[24]; #Minimum moisture in sequence by CAPAC1 propCapac1Max = datalist[25]; #Maximum moisture in sequence by CAPAC1 propCapac2 = datalist[26]; #Moisture measured by CAPAC2 propCapac2Min = datalist[27]; #Minimum moisture in sequence by CAPAC2 propCapac2Max = datalist[28]; #Maximum moisture in sequence by CAPAC2 propCapac3 = datalist[29]; #Moisture measured by CAPAC3 propCapac3Min = datalist[30]; #Minimum moisture in sequence by CAPAC3 propCapac3Max = datalist[31]; #Maximum moisture in sequence by CAPAC3 except: #If the station does not answer properly use this pass #Just do nothing if this station does not answer properly if propAirTemp == "": #Meaning the sensor reading was not ok propCapac3Max = 1 #Watering: 1=off 2=on 3=auto RELAY1 print (stampz, " ", row[1], " " , row[3] , " propKlok=" , propKlok , " propSoilTemp=" , propSoilTemp , " propAirTemp=" , propAirTemp , " propAirHum=" , propAirHum , " propRelay1=" , propRelay1 , " propRelay2=" , propRelay2 , " propRelay3=" , propRelay3 , " propRelay4=" , propRelay4 , " propWaterProg=" , propWaterProg , " propWaterON=" , propWaterON , " propWaterSecs=" , propWaterSecs , " propLED1Prog=" , propLED1Prog , " propLED1hours=" , propLED1hours , " propHeatProg=" , propHeatProg , " propHeatON=" , propHeatON , " propHeatOFF=" , propHeatOFF , " propLED2Prog=" , propLED2prog , " propLED2hours=" , propLED2hours , " propCapac1=" , propCapac1 , " propCapac1Min=" , propCapac1Min , " propCapac1Max=" , propCapac1Max , " propCapac2=" , propCapac2 , " propCapac2Min=" , propCapac2Min , " propCapac2Max=" , propCapac2Max , " propCapac3=" , propCapac3 , " propCapac3Min=" , propCapac3Min , " propCapac3Max=" , propCapac3Max ) #*** KWEEKKAS *********************************************************** if row[2]==True and row[0]==2: #Meaning this station is set to be activ link = "http://" + row[3] + ":9000" #Calculate the entire link try: #Make a provision for if the station does not answer properly r = requests.get(link, timeout=5.0000) myfile = (r.text) print (" ") #Just print an empty line print (myfile) datalist = myfile.split(' ') #Splice the answer into an array #First but not least tell the client our current sensor values hobbyklok = datalist[0] + " " + datalist[1]; #DateTime DS1307 hobbyPIR = datalist[2]; #ON=1=HIGH or OFF=0=LOW movement PIR hobbyLDR = datalist[3]; #Light measurement, 0-1023 LDR hobbyTemp = datalist[4]; #Air temperature degree Celcius DHT22 hobbyHum = datalist[5]; #Air humidity percentage DHT22 #Next tell the client about the status of all relay hobbyRelay1 = datalist[6]; #WERKVERLICHTING high=off low=on RELAY1 hobbyRelay2 = datalist[7]; #VERWARMING high=off low=on RELAY2 hobbyRelay3 = datalist[8]; #GROEILED high=off low=on RELAY3 hobbyRelay4 = datalist[9]; #WATER high=off low=on RELAY4 hobbyRelay5 = datalist[10]; #AUDIO high=off low=on RELAY5 #First receive all EEPROM values hobbyLightProg = datalist[11]; #WERKVERLICHTING 1=off 2=on 3=auto RELAY1 hobbyLightON = datalist[12]; #If LDR reaches this treshold * 10 RELAY1 hobbyLightSecs = datalist[13]; #Seconds*10 werkverlichting on RELAY1 hobbyHeatProg = datalist[14]; #VERWARMING: 1=off 2=on 3=auto RELAY2 hobbyHeatON = datalist[15]; #Celsius/10 verwarming switch on RELAY2 hobbyHeatOFF = datalist[16]; #Celsius/10 verwarming switch off RELAY2 hobbyLEDProg = datalist[17]; #GROEILED 1=off 2=on 3=auto RELAY3 hobbyLEDHours = datalist[18]; #Hours around noon to switch on RELAY3 hobbyWaterProg = datalist[19]; #WATER 1=off 2=on 3=auto RELAY4 hobbyWaterSecs = datalist[20]; #Seconds*10 to keep watering RELAY4 hobbyAudioProg = datalist[21]; #AUDIO 1=off 2=on 3=auto RELAY5 hobbyAudioMins = datalist[22]; #Minutes*10 to keep audio playing RELAY5 except: #If the station does not answer properly use this pass #Just do nothing if this station does not answer properly if hobbyTemp == "": #Meaning the sensor reading was not ok hobbyAudioProg = 1 #Amplifier: 1=off 2=on RELAY4 print (stampz, " ", row[1], " " , row[3] , " hobbyklok=" , hobbyklok , " hobbyPIR=" , hobbyPIR , " hobbyLDR=" , hobbyLDR , " hobbyTemp=" , hobbyTemp , " hobbyHum=" , hobbyHum , " hobbyRelay1=" , hobbyRelay1 , " hobbyRelay2=" , hobbyRelay2 , " hobbyRelay3=" , hobbyRelay3 , " hobbyRelay4=" , hobbyRelay4 , " hobbyRelay5=" , hobbyRelay5 , " hobbyLightProg=" , hobbyLightProg , " hobbyLightON=" , hobbyLightON , " hobbyLightSecs=" , hobbyLightSecs , " hobbyHeatProg=" , hobbyHeatProg , " hobbyHeatON=" , hobbyHeatON , " hobbyHeatOFF=" , hobbyHeatOFF , " hobbyLEDProg=" , hobbyLEDProg , " hobbyLEDHours=" , hobbyLEDHours , " hobbyWaterProg=" , hobbyWaterProg , " hobbyWaterSecs=" , hobbyWaterSecs , " hobbyAudioProg=" , hobbyAudioProg , " hobbyAudioMins=" , hobbyAudioMins ) #*** VOORTUIN *********************************************************** if row[2]==True and row[0]==4: #Meaning this station is set to be activ link = "http://" + row[3] #Calculate the entire link try: #Make a provision for if the station does not answer properly r = requests.get(link, timeout=5.0000) myfile = (r.text) myfile = myfile[1:] #Slice away leading space myfile = myfile[:-2] #Slice de laatste 2 caracters CRLF print (" ") #Just print an empty line print (myfile) datalist = myfile.split(' ') #Splice the answer into an array tuinklok = str(datalist[0]).zfill(4) + "-" + str(datalist[1]).zfill(2) + "-" + str(datalist[2]).zfill(2) + " " + str(datalist[3]).zfill(2) + ":" + str(datalist[4]).zfill(2) + ":" + str(datalist[5]).zfill(2) #Assign the data tuingrondtemp = datalist[6] #Assign the data tuinldr = datalist[7] #Assign the data tuinrelay0 = datalist[8] #Assign the data tuinrelay1 = datalist[9] #Assign the data tuinrelay2 = datalist[10] #Assign the data tuinrelay3 = datalist[11] #Assign the data tuinrelay4 = datalist[12] #Assign the data tuinldrprog = datalist[13] #Assign the data tuinldrtreshold = datalist[14] #Assign the data tuinwaterdelay = datalist[15] #Assign the data tuinwater1prog = datalist[16] #Assign the data tuinwater1spray = datalist[17] #Assign the data tuinwater2prog = datalist[18] #Assign the data tuinwater2spray = datalist[19] #Assign the data tuinwater3prog = datalist[20] #Assign the data tuinwater3spray = datalist[21] #Assign the data tuinwater4prog = datalist[22] #Assign the data tuinwater4spray = datalist[23] #Assign the data tuintimer = datalist[24] #Assign the data except: #If the station does not answer properly use this pass #Just do nothing if this station does not answer properly if tuinldr == "": #Meaning the sensor reading was not ok tuinklok = "2003-01-01 00:00:01" tuingrondtemp = 99.99 tuinldr = -1 tuinrelay0 = 8 tuinrelay1 = 8 tuinrelay2 = 8 tuinrelay3 = 8 tuinrelay4 = 8 tuinldrprog = 1 tuinldrtreshold = 1 tuinwaterdelay = 1 tuinwater1prog = 1 tuinwater1spray = 1 tuinwater2prog = 1 tuinwater2spray = 1 tuinwater3prog = 1 tuinwater3spray = 1 tuinwater4prog = 1 tuinwater4spray = 1 tuintimer = 0 print (stampz, " ", row[1], " " , row[3] , "tuinklok=" , tuinklok , " tuingrondtemp=" , tuingrondtemp , " tuinldr=" , tuinldr , " tuinrelay0=" , tuinrelay0 , " tuinrelay1=" , tuinrelay1 , " tuinrelay2=" , tuinrelay2 , " tuinrelay3=" , tuinrelay3 , " tuinrelay4=" , tuinrelay4 , " tuinldrprog=" , tuinldrprog , " tuinldrtreshold=", tuinldrtreshold , " tuinwaterdelay=" , tuinwaterdelay , " tuinwater1prog=" , tuinwater1prog , " tuinwater1spray=", tuinwater1spray , " tuinwater2prog=" , tuinwater2prog , " tuinwater2spray=", tuinwater2spray , " tuinwater3prog=" , tuinwater3prog , " tuinwater3spray=", tuinwater3spray , " tuinwater4prog=" , tuinwater4prog , " tuinwater4spray=", tuinwater4spray , " tuintimer=" , tuintimer ) # WRITE DATA INTO DATABASE FLORAMEAS *************************************** #Now we have read all stations we can write the data into the database #First determin the first available free key in the file florameas cur2 = conn.cursor() cur2.execute("""SELECT mesid FROM florameas ORDER BY mesid DESC LIMIT 1""") #Define database action rows2 = cur2.fetchall() #Read the latest entry of this table nextkey = 1 #Just to cath an empty table of measurements for row2 in rows2: nextkey = row2[0] nextkey += 1 #Now we can create a new measurement in the database table florameas cur2.execute(""" INSERT INTO florameas ( mesid , stamp , propKlok , propSoilTemp , propAirTemp , propAirHum , propRelay1 , propRelay2 , propRelay3 , propRelay4 , propWaterProg , propWaterON , propWaterSecs , propLED1Prog , propLED1hours , propHeatProg , propHeatON , propHeatOFF , propLED2prog , propLED2hours , propCapac1 , propCapac1Min , propCapac1Max , propCapac2 , propCapac2Min , propCapac2Max , propCapac3 , propCapac3Min , propCapac3Max , hobbyklok , hobbyPIR , hobbyLDR , hobbyTemp , hobbyHum , hobbyRelay1 , hobbyRelay2 , hobbyRelay3 , hobbyRelay4 , hobbyRelay5 , hobbyLightProg , hobbyLightON , hobbyLightSecs , hobbyHeatProg , hobbyHeatON , hobbyHeatOFF , hobbyLEDProg , hobbyLEDHours , hobbyWaterProg , hobbyWaterSecs , hobbyAudioProg , hobbyAudioMins , tuinklok , tuingrondtemp , tuinldr , tuinrelay1 , tuinrelay2 , tuinrelay3 , tuinrelay4 , tuinldrtreshold , tuinwaterdelay , tuinwater1prog , tuinwater1spray , tuinwater2prog , tuinwater2spray , tuinwater3prog , tuinwater3spray , tuinwater4prog , tuinwater4spray , tuinrelay0 , tuinldrprog , tuintimer ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )""",(nextkey, stampz , propKlok , propSoilTemp , propAirTemp , propAirHum , propRelay1 , propRelay2 , propRelay3 , propRelay4 , propWaterProg , propWaterON , propWaterSecs , propLED1Prog , propLED1hours , propHeatProg , propHeatON , propHeatOFF , propLED2prog , propLED2hours , propCapac1 , propCapac1Min , propCapac1Max , propCapac2 , propCapac2Min , propCapac2Max , propCapac3 , propCapac3Min , propCapac3Max , hobbyklok , hobbyPIR , hobbyLDR , hobbyTemp , hobbyHum , hobbyRelay1 , hobbyRelay2 , hobbyRelay3 , hobbyRelay4 , hobbyRelay5 , hobbyLightProg , hobbyLightON , hobbyLightSecs , hobbyHeatProg , hobbyHeatON , hobbyHeatOFF , hobbyLEDProg , hobbyLEDHours , hobbyWaterProg , hobbyWaterSecs , hobbyAudioProg , hobbyAudioMins , tuinklok , tuingrondtemp , tuinldr , tuinrelay1 , tuinrelay2 , tuinrelay3 , tuinrelay4 , tuinldrtreshold , tuinwaterdelay , tuinwater1prog , tuinwater1spray , tuinwater2prog , tuinwater2spray , tuinwater3prog , tuinwater3spray , tuinwater4prog , tuinwater4spray , tuinrelay0 , tuinldrprog , tuintimer )) conn.commit() cur2.close() #Close this thread to the database cur1.close() #Close this thread to the database conn.close() #Close this connection to the database time.sleep(300) #Just wait 5 minutes before performing this read out again