Re: psycopg2 open file for reading - Mailing list psycopg
From | Dan Sawyer |
---|---|
Subject | Re: psycopg2 open file for reading |
Date | |
Msg-id | 5631188E.1070608@earthlink.net Whole thread Raw |
In response to | Re: psycopg2 open file for reading (Adrian Klaver <adrian.klaver@aklaver.com>) |
List | psycopg |
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 >>>>> >>>>> >>>> >>> >>> >> > >