Re: Escaping input from COPY

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, Roger Leigh <rleigh(at)codelibre(dot)net>
Subject: Re: Escaping input from COPY
Date: 2011-12-21 23:16:42
Message-ID: CAK3UJRGgmBsSv42HtgYujKQQSWOOw+jcNneti9c58pmZQQSxjg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Misa Simic 2011-12-21 23:20:56 Re: [partition table] python fetchall or fetchone function can not get the returning rows
Previous Message Havasvölgyi Ottó 2011-12-21 22:41:51 Re: fsync on ext4 does not work