0

I have a simple PostgreSQL copy statement that copies a table from a network host (NETWORK-HOST) database to a .csv file on a shared network folder. It works fine in PGAdmin, but when I transfer it to a psycopg2 script, it tells me permission denied. I have double checked to make sure full control is granted to my username on the network share, but that has not made a difference. I am running Windows 10, Python 3 (32 bit), PostgreSQL 9.5.1.

Here is the script in PGAdmin that runs successfully:

copy "Schema".county_check_audit to '\\NETWORK-HOST\NetworkFolder\county_check.csv' delimiter ',' CSV HEADER;

here is the script where I get the permission error:

import psycopg2
connection = psycopg2.connect(database="db", user="postgres", password="password", host="NETWORK-HOST")
cursor = connection.cursor()
cursor.execute("copy \"Schema\".area_check_audit to '\\\\NETWORK-HOST\\NetworkFolder\\area_check.csv' delimiter ',' CSV HEADER;")
connection.commit()

This is the error:

psycopg2.ProgrammingError: could not open file "\\NETWORK-HOST\NetworkFolder\area_check.csv" for writing: Permission denied

Any insights are greatly appreciated.

Matt
  • 967
  • 2
  • 9
  • 23
  • according to the error message, you have to add write access to the file (to the user that is executing you py script i guess) . I'd suggest to test your code first by trying to write to a file that is on the same host and once you are sure your code is fine, you can debug the access rights to your file on another host. – Akli REGUIG Dec 05 '17 at 18:35
  • It appears I am getting the same error when I try to export a table from my localhost to my C: drive so there must be a problem with my code. – Matt Dec 05 '17 at 18:57
  • you have to change your file's security access as mentioned in this post : https://stackoverflow.com/a/22486389/1360584 – Akli REGUIG Dec 05 '17 at 19:03
  • you were correct, except I needed to add the 'write' permissions as well as the 'read' permissions in the settings. – Matt Dec 05 '17 at 19:18
  • 1
    @AkliREGUIG if you want to add this as an answer I will give you credit. – Matt Dec 06 '17 at 13:41

1 Answers1

0

According to the error message, you have to add write access to the file.

To change file'security access on windows, check : Permission denied when trying to import a CSV file from PGAdmin

I'd suggest to test your code first by trying to write to a file that is on the same host and once you are sure your code is fine, you can debug the access rights to your file on another host.

Akli REGUIG
  • 552
  • 4
  • 13