Re: psycopg2 open file for reading

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