Re: Escaping input from COPY

On Wednesday, December 21, 2011 3:16:42 pm Josh Kupershmidt wrote:
> On Tue, Dec 20, 2011 at 7:47 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
wrote:
> > As far as I know you did not get an answer, which is not the same as
> > there being no answer:) I think you will find that the escaping is
> > handled for you.
>
> I am rather dubious of the claim that "escaping is handled for you"
> with copy_from(). Let us look at this example from psycopg2's
> copy_from.py example code:
>

The issue is COPY needs to see data in proper format and so yes some massaging
is necessary. For simple cases copy_from() is sufficient ,for more complex cases
see below.

>
> But only because none of the rows happen to contain any characters
> which must be be escaped. How are you supposed to use copy_from() with
> arbitrary text, e.g.

In this case copy_expert is your friend:

rows = [('Strange\t\tFirst\\Name', 'Last\nName', 100)]
f=StringIO.StringIO()
c=csv.writer(f,delimiter='|')
for row in rows:
c.writerow(row)
f.seek(0)
cur.copy_expert("copy copy_t from stdin with csv delimiter '|'",f)

test(5432)aklaver=>SELECT * from copy_t ;
fld_1 | fld_2 | fld_3
----------------------------+-------+-------
Strange First\Name | Last +| 100
| Name |

At any rate this more appropriately handled on the psycopg list, so if you want
to explore this further we can continue over there.

>
> rows = [('Strange\t\tFirst\\Name', 'Last\nName', 100),
> ]
>
> because that sure doesn't seem to be handled automagically. Yes, I
> know I can write my own escaping code, but as Roger points out that's
> not ideal.
>
> Josh

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com