CODEWITHSUNDEEP
pythonsqlite
kohane15
kohane15

Reputation: 852

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

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!

Upvotes: 0

Views: 230

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

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.

Upvotes: 1

Related Questions