如何修复"类型时间戳的语法无效"Postgres？

Question

我在这个问题上挣扎

我运行该功能时很好：

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

但是，当我使用定义变量执行功能时：

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

它给了我这个错误：

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

您没有将格式的字符串重新分配给payload。您仍在将变量的版本带有其中的%(end)s。

更改

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

进入

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