in

SQLite3 Query in Python to Retrieve count of rows between two Dates


Problem

I have a sqlite3 db with two tables, bt and data.
Both tables contain a date column with format YYYY-MM-DD.
I have read one table in as a pandas df.

I need to iterate over the rows in the df and return a count of rows that fall within 14 days of the date in that row.

Code I Have Tried

import sqlite3 as lite
import pandas as pd

#Function to Create DB Connection and Raise Error
def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = lite.connect(db_file)
        return conn
    except lite.Error as e:
        print(e)

    return conn

#Creat Connection to DB and Create df
conn = create_connection('test.db')
df = pd.read_sql("Select * from data;", conn)

#Create Cursor
cur = conn.cursor()

#Iterate over rows in df and return count of rows within date range
for index ,row in df.iterrows():
    cur.execute("SELECT count(*) FROM bt WHERE id='{}' AND datetime(btdate) BETWEEN datetime('{}') AND datetime('{}', '-15 day');".format(row['id'], row['date'], row['date']))
    print(cur.fetchall())

Current Output

The above Query is returning all 0’s however, I know that there are rows that meet this criteria within the table.

[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]
[(0,)]

Desired Output
My desired output is a count of the number of rows between the two dates.



Source: https://stackoverflow.com/questions/70714190/sqlite3-query-in-python-to-retrieve-count-of-rows-between-two-dates

clang-tidy fails with OpenCV code that compiles fine

A Deep Learning Based Attendance System is a mobile application