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.

>Solution :

The problem with your code is that you have set the starting and the ending values of the operator BETWEEN reversed.
It should be:

BETWEEN datetime('{}', '-15 day') AND datetime('{}') 

but, I would also suggest to use ? placeholders to pass the parameters.
Also, there is no need to use the datetime() or date() functions to get a date if it is already in the format YYYYY-MM-DD.

Use this:

sql = "SELECT COUNT(*) FROM bt WHERE id = ? AND btdate BETWEEN date(?, '-15 day') AND ?;"
cur.execute(sql, (row['id'], row['date'], row['date']))