Pandas DataFrame - Using variables in SQL query

[Mr Toad]

Hi, 

I have a weather station and rather than  use off the shelf software I'm writing my own reports. I have the basic query and the report I want working but I need to be able to produce that report for a variety of dates. 

As an example; Barometric pressure, Max and Min each hour for a day, each day for a week, or a month etc. 

I'm using Python 3.7 and Pandas Dataframe.

df = pd.read_sql("SELECT strftime('%d', dateTime, 'unixepoch')AS day, "
                              "strftime('%m', dateTime, 'unixepoch')AS month, "
                              "strftime('%Y', dateTime, 'unixepoch')AS year, "
                              "round(min*33.86,2) AS low, "
                              "round(max*33.86,2) AS high "
                              "FROM archive_day_barometer "
                              "WHERE month = '11' AND year='2020'", conn)

This query works perfectly and gives me the results I want, however, when I try to replace the '11' amd '2020' in the WHERE statement so I can change the report  I can't get it to work.

I've tried setting a variable yr = ('2020') the using it like this 

("SELECT............."

 "WHERE year=?", yr, con)

I've also tried @yr which one search implied would solve my problem. I would be very grateful for any help in getting this working.

Thank you

Mason

[Roger Binns]

On 12/9/20 2:02 AM, Mr Toad wrote:
>   "WHERE month = '11' AND year='2020'"

That is treating the month and year columns as though they were strings.

SQLite version 2 used to store everything in the underlying database as
strings, and then in queries would do necessary conversions to integer etc.

SQLite 3 still has some of that assistance around, but if you are
arbitrarily mixing strings and integers you will have problems. You can
use typeof() to see what types your data really has. You can use CHECK
on column constraints to force types when adding data too.

> I've tried setting a variable yr = ('2020') the using it like this > ("SELECT............."
> "WHERE year=?", yr, con)

You have to pass the bindings as one parameter of a sequence. In this
case it should be ('2020',) [note the comma to make it a sequence]

> weather station and rather than use off the shelf software I'm writing my own reports

I'm doing the same thing! Have a look at influxdb for a time series
database which should make that side a lot easier for you.

I'm using rtl_433 with several transmitters, but am only focusing on the
data for the last 36 hours. Which easily fits in memory and has no need
for databases :-)

Roger