如何在psycopg2中转换列类型解决时区时间戳不匹配问题

我想用pandas DataFrame的两列更新PostgreSQL表，编写了如下代码：

query = """
    update table_1 m
    set column_1 = e.column_1
    from (VALUES %s) AS e (column_2, column_1) 
    where m.column_2= e.column_2::text"""


args = (('random_value_2','2022-11-15T13:04:18.844Z'), ('random_value_1','2022-11-15T13:04:18.844Z'))

psycopg2.extras.execute_values(
        cur, query, args, template=None, page_size=100
        )           

运行代码时出现如下错误：

psycopg2.errors.DatatypeMismatch: column "column_1" is of type timestamp with time zone but expression is of type record
LINE 3:     set column_1= e.column_1

请问如何在psycopg2中将字符串或Python datetime转换为带时区的timestamp？

错误根源是execute_values默认未正确识别时间戳类型，导致PostgreSQL将传入的时间字符串判定为record类型，而非timestamptz（带时区的时间戳）。以下是两种可行的解决方式：

1. SQL语句中显式指定列类型 #

修改VALUES子句的列定义，明确标注column_1的类型为timestamptz，PostgreSQL会自动解析ISO格式的时间字符串：

query = """
    update table_1 m
    set column_1 = e.column_1
    from (VALUES %s) AS e (column_2 text, column_1 timestamptz) 
    where m.column_2 = e.column_2"""

无需修改Python端的参数，直接传入原字符串即可。

2. 传入带时区的Python datetime对象 #

利用psycopg2的自动类型映射机制，先将时间字符串转为带时区的datetime对象，再传入执行：

from datetime import datetime

# 转换字符串为带时区的datetime（ISO 8601格式的Z表示UTC时区）
args = [
    ('random_value_2', datetime.fromisoformat('2022-11-15T13:04:18.844Z')),
    ('random_value_1', datetime.fromisoformat('2022-11-15T13:04:18.844Z'))
]

query = """
    update table_1 m
    set column_1 = e.column_1
    from (VALUES %s) AS e (column_2 text, column_1 timestamptz) 
    where m.column_2 = e.column_2"""

psycopg2.extras.execute_values(
        cur, query, args, template=None, page_size=100
        )

如果你的pandas DataFrame时间列已是带时区的datetime类型，直接提取传入即可，无需额外转换。

额外优化：使用自定义模板 #

通过execute_values的template参数，可更灵活地控制每个值的类型转换：

query = """
    update table_1 m
    set column_1 = e.column_1
    from (VALUES %s) AS e (column_2, column_1) 
    where m.column_2 = e.column_2"""

# 自定义模板，对第二列显式转换为timestamptz
template = "(%s, %::timestamptz)"
psycopg2.extras.execute_values(
        cur, query, args, template=template, page_size=100
        )

内容的提问来源于stack exchange，提问作者Süleyman Kamalak