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 |
