Literal Escaped Octets

[Chason Hayes]

On Tue, 07 Feb 2006 01:58:00 +0000, Steve Holden wrote:

> Chason Hayes wrote:
>> On Mon, 06 Feb 2006 13:39:17 +0000, Steve Holden wrote:
> [...]
>>>
>>>The URL you reference is discussing how you represent arbitrary values 
>>>in string literals. If you already have the data in a Python string the 
>>>best advise is to use a parameterized query - that way your Python DB 
>>>API module will do the escaping for you!
>>>
>>>regards
>>>  Steve
>> 
>> 
>> Thanks for the input. I tried that with a format string and a
>> dictionary, but I still received a database error indicating illegal
>> string values. This error went away completely when I used a test file
>> consisting only of text, but reproduced everytime with a true binary file.
>> If you can let me know where I am wrong or show me a code snippet with a
>> sql insert that contains a variable with raw binary data that works,
>> I would greatly appreciate it.
>> 
> I tried and my experience was exactly the same, which made me think less 
> of PostgreSQL.
> 
> They don't seem to implement the SQL BLOB type properly, so it looks as 
> though that rebarbative syntax with all the backslashes is necessary. Sorry.
> 
> regards
>   Steve

with regards to escaping data parameters I have found that I have to
specifically add quotes to my strings for them to be understood by
pstgresql. For example

ifs=open("binarydatafile","r")
binarydata=ifs.read()
stringdata=base64.encodestring(binarydata)

#does not work
cursor.execute("insert into binarytable values(%s)" % stringdata)

#need to do this first
newstringdata = "'" + stringdata + "'"

then the select statment works.
Is this expected behavior? Is there a better way of doing this?

thanks for any insight
Chason