How to COPY FROM file using psyocopg3 · psycopg/psycopg · Discussion #139

link管理
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
Working implementation

    with open(csv_path, "r") as file:
        with psycopg2.connect(db_url) as conn:
            with conn.cursor() as cur:
                    cur.execute("CREATE TEMP TABLE tmp_data(id uuid, bla jsonb) ON COMMIT DROP;")
                    cur.copy_from(file, "tmp_data", sep="|", null="")
Failing implementation
    with open(csv_path, "r") as file:
        with psycopg.connect(db_url) as conn:
            with conn.cursor() as cur:
                    cur.execute("CREATE TEMP TABLE tmp_data(id uuid, bla jsonb) ON COMMIT DROP;")
                    with cur.copy(
                        f"COPY tmp_data(id, bla) FROM '{csv_path}' (format csv, delimiter '|', quote '\"');"
                    ) as copy:
                        copy.write(file)
This raises an exception, could not open file "stuff.csv" for reading: No such file or directory.
Why would psycopg2 have no trouble finding and using the input file where as the new copy fails to do so?  Is this even the actual source of the error?
      You can (should?) use COPY ... FROM STDIN instead and pass the whole file content to the copy object.

E.g.:
with cur.copy("COPY tmp_data(id, bla) FROM stdin (format csv, delimiter '|', quote '\"')") as copy:
    copy.write(file.read())
        You can (should?) use COPY ... FROM STDIN instead and pass the whole file content to the copy object.

E.g.:
with cur.copy("COPY tmp_data(id, bla) FROM stdin (format csv, delimiter '|', quote '\"')") as copy:
    copy.write(file.read())
        @dlax While that did resolve the question, there seems to be other syntactical issues with my approach.  The next error that was presented:

COPY from stdin failed: error from Python: OperationalError - sending query failed: another command is already in progress                                    

Is the prior step that creates a temporary table conflicting with the subsequent copy call?
>>> with psycopg.connect() as cnn:
...     with cnn.cursor() as cur:
...         cur.execute("create temp table tmpcopy(id int primary key, data text) on commit drop")
...         with cur.copy("copy tmpcopy from stdin (format 'csv')") as copy:
...             copy.write(open("test.csv").read())
...         print (cur.execute("select * from tmpcopy").fetchall())
[(1, 'hello'), (2, 'world')]
maybe you are nesting the CREATE TABLE into the copy block or something like that?
        If the print statement above is moved into the with cur.copy() block you get an error like:
QueryCanceled: COPY from stdin failed: error from Python: OperationalError - sending query failed: another command is already in progress
CONTEXT:  COPY tmpcopy, line 3
so yes, maybe that's it. Maybe we should forbid to run execute in a copy block and throw a descriptive exception.