Обсуждение: psycopg2 open file for reading

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

Dan

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.klaver@aklaver.com

On 10/28/2015 07:42 AM, Shulgin, Oleksandr wrote:
> On Wed, Oct 28, 2015 at 3:35 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>
>     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)
>
>
> And you don't actually need a "memory file", any object implementing
> "read" method, such as a normal fie object should just work:
>
> file=open('1.txt', 'r')
> cur_copy.copy_expert(sql_copy, file)

Hmm, I was over thinking this. Thanks for the simplification.

>
> --
> Alex
>


--
Adrian Klaver
adrian.klaver@aklaver.com

The file open command is:

        f = open("/tmp/test.txt","r")

Is this correct? Is the read method syntax correct

On Wed, Oct 28, 2015 at 3:35 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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)

And you don't actually need a "memory file", any object implementing "read" method, such as a normal fie object should just work:

file=open('1.txt', 'r')

cur_copy.copy_expert(sql_copy, file)

--

Alex

Yes. That is what is producing the errors. The pertinent lines are:

        conn = psycopg2.connect(conn_string)
        cursoro = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
            ... processing python code
            ... creates test.txt
            ... closes test.txt
        f = open("/tmp/test.txt","r")
        copy_string = 'copy temp_tbl from '/tmp/test.txt' csv delimiter '|';'
        cursoro.copy_expert(copy_string, file)
Is a cursor the wrong way to reference copy_expert ?

On Wed, Oct 28, 2015 at 4:04 PM, Dan Sawyer <dansawyer@earthlink.net> wrote:

The file open command is:

        f = open("/tmp/test.txt","r")

Is this correct? Is the read method syntax correct

Well, it looks correct.  Do you get any errors with that?

On 10/28/2015 07:42 AM, Shulgin, Oleksandr wrote:

On Wed, Oct 28, 2015 at 3:35 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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)

And you don't actually need a "memory file", any object implementing "read" method, such as a normal fie object should just work:

file=open('1.txt', 'r')

cur_copy.copy_expert(sql_copy, file)

--

Alex

On 10/28/2015 08:25 AM, Dan Sawyer wrote:
> Yes. That is what is producing the errors. The pertinent lines are:
>
>          conn = psycopg2.connect(conn_string)
>          cursoro = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
>              ... processing python code
>              ... creates test.txt
>              ... closes test.txt
>          f = open("/tmp/test.txt","r")
>          copy_string = 'copy temp_tbl from '/tmp/test.txt' csv delimiter
> '|';'
>          cursoro.copy_expert(copy_string, file)
>
> Is a cursor the wrong way to reference copy_expert ?

No, but this:

'copy temp_tbl from '/tmp/test.txt'

needs to be:

'copy temp_tbl from FROM STDIN'

See:

http://initd.org/psycopg/docs/cursor.html#cursor.copy_expert
>
> On 10/28/2015 08:06 AM, Shulgin, Oleksandr wrote:
>> On Wed, Oct 28, 2015 at 4:04 PM, Dan Sawyer <dansawyer@earthlink.net
>> <mailto:dansawyer@earthlink.net>> wrote:
>>
>>     The file open command is:
>>
>>             f = open("/tmp/test.txt","r")
>>
>>     Is this correct? Is the read method syntax correct
>>
>>
>> Well, it looks correct.  Do you get any errors with that?
>>
>>     On 10/28/2015 07:42 AM, Shulgin, Oleksandr wrote:
>>>     On Wed, Oct 28, 2015 at 3:35 PM, Adrian Klaver
>>>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>>>
>>>
>>>         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)
>>>
>>>
>>>     And you don't actually need a "memory file", any object
>>>     implementing "read" method, such as a normal fie object should
>>>     just work:
>>>
>>>     file=open('1.txt', 'r')
>>>     cur_copy.copy_expert(sql_copy, file)
>>>
>>>     --
>>>     Alex
>>
>>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/28/2015 09:41 AM, Dan Sawyer wrote:

CCing list

> Thank you. That references std in and out.

Yes because you are using copy_expert(sql, file, size=8192) versus
copy_from(file, table, sep='\t', null='\\N', size=8192, columns=None).
In copy_from and copy_to you specify the file and the table and psycopg2
builds the SQL using that and the other arguments that are set or that
you set. In copy_expert() you create the SQL and pass it in. You also
specify a file but psycopg2 does not know whether you are reading from
that file or writing to it. All it does is make it available for the SQL
that you created to use. In that SQL you need to say either STDIN for
reading from the file in the case of COPY FROM of STDOUT in the case of
COPY TO. The SQL that supplied then reads or writes the file that was
passed in and does the appropriate action. If you want to copy_expert()
that is how you have to set up the query.

>
> Is there a way to 'copy' from a file?

Yes, see above.

>
> If so is the code snippet below correct or is it in error?

It is in error for the reasons above. Just change '/tmp/test.txt' to
STDIN. FYI the ':' is not needed, psycopg2 will do the right thing
without it.

>
>
> On 10/28/2015 08:28 AM, Adrian Klaver wrote:
>> On 10/28/2015 08:25 AM, Dan Sawyer wrote:
>>> Yes. That is what is producing the errors. The pertinent lines are:
>>>
>>>          conn = psycopg2.connect(conn_string)
>>>          cursoro =
>>> conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
>>>              ... processing python code
>>>              ... creates test.txt
>>>              ... closes test.txt
>>>          f = open("/tmp/test.txt","r")
>>>          copy_string = 'copy temp_tbl from '/tmp/test.txt' csv delimiter
>>> '|';'
>>>          cursoro.copy_expert(copy_string, file)
>>>
>>> Is a cursor the wrong way to reference copy_expert ?
>>
>> No, but this:
>>
>> 'copy temp_tbl from '/tmp/test.txt'
>>
>> needs to be:
>>
>> 'copy temp_tbl from FROM STDIN'
>>
>> See:
>>
>> http://initd.org/psycopg/docs/cursor.html#cursor.copy_expert
>>>
>>> On 10/28/2015 08:06 AM, Shulgin, Oleksandr wrote:
>>>> On Wed, Oct 28, 2015 at 4:04 PM, Dan Sawyer <dansawyer@earthlink.net
>>>> <mailto:dansawyer@earthlink.net>> wrote:
>>>>
>>>>     The file open command is:
>>>>
>>>>             f = open("/tmp/test.txt","r")
>>>>
>>>>     Is this correct? Is the read method syntax correct
>>>>
>>>>
>>>> Well, it looks correct.  Do you get any errors with that?
>>>>
>>>>     On 10/28/2015 07:42 AM, Shulgin, Oleksandr wrote:
>>>>>     On Wed, Oct 28, 2015 at 3:35 PM, Adrian Klaver
>>>>>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>>>>> wrote:
>>>>>
>>>>>
>>>>>         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)
>>>>>
>>>>>
>>>>>     And you don't actually need a "memory file", any object
>>>>>     implementing "read" method, such as a normal fie object should
>>>>>     just work:
>>>>>
>>>>>     file=open('1.txt', 'r')
>>>>>     cur_copy.copy_expert(sql_copy, file)
>>>>>
>>>>>     --
>>>>>     Alex
>>>>
>>>>
>>>
>>
>>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

As a follow up the following worked:

     f = open("/tmp/test.txt","r")
     cursoro.copy_from(f, 'temp_tbl', sep = '|',
columns=('norm_int_name', 'row_num'))     # this works

If I read the description from Adrian correctly copy_expert should work
if the STDIN option was used. I will try that independently.

Thank you,    Dan

On 10/28/2015 10:04 AM, Adrian Klaver wrote:
> On 10/28/2015 09:41 AM, Dan Sawyer wrote:
>
> CCing list
>
>> Thank you. That references std in and out.
>
> Yes because you are using copy_expert(sql, file, size=8192) versus
> copy_from(file, table, sep='\t', null='\\N', size=8192, columns=None).
> In copy_from and copy_to you specify the file and the table and
> psycopg2 builds the SQL using that and the other arguments that are
> set or that you set. In copy_expert() you create the SQL and pass it
> in. You also specify a file but psycopg2 does not know whether you are
> reading from that file or writing to it. All it does is make it
> available for the SQL that you created to use. In that SQL you need to
> say either STDIN for reading from the file in the case of COPY FROM of
> STDOUT in the case of COPY TO. The SQL that supplied then reads or
> writes the file that was passed in and does the appropriate action. If
> you want to copy_expert() that is how you have to set up the query.
>
>>
>> Is there a way to 'copy' from a file?
>
> Yes, see above.
>
>>
>> If so is the code snippet below correct or is it in error?
>
> It is in error for the reasons above. Just change '/tmp/test.txt' to
> STDIN. FYI the ':' is not needed, psycopg2 will do the right thing
> without it.
>
>>
>>
>> On 10/28/2015 08:28 AM, Adrian Klaver wrote:
>>> On 10/28/2015 08:25 AM, Dan Sawyer wrote:
>>>> Yes. That is what is producing the errors. The pertinent lines are:
>>>>
>>>>          conn = psycopg2.connect(conn_string)
>>>>          cursoro =
>>>> conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
>>>>              ... processing python code
>>>>              ... creates test.txt
>>>>              ... closes test.txt
>>>>          f = open("/tmp/test.txt","r")
>>>>          copy_string = 'copy temp_tbl from '/tmp/test.txt' csv
>>>> delimiter
>>>> '|';'
>>>>          cursoro.copy_expert(copy_string, file)
>>>>
>>>> Is a cursor the wrong way to reference copy_expert ?
>>>
>>> No, but this:
>>>
>>> 'copy temp_tbl from '/tmp/test.txt'
>>>
>>> needs to be:
>>>
>>> 'copy temp_tbl from FROM STDIN'
>>>
>>> See:
>>>
>>> http://initd.org/psycopg/docs/cursor.html#cursor.copy_expert
>>>>
>>>> On 10/28/2015 08:06 AM, Shulgin, Oleksandr wrote:
>>>>> On Wed, Oct 28, 2015 at 4:04 PM, Dan Sawyer <dansawyer@earthlink.net
>>>>> <mailto:dansawyer@earthlink.net>> wrote:
>>>>>
>>>>>     The file open command is:
>>>>>
>>>>>             f = open("/tmp/test.txt","r")
>>>>>
>>>>>     Is this correct? Is the read method syntax correct
>>>>>
>>>>>
>>>>> Well, it looks correct.  Do you get any errors with that?
>>>>>
>>>>>     On 10/28/2015 07:42 AM, Shulgin, Oleksandr wrote:
>>>>>>     On Wed, Oct 28, 2015 at 3:35 PM, Adrian Klaver
>>>>>>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>>>>>> wrote:
>>>>>>
>>>>>>
>>>>>>         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)
>>>>>>
>>>>>>
>>>>>>     And you don't actually need a "memory file", any object
>>>>>>     implementing "read" method, such as a normal fie object should
>>>>>>     just work:
>>>>>>
>>>>>>     file=open('1.txt', 'r')
>>>>>>     cur_copy.copy_expert(sql_copy, file)
>>>>>>
>>>>>>     --
>>>>>>     Alex
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>