trying to copy file cretead by postgresql copy to command using python

Question

I'm trying to send an CSV file that I create using this command :

copy ( select 1 as a ,2 as b ,3 as c) to '/var/lib/pgsql/output.csv' with delimiter ',' csv header;

I'm able to create the file only at /var/lib/pgsql/ because of permission errors that I'm getting. now I'm trying or to move to file to a different location as /home/user/Desktop/someFolder or directly attach it to the mail.

this is the code:

#!/usr/bin/env python

import os
import subprocess
import psycopg2
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
import shutil

def fetch_all_postgres(pgsql_conn):
    cmd = """ copy ( select 1 as a ,2 as b ,3 as c) to '/var/lib/pgsql/output.csv' with delimiter ',' csv header; """
    cur = pgsql_conn.cursor()
    cur.execute(cmd)
    pgsql_conn.commit()


def send_mail():
    msg = MIMEMultipart('alternative')
    msg['Subject'] = "yuval script"
    msg['From'] = From
    msg['To'] = To
    msg['Cc'] = copy
    source_files = '/var/lib/pgsql/output.csv'
    destination_folder = "/home/user/Desktop/somefolder/output.csv"
    
    # first try
    # shutil.move("/var/lib/pgsql/output.csv", "/home/user/Desktop/somefolder/output.csv")
    
    # second try
    # subprocess.run("mv %s %s" % (source_files, destination_folder), shell=True)
    
    # therd try
    # os.system("mv /var/lib/pgsql/output.csv /home/user/Desktop/somefolder/output.csv")
    
    
    file_name = "output.csv"
    attachment = open(source_files, "rb")
    p = MIMEBase('application', 'octet-stream')
    p.set_payload(attachment.read())
    encoders.encode_base64(p)
    p.add_header('Content-Disposition', "attachment; filename= %s" % file_name)
    msg.attach(p)
    s = smtplib.SMTP('smtp.gmail.com', 587)
    s.starttls()
    s.login(From, Passdune)
    s.sendmail(From, To, msg.as_string())
    s.quit()


def main():
    print("connecting to db...")
    conn = psycopg2.connect(CONNECTION_STRING)
    fetch_all_postgres(conn)
    send_mail()
    print("end")


if __name__ == '__main__':
    main()

this is the error if I trying to set the path of the copy to command to a different folder other then /var/lib/pgsql

psycopg2.ProgrammingError: could not open file "/home/yaodav/Desktop/output1.csv" for writing: Permission denied HINT: COPY TO instructs the PostgreSQL server process to write a file. You may want a client-side facility such as psql's \copy.

this is the error on all the mv/copy command that Im trying :

 Permission denied: '/var/lib/pgsql/output1.csv

I also tried this and this

The error message explains the issue `COPY` is run as the Postgres server user(generally postgres). It can only write to directories that user has privileges on. Since you are using `psycopg2` I would look at its built in `copy` commands; [Copy](https://www.psycopg.org/docs/usage.html#copy). — Adrian Klaver, Dec 16 '20 at 16:40

score 1 · Accepted Answer · answered Dec 16 '20 at 20:44

Using copy commands from psycopg2:

cur = con.cursor()
f = open('test.csv', 'w')
cur.copy_expert("copy ( select 1 as a ,2 as b ,3 as c) to stdout with delimiter ',' csv header", f)
f.close()

cat /home/aklaver/test.csv                                                                                                                                                
a,b,c
1,2,3

trying to copy file cretead by postgresql copy to command using python

1 Answers1