使用PostgreSQL中的psycopg3结果将CSV数据导入psycopg.errors.InvalidDatetimeFormat

Question

我有一个CSV文件my_table.csv，如下所示：

"dt_start","my_int_value","my_double_value","dt_version"
"2022-01-02 00:00:00",2,2.2,"2022-01-02 00:00:00"
"2022-01-03 00:00:00",3,3.3,"2022-01-03 00:00:00"

现在，我只想使用PostgreSQL包中的使用说明 (使用psycopg==3.1.3和psycopg-binary==3.1.3)将这个文件导入到PostgreSQL数据库的表使用说明中。

我的代码如下：

import os
import psycopg


table_name = "my_table"

conn = psycopg.connect(
    dbname="MY_DB",
    user="MY_USER",
    password="MY_PW",
    host="MY_HOST",
    port="MY_PORT",
)

with conn:
    with conn.cursor() as cur:

        # create table
        cur.execute(
            f"""
            CREATE TABLE IF NOT EXISTS {table_name} (
                dt_start TIMESTAMP NOT NULL,
                my_int_value INT NOT NULL,
                my_double_value DOUBLE PRECISION NOT NULL,
                dt_version TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY(dt_start, my_int_value, my_double_value, dt_version)
            )
            """
        )

        # clear table
        cur.execute(f"TRUNCATE {table_name}")
        conn.commit()

        # insert one row
        cur.execute(
            f"""INSERT INTO {table_name}"""
            + f""" (dt_start, my_int_value, my_double_value, dt_version)"""
            + f""" VALUES (%s, %s, %s, %s)""",
            ("2022-01-01 00:00:00", 1, 1.1, "2022-01-01 00:00:00"),
        )
        conn.commit()

        # fetch it
        cur.execute(f"""SELECT * FROM {table_name}""")
        print(cur.fetchall())

        # this breaks with "psycopg.errors.InvalidDatetimeFormat"
        with open(f"""{table_name}.csv""", "r") as f:
            with cur.copy(f"COPY {table_name} FROM STDIN") as copy:
                while data := f.read(100):
                    copy.write(data)
        conn.commit()

使用某些示例数据的第一步工作得很好，但是CSV导入中断时出现了一个错误，如：

psycopg.errors.InvalidDatetimeFormat: invalid syntax for type timestamp without time zone: »"dt_start","my_int_value","my_double_value","dt_version"«    
CONTEXT:  COPY my_table, Row 1, Column dt_start: »"dt_start","my_int_value","my_double_value","dt_version"«

同时，我还尝试了来自docs的不同导入变体和不同的日期时间格式，但所有这些都会导致相同的错误。

关于如何解决这个问题，有什么建议吗？

Answer 1

create table import_test(dt_start timestamp, my_int_value integer, my_double_value float, dt_version timestamp);

cat import_test.csv
"dt_start","my_int_value","my_double_value","dt_version"
"2022-01-02 00:00:00",2,2.2,"2022-01-02 00:00:00"
"2022-01-03 00:00:00",3,3.3,"2022-01-03 00:00:00"

import psycopg
from psycopg import sql

con = psycopg.connect("dbname=test user=postgres host=localhost port=5432")

with open('import_test.csv') as f:
    with cur.copy(sql.SQL('COPY {} FROM STDIN WITH(FORMAT CSV, HEADER)').format(sql.Identifier('import_test'))) as copy:
                while data := f.read(100):
                    copy.write(data)

con.commit()

select * from import_test ;
      dt_start       | my_int_value | my_double_value |     dt_version      
---------------------+--------------+-----------------+---------------------
 2022-01-02 00:00:00 |            2 |             2.2 | 2022-01-02 00:00:00
 2022-01-03 00:00:00 |            3 |             3.3 | 2022-01-03 00:00:00

Answer 2

该文件包含一个标题，请告诉Postgres：

COPY my_table FROM STDIN (FORMAT csv, HEADER true)

检查文件。中的语法