Re: psycopg2 open file for reading

On 10/28/2015 07:23 AM, Dan Sawyer wrote:
> The question relates to copy file commands within a python script. The
> database open and file operations are working correctly.
>
> The string passed to psycopg2 is: copy_string = 'copy temp_tbl from
> '/tmp/test.txt' csv delimiter '|';'
>
> The psycopg2 commands executed tried are:
>
> cursoro.copy_expert(copy_string,f)
> and
> cursoro.execute(copy_string)
>
> Both produce the follow errors:
>
> Traceback (most recent call last):
> File "./norm_name_py_01.py", line 162, in <module>
> main()
> File "./norm_name_py_01.py", line 157, in main
> cursoro.copy_expert(copy_string,f)
> psycopg2.ProgrammingError: could not open file "/tmp/test.txt" for
> reading: Permission denied
>
> ls -altr /tmp/test.txt
> -rw-rw-r--. 1 dan dan 52833 Oct 28 06:07 /tmp/test.txt

COPY is run as the server user and said user needs permissions on the file:

http://www.postgresql.org/docs/current/static/sql-copy.html

"COPY with a file name instructs the PostgreSQL server to directly read
from or write to a file. The file must be accessible by the PostgreSQL
user (the user ID the server runs as) and the name must be specified
from the viewpoint of the server"

The cheat is to read the on disk file and write it into an in memory
file and then use that with STDIN. Something like:

sql_copy = "COPY " + self.pg_tbl_name
sql_copy += " FROM STDIN WITH CSV DELIMITER '\t'"
cur_copy.copy_expert(sql_copy, mem_file)

>
> Dan
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com