如何在内核2的排除值中插入列

Question

我想用熊猫数据的2列来更新一个表。这是我的代码：

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中将str / python转换为具有时区的时间戳？

Answer 1

表创建和初始数据输入。

create table table_1 (column_1 timestamp, column_2 varchar);

insert into table_1 values (current_timestamp,'random_value_2'), (current_timestamp, 'random_value_1');

select * from table_1;
          column_1          |    column_2    
----------------------------+----------------
 11/16/2022 08:00:58.309285 | random_value_2
 11/16/2022 08:00:58.309285 | random_value_1

如您所示，Python代码：

import psycopg2
from psycopg2.extras import execute_values 

con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()

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

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"""

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

DatatypeMismatch: column "column_1" is of type timestamp without time zone but expression is of type text
LINE 3:     set column_1 = e.column_1

con.rollback()

失败的表达式中的错误是文本而不是记录，错误是不同的。这意味着你实际上在做一些不同的事情。

更正代码，将显式强制转换为时间戳：

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

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

con.commit()

从表中选择：

select * from table_1;
          column_1          |    column_2    
----------------------------+----------------
 11/16/2022 08:00:58.309285 | random_value_2
 11/16/2022 08:00:58.309285 | random_value_1