Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 5792D1DC7C7C
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  7 Nov 2011 04:46:25 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 02578-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  7 Nov 2011 08:46:16 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2
Received: from mail.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk
	[84.51.143.99])
	by mail.postgresql.org (Postfix) with ESMTP id D69031DC7C7A
	for <pgsql-sql@postgresql.org>; Mon,  7 Nov 2011 04:45:51 -0400 (AST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by mail.archonet.com (Postfix) with ESMTP id 665B17C085;
	Mon,  7 Nov 2011 08:44:54 +0000 (GMT)
Message-ID: <4EB79A86.1090005@archonet.com>
Date: Mon, 07 Nov 2011 08:44:54 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.23) Gecko/20110921 Lightning/1.0b2 Thunderbird/3.1.15
MIME-Version: 1.0
To: John Fabiani <johnf@jfcomputer.com>
CC: pgsql-sql@postgresql.org
Subject: Re: the use of $$string$$
References: <201111040738.29303.johnf@jfcomputer.com>
	<201111040826.16177.johnf@jfcomputer.com>
	<4EB429AD.3030607@archonet.com>
	<201111041712.09607.johnf@jfcomputer.com>
In-Reply-To: <201111041712.09607.johnf@jfcomputer.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.918 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	RDNS_DYNAMIC=0.982
X-Spam-Level: 
X-Archive-Number: 201111/25
X-Sequence-Number: 35998

On 05/11/11 00:12, John Fabiani wrote:
> I'm using psycopg2.

OK - bear in mind, I'm not a Python user.

> This is what I'm doing from python
> myvarString = "long string that contains single quotes"
> cusor.execute("insert into table (pkid, myfield) values (%s, $$%s$$)",(123,
> myvarString))
>
> When I execute the above I'm seeing:
> E'long string that contains single quotes' in the field.  When I do a "select
> * from table"   I get E'long string that contains single quotes'.

OK, so it seems psycopg is quoting your strings for you (as you'd 
expect). It's presumably turning your query into:
     ... values (E'123', $$E'<long string>'$$)
So - the $$ quoting is unnecessary here - just use the % placeholders.

Incidentally, should it be %s for the numeric argument?

> myvarString = "long string that without single quotes"
> cusor.execute("insert into table (pkid, myfield) values (%s, %s)",(123,
> myvarString))
>
> I get the following:
>   "long string that without single quotes"

That seems sensible to me (unless there's a typo in your example). You 
shouldn't need to quote any of your values in your Python code - it's 
doing it for you. I'm guessing there are other options beside %s for 
other data-types (integers,floats,boolean etc).

-- 
   Richard Huxton
   Archonet Ltd