How to make undefined many SQL updates

Question

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

Show the tabel schema. Is the first column guaranteed to contain consecutive values? — CL., Jun 07 '17 at 16:47
Is the first column guaranteed to contain consecutive values? — CL., Jun 08 '17 at 10:21
For sure! As you can see, the first column (ID) is autoincremented. — N3wbie, Jun 08 '17 at 10:43
Only one of the two rows should be deleted. The second row should contain the average value of both rows. Same thing for every two-rows in the whole timeinterval. The interval is over 24h of the day which is seven days in the past. — N3wbie, Jun 08 '17 at 11:37
I mean, is it possible that any row got ever deleted before? — CL., Jun 08 '17 at 12:29
Yes this will be possible, because I want to delete rows everday of three different days. (weekago, monthago and yearago) I'll edit the question agin with the database class and the main.py. In hope it will be more understandable. — N3wbie, Jun 08 '17 at 12:39

score 1 · Accepted Answer · answered Jun 08 '17 at 13:25

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;

Well, this looks like the most efficient way I was searching for. Never thought about to creat a temporary table. I'm very thankful for this helpful answer. I' will try this and report my result. Best greetings! — N3wbie, Jun 08 '17 at 13:41

How to make undefined many SQL updates

1 Answers1