Take multiple rows from a sqlite database, combine rows that share the same date, and print in a table

Question

I've got a SQLITE database with a table called 'Weather'.

There are 15 columns in the table, but for brevity let's assume that i'm only interested in a smaller sub-set

This is the SQL to create the table:

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
)

This is the SQL to create sample date:

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', '');

I want to be able to query the database using Python (3.7) and then print a table.

The inputs to the query would be:

station_id of station 1 (3779)
station_id of station 2 (72502)
The variable to be compared (e.g. temperature, rainfall - Only one at a time)
Start date of data to retrieve (in the sample data, 2008-04-29)
End date of data to retrieve (in the sample data, 2008-05-14)

An example of the output would be:

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

Each row of the printed table would be the combination of two rows in the database. There should be one row for each date between start and end dates, but it isn't guaranteed. I therefore need to consider missing rows and NULL values in the data itself.

There isn't a real purpose to the exercise beyond my trying to learn. I've just finished 'Python for everybody' on Coursera and I wanted to try and solidify what I learn with my own little project.

I am aware of the various packages that can be used to print the tables:

Printing Lists as Tabular Data

I'm struggling to get my head around how to extract the data from the DB though, to get it into a suitable format (or which is indeed the best).

Sample data should be in the body of your question (preferably as a create table and insert statements for easy copy and pasting). Include edge cases. — Shawn, Nov 10 '19 at 16:55
I've added the SQL. I'd slightly disagree that I'm asking for a tutorial - I know how to insert a row, retrieve a row (that's how I got the data in there in the first place). There seem to be so many ways though to retrieve the data and then turn it onto a table though. I'm not specifically asking for code - even just the steps that I should take to get from A to B. I'll learn more by trying to do it myself and if I get stuck, then asking more questions :) — Nick, Nov 10 '19 at 19:12

MikeT · Accepted Answer · 2019-11-10T20:17:08.287

I believe the following could be used as the basis. Noting that a date range selection has been included and that the columns used are limited to just those needed for the example.

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;

The first 3 statements are just preparing the test data

The following statement creates 2 CTE's one for the date range, the other for the type (t for temps r for rainfall)

Then for each row in the daterange cte (2008-10-01 ........ 2008-10-31) then three columns are output.

the date currently being handled from the CTE,
The sum of the temperatures or rainfall for that date and the first station id,
The sum of the temperatures or rainfall for that date and the second station id.

e.g. :-

If the type is changed to r then :-

Note the highlighted row shows that the multiple rows for the same date have been added.
- It should be noted that this is an in-principle example and has not been written, for the sake of bervity, to consider all asspects. Rather it is example code that covers some of the issues that may be encountered.

I'll take a look tonight, but it looks like this will do what I need if I pair it with this solution to pretty printing a SQL table: https://stackoverflow.com/questions/10865483/print-results-in-mysql-format-with-python I'll come back and mark this as the answer, once I've got it working :) — Nick, Nov 11 '19 at 08:47
Are you able to offer a bit more advise on how to add parameters to the SQL? — Nick, Nov 15 '19 at 22:58
Stack Overflow doesn't let you edit a comment after 5 minutes! Maybe I need to do a new question :) I tried (for example): ( SELECT "+sdate+"=?", (start_date,) /*<<<<< 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 ), I could only find examples for adding to 'execute' rather than executescript (so not clear where in the multiple queries the parameters should sit) — Nick, Nov 15 '19 at 23:04
@Nick that should really be a new question as the original has been answered i.e. the result is bar formatting the result is as asked. If you believe that the answer answered the question and that you have moved forward then you should tick the answer to indicate that a suitable answer has been given. Bur basically instead of a hard coded values you replace it with a **?** and then pass it (and others if multiple parameters and thus ?). — MikeT, Nov 15 '19 at 23:12
@Nick from a very quick look, executescripty is for execeuting a number of SQL statements and doesn't appear to cater for parameters, you either want to use execute or exceutemany as these allow parameters to be passed. To use executescript you would need to build the statments with the values hard coded. — MikeT, Nov 15 '19 at 23:19

Take multiple rows from a sqlite database, combine rows that share the same date, and print in a table

1 Answers1