1

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?

Alex Willison
  • 257
  • 7
  • 20

0 Answers0