Datetimes are contained in the following dict:
datetimes = {
'start': datetime.strptime(args.start,"%Y-%m-%d %H:%M:%S").replace(tzinfo=pytz.UTC),
'end' : datetime.strptime(args.end,"%Y-%m-%d %H:%M:%S").replace(tzinfo=pytz.UTC)
}
Here is an attempt to use this dict in a PostgreSQL query through psycopg2:
stmt = "WITH a AS ( SELECT date_trunc('day', start_time - INTERVAL '18 hours') AS day, sum(sot_allowed) AS allowed \
FROM planning_constraints \
WHERE start_time >= %(start)s \
AND start_time < %(end)s \
AND comment like '6%' \
GROUP BY day ), \
pa AS ( SELECT date_trunc('day', start_time - INTERVAL '18 hours') AS day, sum(sot_contribution) AS planned_all \
FROM planning \
WHERE start_time >= %(start)s \
AND start_time < %(end)s \
AND state = 'selected' \
GROUP BY day), \
po AS ( SELECT date_trunc('day', start_time - INTERVAL '18 hours') AS day, sum(sot_contribution) AS planned_occ \
FROM planning \
WHERE start_time >= %(start)s \
AND start_time < %(end)s \
AND state = 'selected' \
AND name ~ '^s' \
GROUP BY day) \
SELECT day, \
COALESCE(allowed, interval '0 minute') AS sot_allowed, \
COALESCE(planned_all, interval '0 minute') AS sot_planned_all, \
COALESCE(planned_occ, interval '0 minute') AS sot_planned_occ, \
round((COALESCE(date_part('epoch',planned_all)) / COALESCE(date_part('epoch',allowed)))::numeric,4) AS percent_all, \
round((COALESCE(date_part('epoch',planned_occ)) / COALESCE(date_part('epoch',allowed)))::numeric,4) AS percent_occ \
FROM a \
FULL OUTER JOIN pa \
USING (day) \
FULL OUTER JOIN po \
USING (day) \
ORDER BY day \
"
cur.execute(stmt, (datetimes))
However, the following error is produced:
psycopg2.ProgrammingError: argument formats can't be mixed
How can this dict be used to insert similar values into multiple locations in the query? Specifically, the %(start)s
and %(end)s
?