Thread: 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 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 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
f = open("/tmp/test.txt","r")
Is this correct? Is the read method syntax correct
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 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
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 ?
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)
On 10/28/2015 08:06 AM, Shulgin, Oleksandr wrote:
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 correctWell, 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 >>>>> >>>>> >>>> >>> >>> >> > >