时间戳

首页 > 解决方案 > PostgreSQL - 更新没有时区的日期时间

python - PostgreSQL - 更新没有时区的日期时间

问题描述

我正在尝试在没有时区的 PostgreSQL 数据库中替换日期时间值；

from sqlalchemy import create_engine

# Connection with DB
engine = create_engine('postgresql://postgres:xxx')

engine.connect()

# Select Values
batch_id = 15
correct_datetime = pd.to_datetime('2021-10-05' , format='%Y-%m-%d')

# Perform update
sql = f"""
    UPDATE my_table
    SET datetime_opening={correct_datetime}
    WHERE id={batch_id}
"""

with engine.begin() as conn:     # TRANSACTION
    conn.execute(sql)

但是，它会引发以下错误：

(psycopg2.errors.SyntaxError) syntax error at or near "00"
LINE 3: ...   SET datetime_opening=2021-10-05 00:00:00

其他试验包括：

# 1
correct_datetime = '2021-10-05'
ProgrammingError: (psycopg2.errors.DatatypeMismatch) column "datetime_opening" is of type timestamp without time zone but expression is of type integer
LINE 3:     SET datetime_opening=2021-10-05
                                                  ^
HINT:  You will need to rewrite or cast the expression.

# 2
correct_datetime = df.iloc[-1, 6] + timedelta(days=7)
ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "18"
LINE 3: ...   SET datetime_opening=2021-10-05 18:00:00

如何使用新的日期时间正确更新值？

标签： pythonpostgresqldatetimesqlalchemy

解决方案

要实现的唯一区别是将 {correct_datetime} 替换为 '{correct_datetime}'。

因此，

correct_datetime = '2021-10-05'


# Perform update
sql = f"""
    UPDATE my_table
    SET datetime_opening ='{correct_datetime}'
    WHERE id={batch_id}
"""

推荐阅读