Re: Escaping input from COPY

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:

data = StringIO.StringIO()
data.write('\n'.join(['Tom\tJenkins\t37',
'Madonna\t\N\t45',
'Federico\tDi Gregorio\t\N']))

data.seek(0)
curs.copy_from(data, 'test_copy')

This works because the strings have essentially been escaped by hand,
and None turned into '\N'. So let's say you had the same data, without
the escaping being done by hand, like this:

rows = [('Tom', 'Jenkins', 37),
('Madonna', None, 45),
('Federico', 'Di Gregorio', None),]

You could get away with:

data = StringIO.StringIO()
for row in rows:
data.write('\t'.join([str(el) if el is not None else '\\N' for el in row]))
data.write('\n')

data.seek(0)
curs.copy_from(data, 'test_copy')

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.

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