python - 从 sqlite 数据库中获取多行,合并共享相同日期的行,并在表中打印
问题描述
我有一个 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”,我想尝试通过自己的小项目巩固我所学到的知识。
我知道可用于打印表格的各种软件包:
不过,我正在努力弄清楚如何从数据库中提取数据,以将其转换为合适的格式(或者确实是最好的格式)。
解决方案
我相信以下内容可以作为基础。请注意,已包含日期范围选择,并且使用的列仅限于示例所需的列。
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 代表 temps r 代表降雨)
然后对于日期范围 cte (2008-10-01 ........ 2008-10-31) 中的每一行,输出三列。
- 当前从 CTE 处理的日期,
- 该日期的温度或降雨量与第一个站点 ID 的总和,
- 该日期的温度或降雨量与第二个站点 ID 的总和。
例如:-
如果类型更改为 r 则:-
- 请注意,突出显示的行显示已添加同一日期的多行。
- 应该注意的是,这是一个原则上的示例,为了简洁起见,并未考虑所有方面。相反,它是涵盖一些可能遇到的问题的示例代码。
推荐阅读
- mediawiki - 哪个版本的 MediaWiki 出现了“页面预览”功能?
- react-native - 如何在反应原生应用程序中跟踪设备上的所有图像?
- c - 在C中使用'for'循环是否可以重复字符串文字?
- r - feasts 包的 gg_season 基本示例中的错误
- python - 使用python从S3存储桶下载后加载pickle文件的问题
- php - 为什么 echo 元素只在 div 中水平显示
- javascript - 在选项卡之间切换
- amazon-web-services - ConflictException:阶段已经存在于 aws_api_gateway_deployment 和 stage_name
- r - 我无法在 R 中运行 RSQLite
- sql-server - 我需要帮助加入表格