Query timeseries from QuestDb using Pandas with parameters

Question

I am trying to load timeseries data from QuestDb to Pandas Dataframe. I'm trying to use Postgres driver like

import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime

engine = create_engine('postgresql://admin:quest@localhost:8812/mydb')
df = pd.read_sql_query(
  "select * from cases where ts between %(dstart)s and %(dfinish)s",
  con=engine, 
  params={"dstart":datetime(2020,12,24,16,0),"dfinish":datetime(2021,1,1,0,0)})

But got back

DatabaseError: (psycopg2.DatabaseError) between/and parameters must be constants
LINE 1: ...etry where ts between '2020-12-24T16:00:00'::timestamp ...

I also tried to use > and < instead of BETWEEN

df = pd.read_sql_query(
  "select * from cases where ts > %(dstart)s and ts < %(dfinish)s",
  con=engine, 
  params={"dstart":datetime(2014,6,24,16,0),"dfinish":datetime(2014,6,24,17,0)})

But still no luck

DatabaseError: (psycopg2.DatabaseError) unsupported class
LINE 1: ... > '2020-06-24T16:00:00'::timestamp and ts < '2021-01-0...

It works without parameters so it's not entirely wrong but I'm missing something here.

As is says `datetime(2014,6,24,16,0)` is an `unsupported class`. `datetime` is a python method. My guess is the dates need to be strings in the correct format. Similar to [Python postgreSQL sqlalchemy query a DATERANGE column](https://stackoverflow.com/q/53745320/7758804) — Trenton McKinney, Jan 12 '21 at 18:29

score 4 · Accepted Answer · answered Jan 12 '21 at 22:40

QuestDb has Postgres driver support but is not full SQL query compatible. Few bits don't work so BETWEEN apparently can only be used with constants. Also when you use python datetime parameters they are converted to '2020-06-24T16:00:00'::timestamp in the query which is also not supported by QuestDb

The workaround is to pass string parameters and convert them to timestamp in the query itself, something like

df = pd.read_sql_query(
  "select * from cases where ts > to_timestamp(%(dstart)s, 'yyyy-MM-dd HH:mm:ss') " + 
  "and ts < to_timestamp(%(dfinish)s, 'yyyy-MM-dd HH:mm:ss')",
  con=engine, 
  params={"dstart":datetime(2020,12,24,16,0).strftime("%Y-%m-%d %H:%M:%S"),
  "dfinish":datetime(2021,6,24,17,0).strftime("%Y-%m-%d %H:%M:%S")})

That's the same as in the Trenton mentioned in the comments.

Query timeseries from QuestDb using Pandas with parameters

1 Answers1