Re: How to mimic Perl's DBD::Pg's do('COPY...')/pg_putline/pg_endcopy ?

----- "Sean Davis" <sdavis2-2loH/[email protected]> wrote:

> On Wed, Oct 14, 2009 at 10:25 AM, Kynn Jones < [email protected] >
> wrote:
> 
> 
> 
> 
> 
> In the examples section of the unpacked tarball there are two
> examples,
> copy_to.py and copy_from.py.
> 
> 
> 
> Sorry, I should have pointed out that I had looked at those examples,
> and they do not illustrate the functionality I described in my
> original message. Those examples require either 1) creating a
> temporary file from which the data could be read; or 2) create a
> "file-like object" resident in memory. Either of these two
> requirements is inconvenient when the amount of data to be processed
> is large (which is precisely the times when one would want to use
> PostgreSQL's COPY feature).
> 
> For option 2), the "file-like object" does not need to be created in
> memory. If I recall, you just need an object with read and readline
> methods; this could be a buffered reader from disk, a stream
> processor, or anything else that supplies read and readline as methods
> to get the next chunk of data. The data need not be in memory all at
> once.
> 
> Sean

The issue is not reading from the file. That can be dealt with in several ways, including using copy_expert():

copy_expert(sql, file, size=None) -- Submit a user-composed COPY statement.
`file` must be an open, readable file for COPY FROM or an open, writeable
 file for COPY TO. The optional `size` argument, when specified for a COPY
 FROM statement, will be passed to file's read method to control the read
 buffer size.

The problem would be doing the line by line selection within the context of the COPY. To get around this I have usually used the Python CSV module to read in data and then Psycopg to write it out via execute().