The base is connected with MS-ACCESS (one of my specialties) and I
exploit some data for my energy management (I was 0also energy manager)
# -*- coding: utf-8 -*-
import mysql.connector
from datetime import datetime, timedelta
# script éxécuté une fois par jour (à 00:07:30 ?)
# version du 30/05/2022 (a)
db = mysql.connector.connect(
host="localhost",
user="weewx",
password="weewx",
database="weewx"
)
cur = db.cursor()
# -1- -1- -1- -1- -1- -1- -1- -1- -1- -1- -1- -1-
-1- -1-
# mise à jour du champ 'date_jour' dans la table 'archive' comme suit :
# transforme le timestamp 'archive.dateTime' en une date (sans hh:mm:ss)
# pour le placer dans le chanp 'date_jour' qui servira par après à
calculer les valeurs quotidiennes
sql="SELECT archive.dateTime FROM archive WHERE (((archive.date_jour) Is
Null));"
cur.execute(sql)
x = cur.fetchall()
i = 0
print("Mise à jour dates dans la table archive")
for l in x:
i+=1
DateWx =datetime.fromtimestamp(l[0])
DateWx =datetime.date(DateWx)
sql="UPDATE archive SET archive.date_jour = %s WHERE
(((archive.dateTime)=%s));"
value=(DateWx, l[0])
try:
# Executing the SQL command
cur.execute(sql, value)
# Commit your changes in the database
db.commit()
i += cur.rowcount
print(l[0], end="\t")
print(DateWx)
except:
# Rolling back in case of error
db.rollback()
print("Nbre de ligne(s) mise(s) à jour : " + str(i) )
# -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
Le 10-03-23 à 16:41, Tom Keffer a écrit :
Hmmm, you may have a non-standard schema.
Try this:
*sqlite3 /var/lib/weewx/weewx.sdb*
sqlite> *.schema*
sqlite> *.quit*
On Fri, Mar 10, 2023 at 5:34 AM Blaise Cacramp
<blaise.cacr...@gmail.com> wrote:
Ok, not null values
Le 10-03-23 à 14:09, Tom Keffer a écrit :
Do you have a null value for dateTime in your database?
Here is how to check.First, make sure you have the tool sqlite3
*sudo apt-get install sqlite3*
Then use it to find null values
*sqlite3 /var/lib/weewx/weewx.sdb*
*sqlite> select dateTime from archive where dateTime isnull;*
*sqlite> .quit*
There should not be any null values.
--
You received this message because you are subscribed to the Google Groups
"weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to weewx-user+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/weewx-user/d3498a77-a761-ad55-3ee0-5c9ae1b9388e%40gmail.com.
