1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160
|
# -*- coding: utf-8 -*-
import mysql.connector
from datetime import datetime, timedelta
# script exécuté une fois par jour (à 00:07:30 ?)
# version du 30/05/2022 (a)
db = mysql.connector.connect(
host="localhost",
user="xxx",
password="yyy",
database="zzz"
)
cur = db.cursor()
# -1- -1- -1- -1- -1- -1- -1- -1- -1- -1- -1- -1- -1- -1-
# partie qui ne nous intéresse pas ici.
# -2- -2- -2- -2- -2- -2- -2- -2- -2- -2- -2- -2- -2- -2-
# mise à jour de la table 't_quotidien' avec les valeurs quotidiennes de la table archive
# 2-1 La dernière date de 't_quotidien'
sql="SELECT Max(t_quotidien.q_date) AS X FROM t_quotidien;"
cur.execute(sql)
x = cur.fetchone()
derDateQuot = x[0]
print("derDateQuot =", end =" ")
print (derDateQuot )
derDateQuot+= timedelta(days=1) # on commence après la dernière date !
# 2-2 la dernière 'date_jour' de 'archive'
sql="SELECT Max(archive.date_jour) AS X FROM archive;"
cur.execute(sql)
x = cur.fetchone()
derDateArch = x[0]
# comme c'est théoriquement le jour en cours, on recule à la veille
print("dernière date archive trouvée : ", end = ' ')
print(derDateArch)
derDateArch -= timedelta(days=1)
print("on termine la veille : ", end = " ")
print(derDateArch)
# 2-3 Boucle entre derDateQuot et derDateArch, insertion des enregistrements
delta=derDateArch-derDateQuot
nbreJours=delta.days + 1
print(nbreJours, end = ' ')
print("jour(s) à calculer")
for i in range(nbreJours):
dateBoucle = derDateQuot+timedelta(days=i)
print(dateBoucle, end = ' ')
sql="SELECT Avg(archive.outTemp) AS MoyenneDeoutTemp, Max(archive.outTemp) AS MaxDeoutTemp1, Min(archive.outTemp) AS MinDeoutTemp, Avg(archive.outHumidity) AS MoyenneDeoutHumidity, Avg(archive.pressure) AS MoyenneDepressure, Sum(archive.rain) AS SommeDerain, Max(archive.rainRate) AS MaxDerainRate "
sql += " FROM archive GROUP BY archive.date_jour "
sql += "HAVING archive.date_jour='"
sql += str(dateBoucle) + "';"
cur.execute(sql)
x = cur.fetchone()
if (x is not None):
if (len(x)==7):
sql = "INSERT INTO t_quotidien VALUES("
sql += "'" + str(dateBoucle) + "',"
# température moyenne
if (x[0] is not None):
sql+= str(round(x[0],2))+", "
else :
sql+="Null, "
# température max
if (x[1] is not None):
sql+= str(round(x[1],2))+", "
else :
sql+="Null, "
# température min
if (x[2] is not None):
sql+= str(round(x[2],2))+", "
else :
sql+="Null, "
# Humidité moyenne
if (x[3] is not None):
sql+= str(round(x[3],1))+", "
else :
sql+="Null, "
# bar
if (x[4] is not None):
sql+= str(round(x[4],2))+", "
else :
sql+="Null, "
# rain
if (x[5] is not None):
sql+= str(round(x[5],4))+", "
else :
sql+="Null, "
# rain rate
if (x[6] is not None):
sql+= str(round(x[6],4))
else :
sql+="Null"
sql+=",Null,Null,Null);" # tpe, dj18, dj16
#print(sql)
try:
# Executing the SQL command
cur.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rolling back in case of error
db.rollback()
# 2.4 Calculs TPE, DJ18 et DJ16
print("\nCalculs TPE & Cie\n")
print("date \t\tTPE \tDJ16.5 \tDJ18")
while True :
# première date où tpe est null
sql="SELECT Min(t_quotidien.q_date) AS MinDeq_date FROM t_quotidien GROUP BY t_quotidien.q_tpe HAVING t_quotidien.q_tpe Is Null;"
cur.execute(sql)
x = cur.fetchone()
if (x is None):
break # on sort de la boucle, tout est à jour
derDateQuot=x[0]
print(derDateQuot, end = '\t')
TPE=0
for i in range(3):
DateQuot = derDateQuot- timedelta(days=2 - i)
sql = "SELECT t_quotidien.q_temp FROM t_quotidien WHERE t_quotidien.q_date = '" + str(DateQuot) +"' ;"
cur.execute(sql)
x = cur.fetchone()
if (x is not None):
if (i==0):
TPE = x[0]*0.1 # 2 jours avant = 10 %
elif(i==1):
TPE+=(x[0]*0.3) # 1 jour avant += 30 %
else :
TPE+=(x[0]*0.6) # le jour même += 60 %
print(round(TPE,2),end='\t')
if (16.5-TPE >= 0):
DJ16= round(16.5-TPE,2)
else :
DJ16 = 0
if (18-TPE >= 0):
DJ18= round(18-TPE,2)
else :
DJ18 = 0
print(round(DJ16,2), end='\t')
print(round(DJ18,2), end='\n')
sql="UPDATE t_quotidien SET t_quotidien.q_tpe = "+ str(TPE)
sql += ", t_quotidien.q_dj_18 = " +str(DJ18)
sql += ", t_quotidien.q_dj_16 = " + str(DJ16)
sql += " WHERE t_quotidien.q_date='" + str(DateQuot) +"';"
try:
# Executing the SQL command
cur.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rolling back in case of error
db.rollback()
db.close |
Partager