python - 如何进行未定义的许多 SQL 更新

Question

我正在尝试通过更新时间间隔内的两行(仅 float )的所有平均值来管理 SQLite 数据库。确定平均值后，应删除两行之一。我正在寻找的 SQL 语句嵌入在 Python 脚本中，每天由 cronjob 调用一次。

例如:

在 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

在 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

我不太确定如何解决这个问题以及哪种方法最有效。当然，也可以统计行数，每两行循环一次。在我的例子中，这可能意味着 cronjob 执行超过 1000 个 sql 查询，这似乎不是很有效。

编辑按照 Pythonclass 创建 SQLite 数据库

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()

编辑 2:

这是每天被 cronjob 调用一次的 main.py。

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()

这是用于更新和删除数据库的数据库类(现在是没有 SQL 语句的 sum_up 函数):

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

Answer 1

要获取连续的 ID 值，请将所有内容复制到临时表中。然后使用自连接将具有相应偶数和奇数 ID 值的行组合起来，并将所有内容复制回去:

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;