I'm trying to manage a SQLite Database by updating all the average values of two rows (only the floats), which are in a timeinterval. One of the two rows should be deleted after the average values are determind. The SQL-statment I'm looking for, is embbeded in a Pythonscript, which gets one time a day called by a cronjob.
For Example:
Before the Cronjob
1|Node1|45.39|25.07|1013.3|2.086|2017-06-01 17:43:43
2|Node1|48.07|25.07|1013.4|2.081|2017-06-01 17:43:57
3|Node1|45.37|25.08|1013.3|2.08|2017-06-01 17:44:01
4|Node1|48.04|25.07|1013.3|1.991|2017-06-01 17:44:15
5|Node1|45.39|25.08|1013.3|1.991|2017-06-01 17:48:36
6|Node1|45.47|25.03|1013.4|1.991|2017-06-01 17:50:57
7|Node1|45.48|25.03|1013.3|1.991|2017-06-01 17:52:09
8|Node1|45.46|25.04|1013.3|1.991|2017-06-01 17:53:26
9|Node1|45.25|25.12|1013.3|2.092|2017-06-01 17:57:20
10|Node1|45.43|25.02|1013.2|2.093|2017-06-01 18:03:19
After the Cronjob
1|Node1|46.73|25.07|1013.35|2.084|2017-06-01 17:43:43
2|Node1|46.705|25.075|1013.3|2.081|2017-06-01 17:44:01
3|Node1|45.43|25.055|1013.35|1.999|2017-06-01 17:48:36
4|Node1|48.47|25.035|1013.3|1.991|2017-06-01 17:52:09
5|Node1|45.35|25.07|1013.25|2.092|2017-06-01 17:57:20
I'm not really sure how to solve this problem and which is the most efficient way. Of course, it is possible to count the number of rows and loop them for each two rows. In my case this could mean the cronjob do more then 1000 sql-querys, which seems to be not very efficient.
EDIT Following Pythonclass creates the SQLite-database
conn = sqlite3.connect('/var/www/sqliteDatabases/database_sdr.db') #connect to database "database.db"
cur = conn.cursor()
#create a cursor
#______create table for temperature and humidity with date and time______
cur.execute('''CREATE TABLE measurement(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
nodeId text,
humidity float,
temperature float,
pressure float,
voltage float,
datetime text)''') c
conn.commit() conn.close()
EDIT 2:
This is the main.py which gets called one time a day by a cronjob.
from includes.MyDatabase import MyDatabase
from includes.MyDates import MyDates
uri = '/var/www/sqliteDatabases/database_sdr.db'
dt = MyDates()
db = MyDatabase(uri)
db.sum_up_week(dt.weekago)
db.sum_up_month(dt.monthago)
db.sum_up_year(dt.yearago)
db.close()
this is the Database class for updating and deleting the Database (For now the sum_up-function without SQL Statements):
import sqlite3
import sys
class MyDatabase():
def __init__(self, uri):
self.uri = uri
try:
self.conn = sqlite3.connect(self.uri) #connect to database "wsn.db"
self.cur = self.conn.cursor() #create a cursor
except sqlite3.Error as err:
print('Database connection failed with this path:'+self.uri+' Error: '), err.args[0]
exit()
def sum_up_week(self, week):
print(week) #e.g. 2017-06-01
def sum_up_month(self, month):
print(month) #e.g. 2017-05-08
def sum_up_year(self, year):
print(year) #e.g. 2016-06-08
def close(self):
self.conn.commit() #save the determined rows
self.conn.close() #close connection to DB
To get consecutive ID values, copy everything into a temporary table. Then use a self-join to combine the rows with corresponding even and odd ID values, and copy everything back:
CREATE TEMPORARY TABLE t1 (
id INTEGER PRIMARY KEY,
nodeId,
humidity,
temperature,
pressure,
voltage,
datetime
);
INSERT INTO t1
SELECT NULL, nodeId, humidity, temperature, pressure, voltage, datetime
FROM measurement;
CREATE TEMPORARY TABLE t2 AS
SELECT a.nodeId AS nodeId,
(a.humidity + b.humidity ) / 2 AS humidity,
(a.temperature + b.temperature) / 2 AS temperature,
(a.pressure + b.pressure ) / 2 AS pressure,
(a.voltage + b.voltage ) / 2 AS voltage,
a.datetime AS datetime
FROM t1 AS a
JOIN t1 AS b ON a.id + 1 = b.id
AND (a.id % 2) == 1;
DELETE FROM measurement;
INSERT INTO measurement
SELECT NULL, nodeId, humidity, temperature, pressure, voltage, datetime
FROM t2;
DROP TABLE t1;
DROP TABLE t2;