使用psycopg3將CSV資料匯入PostgreSQL會導致psycopg.errors.InvalidDatetimeFormat

我有一個my_table.csv如下所示的 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"

現在我只想使用包中的指令（使用 and ）將這個檔案從 Python 匯入到我的PostgreSQL資料庫的my_table表中。psycopg3psycopg==3.1.3psycopg-binary==3.1.3

我的代碼如下所示：

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

同時，我還嘗試了檔案中的不同匯入變體和不同的日期時間格式，但都導致相同的錯誤。

有關如何解決此問題的任何提示？

uj5u.com熱心網友回復：

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

uj5u.com熱心網友回復：

該檔案包含一個標題，告訴 Postgres：

COPY my_table FROM STDIN (FORMAT csv, HEADER true)

檢查檔案中的語法。

標籤：PythonPostgreSQL心理咨询师3

上一篇：無法創建postgres服務器(pgAdmin)

下一篇：Laravel中字串長度的規則驗證