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

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.

CodePudding user response：

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']))