Re: psycopg2 open file for reading

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
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>