Python Sqlite: how to select non-existing records(rows) based on a column?

Question

Hope everyone's doing well.

Database:

Value               Date
---------------------------------
3000             2019-12-15
6000             2019-12-17

What I hope to return:

"Data:3000 on 2019-12-15"

"NO data on 2019-12-16" (non-existing column based on Date)

"Data:6000 on 2019-12-17"

I don't know how to filter non-existing records(rows) based on a column.

Possible boilerplate code:

db = sqlite3.connect("Database1.db")
cursor = db.cursor()
cursor.execute("""
SELECT * FROM Table1
WHERE Date >= "2019-12-15" and Date <= "2019-12-17"
""")
entry = cursor.fetchall()
for i in entry:
    if i is None:
        print("No entry found:", i)
    else:
        print("Entry found")
db.close()

Any help is much appreciated!

Tim Biegeleisen · Accepted Answer · 2019-12-20 06:19:43Z

1

The general way you might handle this problem uses something called a calendar table, which is just a table containing all dates you want to see in your report. Consider the following query:

SELECT
    d.dt,
    t.Value
FROM
(
    SELECT '2019-12-15' AS dt UNION ALL
    SELECT '2019-12-16' UNION ALL
    SELECT '2019-12-17'
) d
LEFT JOIN yourTable t
    ON d.dt = t.Date
ORDER BY
    d.dt;

In practice, if you had a long term need to do this and/or had a large number of dates to cover, you might setup a bona-fide calendar table in your SQLite database for this purpose. The above query is only intended to be a proof-of-concept.

answered Dec 20, 2019 at 6:19

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

kohane15 Over a year ago

Thank you for taking your time to help me. I really appreciate it! So after struggling for another few hours, here's what I've managed to. As you suggested, I've created a bona-fide calendar table with dates from 2019-12-01 to 2020-12-01. Database name: CalendarDates; Column name = Date ``` SELECT * FROM CalendarDates LEFT JOIN Table1 ON Table1.Date = CalendarDates.Date ``` I understand what you're saying, but I still don't quite know how to query non-existing records. As you probably can tell, I'm very new to SQL (or programming in general), so please as ELI5 as possible. Thanks again!

Tim Biegeleisen Over a year ago

And...do you have a question?

Tim Biegeleisen Over a year ago

The whole point of the calendar table is that it generates the missing records.

kohane15 Over a year ago

SELECT * FROM Table1 LEFT JOIN CalendarDates ON Table1.Date = CalendarDates.Date WHERE Table1.Date DOES NOT EXIST How would I tanslate this concept into valid SQL statement? Thank you again!

Tim Biegeleisen Over a year ago

You don't need the WHERE clause, at least not the restriction you put there. The left join by default will include every date in your calendar table.

|

Collectives™ on Stack Overflow

Python Sqlite: how to select non-existing records(rows) based on a column?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related