python - 从 sqlite 数据库中获取多行，合并共享相同日期的行，然后在表中打印

Question

我有一个 SQLITE 数据库，其中有一个名为“天气”的表。

表中有 15 列，但为了简洁起见，我们假设我只对较小的子集感兴趣

这是创建表的 SQL:

CREATE TABLE IF NOT EXISTS Weather (
    id              INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    unique_id       TEXT UNIQUE,
    station_id      INTEGER,
    date            TEXT,
    temperature     FLOAT,
    temperature_min FLOAT,
    temperature_max FLOAT,
    precipitation   FLOAT,
    snowfall        INTEGER,
    snowdepth       INTEGER,
    winddirection   INTEGER,
    windspeed       FLOAT,
    peakgust        FLOAT,
    sunshine        FLOAT,
    pressure        FLOAT
)

这是创建示例日期的 SQL:

INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1630', '03779_2008-04-29', '3779', '2008-04-29', '10.4', '8.4', '14.5', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '996.5');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12212', '72502_2008-04-29', '72502', '2008-04-29', 'NULL', '7.8', '15.0', '1.0', 'NULL', 'NULL', 'NULL', '20.5', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1631', '03779_2008-04-30', '3779', '2008-04-30', '8.9', '7.6', '10.8', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '990.2');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12213', '72502_2008-04-30', '72502', '2008-04-30', 'NULL', '4.4', '15.0', 'NULL', 'NULL', 'NULL', 'NULL', '17.3', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12214', '72502_2008-05-01', '72502', '2008-05-01', 'NULL', '2.8', '14.4', '0.3', 'NULL', 'NULL', 'NULL', '12.6', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1632', '03779_2008-05-02', '3779', '2008-05-02', '12.4', '8.7', '16.8', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '1019.8');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12215', '72502_2008-05-02', '72502', '2008-05-02', 'NULL', '10.6', '15.0', '1.5', 'NULL', 'NULL', 'NULL', '16.9', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1633', '03779_2008-05-03', '3779', '2008-05-03', '15.3', '10.1', '21.6', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '1023.4');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12216', '72502_2008-05-03', '72502', '2008-05-03', 'NULL', '8.9', '14.4', 'NULL', 'NULL', 'NULL', 'NULL', '16.2', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1634', '03779_2008-05-04', '3779', '2008-05-04', '18.3', '14.2', '23.2', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '1021.7');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12217', '72502_2008-05-04', '72502', '2008-05-04', 'NULL', '9.4', '21.7', '1.8', 'NULL', 'NULL', 'NULL', '13.0', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1635', '03779_2008-05-05', '3779', '2008-05-05', '18.0', '14.9', '22.7', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '1024.8');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12218', '72502_2008-05-05', '72502', '2008-05-05', 'NULL', '8.9', '22.8', 'NULL', 'NULL', 'NULL', 'NULL', '12.2', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12219', '72502_2008-05-06', '72502', '2008-05-06', 'NULL', '8.9', '26.1', 'NULL', 'NULL', 'NULL', 'NULL', '9.4', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12220', '72502_2008-05-07', '72502', '2008-05-07', 'NULL', '13.3', '22.8', 'NULL', 'NULL', 'NULL', 'NULL', '11.2', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1636', '03779_2008-05-08', '3779', '2008-05-08', '19.4', '14.4', '24.2', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '1014.7');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12221', '72502_2008-05-08', '72502', '2008-05-08', 'NULL', '17.8', '23.9', '0.3', 'NULL', 'NULL', 'NULL', '22.3', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1637', '03779_2008-05-09', '3779', '2008-05-09', '20.2', '15.4', '26.0', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '1012.4');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12222', '72502_2008-05-09', '72502', '2008-05-09', 'NULL', '9.4', '18.3', '30.0', 'NULL', 'NULL', 'NULL', '24.5', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1638', '03779_2008-05-10', '3779', '2008-05-10', '21.6', '17.0', '26.3', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '1016.6');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12223', '72502_2008-05-10', '72502', '2008-05-10', 'NULL', '10.0', '19.4', 'NULL', 'NULL', 'NULL', 'NULL', '12.2', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1639', '03779_2008-05-11', '3779', '2008-05-11', '21.1', '15.8', '26.7', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '1018.8');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12224', '72502_2008-05-11', '72502', '2008-05-11', 'NULL', '10.0', '15.0', 'NULL', 'NULL', 'NULL', 'NULL', '18.4', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1640', '03779_2008-05-12', '3779', '2008-05-12', '19.6', '13.8', '25.4', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '1018.7');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12225', '72502_2008-05-12', '72502', '2008-05-12', 'NULL', '8.3', '13.3', '9.1', 'NULL', 'NULL', 'NULL', '31.0', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1641', '03779_2008-05-13', '3779', '2008-05-13', '16.6', '11.7', '21.6', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '1018.3');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12226', '72502_2008-05-13', '72502', '2008-05-13', 'NULL', '7.8', '22.2', 'NULL', 'NULL', 'NULL', 'NULL', '22.3', 'NULL', 'NULL', '');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('1642', '03779_2008-05-14', '3779', '2008-05-14', '15.3', '11.7', '20.1', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', '1015.3');
INSERT INTO "main"."Weather" ("id", "unique_id", "station_id", "date", "temperature", "temperature_min", "temperature_max", "precipitation", "snowfall", "snowdepth", "winddirection", "windspeed", "peakgust", "sunshine", "pressure") VALUES ('12227', '72502_2008-05-14', '72502', '2008-05-14', 'NULL', '7.8', '23.9', 'NULL', 'NULL', 'NULL', 'NULL', '10.1', 'NULL', 'NULL', '');

我希望能够使用 Python (3.7) 查询数据库，然后打印表格。

查询的输入为:

• 站 1 的 station_id (3779)

• 站 2 的 station_id (72502)

• 要比较的变量(例如温度、降雨量 - 一次仅一个)

• 要检索的数据的开始日期(在示例数据中，2008-04-29)

• 要检索的数据的结束日期(在示例数据中，2008-05-14)

输出示例如下:

|    Date    | Station 1 temperature_min | Station 2 temperature_min |
----------------------------------------------------------------------
| 2008-04-29 |            8.4            |             7.8           |
| 2008-04-30 |            7.6            |             4.4           |

打印表的每一行都是数据库中两行的组合。开始日期和结束日期之间的每个日期应该为一行，但这并不能保证。因此，我需要考虑数据本身中缺失的行和 NULL 值。

除了我试图学习之外，这项练习没有任何真正的目的。我刚刚在 Coursera 上完成了“Python for everything”，我想尝试通过自己的小项目来巩固我所学到的知识。

我知道可用于打印表格的各种包:

Printing Lists as Tabular Data

我正在努力思考如何从数据库中提取数据，以将其转换为合适的格式(或者确实是最好的格式)。

Answer 1

我认为以下内容可以作为基础。请注意，已包含日期范围选择，并且使用的列仅限于示例所需的列。

DROP TABLE IF EXISTS weather;
CREATE TABLE IF NOT EXISTS weather (id INTEGER PRIMARY KEY, date TEXT,station_id INTEGER, temperature REAL, rainfall REAL);
INSERT INTO weather (station_id,date,temperature,rainfall) VALUES
    (3779,'2008-10-08',7.5,1.2),(72502,'2008-10-08',2.3,0),
    (3779,'2008-10-09',5.7,0.7),(72502,'2008-10-09',4.2,1.2),
    (3779,'2008-10-10',10.1,null),
    (3779,'2008-10-11',9.3,1.0),(72502,'2008-10-11',3.3,null),
    (3779,'2008-10-11',2.3,2.1),(72502,'2008-10-11',3.5,3.1),
    (3779,'2008-10-12',4.5,2.1),(72502,'2008-10-12',5.2,0.9)
;


WITH  RECURSIVE cte_daterange(ctedate) AS 
    ( 
        SELECT '2008-10-01' /*<<<<< would likely be parameter */
        UNION ALL SELECT date(ctedate,'+1 days') FROM cte_daterange WHERE ctedate < '2008-11-01' /*<<<< would likely be parameter */ LIMIT 31
    ),
    ctetype(type) AS (SELECT ('t' /*<<<< parameter t for temp, r for rainfall */))
    SELECT 
        ctedate , 
        coalesce((SELECT 
            CASE 
                WHEN (SELECT * FROM ctetype) = 't' THEN  sum(temperature)
                WHEN (SELECT * FROM ctetype) = 'r' then sum(rainfall)
                ELSE 0
            END

        FROM weather WHERE date = ctedate AND station_id = 3779 /*<<<< paarameter */),'n/a') AS Result1,
        coalesce((SELECT 
            CASE 
                WHEN (SELECT * FROM ctetype) = 't' THEN  sum(temperature)
                WHEN (SELECT * FROM ctetype) = 'r' then sum(rainfall)
                ELSE 0
            END
        FROM weather WHERE date = ctedate AND station_id =  72502 /*paremeter */),'n/a') AS Result2

    FROM cte_daterange
;
DROP TABLE IF EXISTS weather;

前3条语句只是准备测试数据

以下语句创建 2 个 CTE，一个用于日期范围，另一个用于类型(t 表示温度，r 表示降雨量)

然后对于日期范围 cte (2008-10-01 ........ 2008-10-31) 中的每一行，输出三列。

1. CTE 当前正在处理的日期，
2. 该日期的气温或降雨量与第一个气象站 ID 的总和，
3. 该日期的气温或降雨量与第二个气象站 ID 的总和。

例如:-

如果类型更改为 r 则:-

• 请注意，突出显示的行显示已添加同一日期的多行。
  • 应该指出的是，这是一个原则性的示例，为了简单起见，编写时并未考虑所有方面。相反，它是示例代码，涵盖了可能遇到的一些问题。