CODEWITHSUNDEEP
pythonpostgresqlpsycopg2psycopg
Greg
Greg

Reputation: 47124

Psycopg copy_expert method - How to use properly

I'm trying to run code like this:

query = "copy  (select email from my_table) TO 'STDOUT' WITH (FORMAT csv, DELIMITER '|', QUOTE '^', HEADER FALSE)"
out_file = StringIO()
cursor.copy_expert(query, out_file, size=8192)

Using the copy_expert cursor method.

But I'm getting this error:

Traceback (most recent call last):
  File "etl/scripts/scratch.py", line 32, in <module>
    cursor.copy_expert(query, out_file, size=8192)
psycopg2.ProgrammingError: must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

I'm not in a position to run it as a superuser and it seems like that shouldn't be required since I'm not touching any real files.

Upvotes: 2

Views: 38136

Answers (2)

Santhosh Balasa
Santhosh Balasa

Reputation: 184

You can also use a csv module instead of copy_expert.

import csv

from sqlalchemy.orm import Session
from sqlalchemy import create_engine

_url = f"snowflake://user:test124/snow.com:443/DB/WAREHOUSE/ACCOUNT"
with open("sample.csv", "w") as csv_file:
    with Session(create_engine(_url).engine) as session:
        result = session.execute("select * from my_table")
        data = result.fetchall()
        csv_writer = csv.writer(csv_file)
        csv_writer.writerows(data)

Upvotes: -3

Nick Barnes
Nick Barnes

Reputation: 21336

There are two variants of COPY TO:

  • COPY TO STDOUT, which streams data back to the client, and
  • COPY TO 'filename', which writes to a server-side file (requiring superuser privileges).

Your COPY statement has quotes around the STDOUT keyword, causing it to be interpreted as a filename. Just remove them.

Upvotes: 11

Related Questions