Entry

PostgreSQL doesn't seem to like data-input that includes double/single quotes and produces "parse error", is there a way around this?

Dec 13th, 2000 23:24
hasan kacmaz, Bill.Allie@mug.org

Thanks to 
Billy G. Allie    | Domain....: Bill.Allie@mug.org
the answer to this was quite easy.
Here is the answer he provided :
----------------
_quote() is an internal function that should not be called directly 
(unless you need to, of course).  It is called by the cursor.execute() 
method to convert the parameters passed to it into strings appropiately 
quoted for postgreSQL.  An example call to execute would be:
    The table being referenced:
	CREATE test (
	    a INT4,
	    b FLOAT8,
	    c TEXT
	);
The statement:(NOTE: %s is used no mater the argument type, and they 
are NOT quoted!)
	cursor.execute("insert into test values (%s, %s, %s)",
			123, 9876.5432, "Isn't this fun!")
would send the following statement to postgreSQL:
        insert into test values(123, 9876.5432, 'Isn\'t this fun!')
> 
>     if type(value) == types.StringType:
	_s = repr(value)
> 	if _s[0] == '"':
> 	    _s = (repr((value + '"'))[:-2] + "'")
> 	return _s
> [...]
> ***
Strings sent to postgreSQL have to be quoted with a single quite. In 
Python, if a string has an embedded single quote, repr() will return 
the string quoted with a double quote.  This code will check the result 
of repr() to see it is quoted with a double quote and force Python to 
quote it with single quotes by appending a double quote to the string 
and then removing the double quote from the result of repr().  Forcing 
Python to quote the repr() result with single quotes caused Python to 
properly escape the embeded single quotes, there by making postgreSQL 
happy.  I figured that since Python already knew how to escape the 
embedded quotes, I would let it do it instead of coding my own routine 
to escape the single quotes.
The following should help:
    Python 2.0 (#4, Dec 12 2000, 00:21:14) [C] on unixware5
    Type "copyright", "credits" or "license" for more information.
    >>> a = "It's a fun thing"
    >>> print repr(a)
    "It's a fun thing"
    >>> print repr(a + '"')    
    'It\'s a fun thing"'
    >>> print repr(a + '"')[:-2] + "'"
    'It\'s a fun thing'
I hope this note clairifies things for you.
BTW:  You may want to retrieve the latest code from the CVS 
repository.  It contains a number of minor bug fixes.
Later.
-- 
____       | Billy G. Allie    | Domain....: Bill.Allie@mug.org
|  /|      | 7436 Hartwell     | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/  |LLIE  | (313) 582-1540    |