Python gotcha with boolean variables

If you don't use python, don't worry, this is a bit long.

On Friday, August 29, 2003, at 12:40 PM, Cath Lawrence wrote:
> If I try to set a boolean to 'f' it works; if I set it to 't' it
> actually sets it to false... You'd think if I'm not allowed to use a
> string it would raise an error?
> Eh. No matter. My problem is solved but it looks like a common gotcha.
> So is there an FAQ on this?

A month ago I sent this and got a reply saying no, it works. My mistake
for not being specific enough. Well, let me elaborate; someone may find
this handy. It's python specific and annoying.

Here's the command line stuff:
insert into bool_test values('pqsl cmd line \'f\'', 'f');
INSERT 2099244 1
insert into bool_test values('pqsl cmd line \'t\'', 't');
INSERT 2099245 1
insert into bool_test values('pqsl cmd line t', t);
ERROR: Attribute 't' not found
insert into bool_test values('pqsl cmd line TRUE', TRUE);
INSERT 2099246 1
insert into bool_test values('pqsl cmd line some random string', 'Some
random string');
ERROR: Bad boolean external representation 'Some random string'

So psql fails to do the insert where it doesn't understand. All well
and good.
(0 and 1 get you casting errors, in case you wondered)

Mostly I'm using python. Pygresql, specifically. This is the one that
gets installed automatically when you configure postgresql --with-python

My test script:
---------------
#!/usr/bin/env python
import pg
def testinsert(rem, val, conn):
try:
insertdict = {"remark": rem,
"value": val }
value = conn.insert("bool_test", insertdict)["value"]
print "Insert of value %s (%s) returns %s" %(val, rem, value)
except:
print "Insert of value %s (%s) fails, generates error " %(val,
rem)

# Set up a DB connection.
conn = pg.DB(dbname="trivial", host="localhost", user="cath")
testinsert("Python 0", 0, conn)
testinsert("Python 1", 1, conn)
testinsert("t", "t", conn)
testinsert("f", "f", conn)
testinsert("'t'", "'t'", conn) # Bearing in mind that the command
line version wants to see 't', not t.
testinsert("'f'", "'f'", conn)
testinsert("TRUE", "TRUE", conn)
testinsert("'TRUE'", "'TRUE'", conn)
testinsert("FALSE", "FALSE", conn)
testinsert("'FALSE'", "'FALSE'", conn)
testinsert("'blah blah blah'", "'blah blah blah'", conn)
--------------
RESULTS:
Insert of value 0 (Python 0) fails, generates error
Insert of value 1 (Python 1) fails, generates error
Insert of value t (t) returns t
Insert of value f (f) returns f
Insert of value 't' ('t') returns f
Insert of value 'f' ('f') returns f
Insert of value TRUE (TRUE) returns t
Insert of value 'TRUE' ('TRUE') returns f
Insert of value FALSE (FALSE) returns f
Insert of value 'FALSE' ('FALSE') returns f
Insert of value 'blah blah blah' ('blah blah blah') returns f

Lesson:
In the python API, you must use t, f, TRUE, FALSE. This seems OK. But
the gotcha is that trying 't' and 'TRUE' result in FALSE values, not
failures or exceptions. I think this is specifically a python thing.

Cath Lawrence, Cath(dot)Lawrence(at)anu(dot)edu(dot)au
Senior Scientific Programmer, Centre for Bioinformation Science,
John Curtin School of Medical Research (room 4088)
Australian National University, Canberra ACT 0200
ph: (02) 61257959 mobile: 0421-902694 fax: (02) 61252595