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(at)earthlink(dot)net
>>>>> <mailto:dansawyer(at)earthlink(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>