How to use params from pandas.read_sql to import data with Python pandas from SQLite table between dates

Question

I'm trying to query a table from sqlite with python pandas to analyse in jupyter notebook. The goal, is to query between two dates that I choose each time I run my script.

I have already tried as in this link:

timestamp column in sqlite return string in python

and

Pandas read_sql with parameters

In this last link, the script executes fine, but without having any data in the dataframe that I load.

When I do it manually in SQLite, writing something like:

SELECT *
FROM 'Historique'
WHERE "index" BETWEEN "2018-12-10 00:00:00" AND "2019-01-01 00:00:00"

The query works fine and it gives me the values I want. So the thing it's that python, I think it's not recognizing the paremeters I'm sending.

My code is:

import pandas as pd
import sqlite3


conn = sqlite3.connect('FR033_Historique.sqlite')
cur = conn.cursor()

start = input("First day to take")
start = pd.to_datetime(start, dayfirst=True)


end = input("last day to take")
end = pd.to_datetime(end, dayfirst=True)




Analyse = pd.read_sql(('SELECT *'
                       'FROM "Historique"'
                       'WHERE "index" BETWEEN %(dstart)s AND %(dfinish)s'),
                       con=conn,  params={"dstart":start,"dfinish":end})

The result I obtained, it's if I put it as in SQLite, python reads well the query and gives the interval of values I want, but I would to pick up each time the dates and automatically search for this.

I hope I make myself clear! Thanks!

Answer 1

You have the syntax for named parameter passing wrong. See the example in the sqlite3 docs:

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

So for your case it should be:

query = '''SELECT *
           FROM "Historique"
           WHERE "index" BETWEEN :dstart AND :dfinish'''

pd.read_sql(query, con=conn,  params={"dstart":start, "dfinish":end})