I have no idea what I'm looking at there. The requirements for the database are:
1. The first column must be dateTime. 2. The value for dateTime must never be null. Does your database satisfy these two requirements? On Fri, Mar 10, 2023 at 9:57 AM Blaise Cacramp <[email protected]> wrote: > 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 <[email protected]> > 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEB4c-U%2BqG1fpfAOB1ypFrhS5g8RaVEK53jYXNK8a3GPOA%40mail.gmail.com.
