How to fix 'invalid syntax for type timestamp' Postgres?

Question

I am struggling with this issue

It was fine when I run the function:

def total_event(start_date, end_date):
    conn = psycopg2.connect(NE_DB_2)
    cur = conn.cursor()
    payload = """SELECT COUNT("pattern_id") FROM "pattern" WHERE "created_at" < '%(end)s' AND "created_at" > '%(start)s'"""
    payload % {'start': start_date, 'end': end_date}
    cur.execute(payload)
    rows = cur.fetchall()
    event_total = str(event_total)    
    return event_total

but when I execute the function with the defined variables:

start_date = '2016-12-05'
end_date = '2016-12-11'
start_date = datetime.datetime.strptime(start_date,'%Y-%m-%d')
end_date = datetime.datetime.strptime(end_date,'%Y-%m-%d')
event_total = total_event(start_date, end_date)
event_total

it gives me this error:

DataErrorTraceback (most recent call last)
<ipython-input-46-0979ea389f87> in <module>()
     12 start_date = datetime.datetime.strptime(start_date,'%Y-%m-%d')
     13 end_date = datetime.datetime.strptime(end_date,'%Y-%m-%d')
---> 14 event_total = total_event(start_date, end_date)
     15 event_total

<ipython-input-46-0979ea389f87> in total_event(start_date, end_date)
      4     payload = """SELECT COUNT("pattern_id") FROM "pattern" WHERE "created_at" < '%(end)s' AND "created_at" > '%(start)s'"""
      5     payload % {'start': start_date, 'end': end_date}
----> 6     cur.execute(payload)
      7     rows = cur.fetchall()
      8     event_total = str(event_total)

DataError: invalid input syntax for type timestamp: "%(end)s"
LINE 1: ..."pattern_id") FROM "pattern" WHERE "created_at" < '%(end)s' ...

Answer 1

You are not reassigning the formatted string to payload. You are still passing the version of the variable with the %(end)s in it.

Change

payload % {'start': start_date, 'end': end_date}

into

payload = payload % {'start': start_date, 'end': end_date}