sqlobject-discuss

[SQLObject] Inserting Boolean Columns Into PostgreSQL - Again

[Clifford I. <cli...@di...>]

Hi,

I had posted a message a few weeks ago about how I was having problems 
inserting boolean columns into PostgreSQL and Ian thought it might have 
something to do with an earlier version of Python 2.2. That was indeed the 
case since I was using Python 2.2.2 on Mandrake 9.1. Of course that started a 
never ending cycle upgrading various things eventually culminating in 
installing Mandrake 10.0rc1 on another machine. Anyway, now I can insert but, 
SQLObject always inserts a "t" regardless of whether I specify the boolean 
should be true or false. Here is the code:

>>> from SQLObject import *
>>> conn = PostgresConnection('user=cilkay dbname=testdb')
>>> class Person(SQLObject):
...     _connection = conn
...     firstName = StringCol()
...     lastName = StringCol()
...     isMember = BoolCol()
...
>>> Person.createTable()
>>> p = Person.new(firstName="Donald", lastName="Duck", isMember="t")
>>> p = Person.new(firstName="Mickey", lastName="Mouse", isMember="f")
>>> p = Person.new(firstName="Minnie", lastName="Mouse", isMember="0")
>>> p = Person.new(firstName="Pluto", lastName="Dog", isMember="False")
>>> p = Person.new(firstName="Goofy", lastName="Dog", isMember="false")

testdb=# select * from person;
 id | last_name | is_member | first_name
----+-----------+-----------+------------
  1 | Duck      | t         | Donald
  2 | Mouse     | t         | Mickey
  3 | Mouse     | t         | Minnie
  4 | Dog       | t         | Pluto
  5 | Dog       | t         | Goofy
(5 rows)

As you can see, I tried various ways of expressing "False" but none of them 
seemed to work. What am I missing? Also, why is the order of the columns not 
the same as the order that was specified in the class definition?

Regards,

Clifford Ilkay
Dinamis Corporation
3266 Yonge Street, Suite 1419
Toronto, ON  M4N 3P6
Canada

Tel: 416-410-3326

Re: [SQLObject] Inserting Boolean Columns Into PostgreSQL - Again

[Ian B. <ia...@co...>]

Clifford Ilkay wrote:
> Hi,
> 
> I had posted a message a few weeks ago about how I was having problems 
> inserting boolean columns into PostgreSQL and Ian thought it might have 
> something to do with an earlier version of Python 2.2. That was indeed the 
> case since I was using Python 2.2.2 on Mandrake 9.1. Of course that started a 
> never ending cycle upgrading various things eventually culminating in 
> installing Mandrake 10.0rc1 on another machine. Anyway, now I can insert but, 
> SQLObject always inserts a "t" regardless of whether I specify the boolean 
> should be true or false. Here is the code:
> 
> 
>>>>from SQLObject import *
>>>>conn = PostgresConnection('user=cilkay dbname=testdb')
>>>>class Person(SQLObject):
> 
> ...     _connection = conn
> ...     firstName = StringCol()
> ...     lastName = StringCol()
> ...     isMember = BoolCol()
> ...
> 
>>>>Person.createTable()
>>>>p = Person.new(firstName="Donald", lastName="Duck", isMember="t")
>>>>p = Person.new(firstName="Mickey", lastName="Mouse", isMember="f")
>>>>p = Person.new(firstName="Minnie", lastName="Mouse", isMember="0")
>>>>p = Person.new(firstName="Pluto", lastName="Dog", isMember="False")
>>>>p = Person.new(firstName="Goofy", lastName="Dog", isMember="false")

Those are all "true" values in the Python sense (any non-empty string is 
considered true).  0 (the integer) or None or False are all false in the 
Python sense.

> testdb=# select * from person;
>  id | last_name | is_member | first_name
> ----+-----------+-----------+------------
>   1 | Duck      | t         | Donald
>   2 | Mouse     | t         | Mickey
>   3 | Mouse     | t         | Minnie
>   4 | Dog       | t         | Pluto
>   5 | Dog       | t         | Goofy
> (5 rows)
> 
> As you can see, I tried various ways of expressing "False" but none of them 
> seemed to work. What am I missing? Also, why is the order of the columns not 
> the same as the order that was specified in the class definition?

I'm not sure what you mean by the order.

   Ian

Re: [SQLObject] Inserting Boolean Columns Into PostgreSQL - Again

[CLIFFORD I. <cli...@di...>]

Hello Ian,

At 13:07 09/03/2004 -0600, Ian Bicking wrote:
>Clifford Ilkay wrote:
[snip]
>>Anyway, now I can insert but, SQLObject always inserts a "t" regardless 
>>of whether I specify the boolean should be true or false. Here is the code:
>>
>>>>>from SQLObject import *
>>>>>conn = PostgresConnection('user=cilkay dbname=testdb')
>>>>>class Person(SQLObject):
>>...     _connection = conn
>>...     firstName = StringCol()
>>...     lastName = StringCol()
>>...     isMember = BoolCol()
>>...
>>
>>>>>Person.createTable()
>>>>>p = Person.new(firstName="Donald", lastName="Duck", isMember="t")
>>>>>p = Person.new(firstName="Mickey", lastName="Mouse", isMember="f")
>>>>>p = Person.new(firstName="Minnie", lastName="Mouse", isMember="0")
>>>>>p = Person.new(firstName="Pluto", lastName="Dog", isMember="False")
>>>>>p = Person.new(firstName="Goofy", lastName="Dog", isMember="false")
>
>Those are all "true" values in the Python sense (any non-empty string is 
>considered true).  0 (the integer) or None or False are all false in the 
>Python sense.

Thank you very much for pointing out this simple error on my part. Once I 
used isMember=False or isMember=True, it worked as expected.

>>testdb=# select * from person;
>>  id | last_name | is_member | first_name
>>----+-----------+-----------+------------
>>   1 | Duck      | t         | Donald
>>   2 | Mouse     | t         | Mickey
>>   3 | Mouse     | t         | Minnie
>>   4 | Dog       | t         | Pluto
>>   5 | Dog       | t         | Goofy
>>(5 rows)
>>As you can see, I tried various ways of expressing "False" but none of 
>>them seemed to work. What am I missing? Also, why is the order of the 
>>columns not the same as the order that was specified in the class definition?
>
>I'm not sure what you mean by the order.

According to the docs, the SQL create script that SQLObject would execute 
when we run Person.tableCreate() for the above class should be:

create table person (
         id int primary key autoincrement,
         first_name text,
         last_name text,
         is_member boolean
);

If you look at the output of the describe table below, you will notice that 
the order of the columns is not as we would have expected if the SQL create 
script were actually as above.

testdb=# \d person
                              Table "public.person"
    Column   |  Type   |                      Modifiers
------------+---------+------------------------------------------------------
  id         | integer | not null default nextval('public.person_id_seq'::text)
  last_name  | text    |
  is_member  | boolean |
  first_name | text    |
Indexes:
     "person_pkey" primary key, btree (id)

If the order of the columns is not as one would expect, that would wreck 
INSERT statements that rely on the position of columns rather than the 
absolute name of the columns. This is probably not a problem in SQLObject 
but it could be if one uses other tools to manipulate the same database.

Regards,

Clifford Ilkay
Dinamis Corporation
3266 Yonge Street, Suite 1419
Toronto, Ontario
Canada M4N 3P6

Tel: 416-410-3326 

[SQLObject] Re: Inserting Boolean Columns Into PostgreSQL - Again

[Chris G. <ch...@il...>]

"CLIFFORD ILKAY" <cli...@di...> wrote in message
news:5.2.0.9.0.20040310151025.04674140@localhost...
> If the order of the columns is not as one would expect, that would wreck
> INSERT statements that rely on the position of columns rather than the
> absolute name of the columns. This is probably not a problem in SQLObject
> but it could be if one uses other tools to manipulate the same database.

Eeeew.. there are tools that use the column number rather than the name?!
What a horrible coding practice.

[SQLObject] Re: Inserting Boolean Columns Into PostgreSQL - Again

[Chris G. <ch...@il...>]

Ian answered your first question, so I'll field the second one. :)

"Clifford Ilkay" <cli...@di...> wrote in message
news:200...@di......
> testdb=# select * from person;
>  id | last_name | is_member | first_name
> ----+-----------+-----------+------------
>   1 | Duck      | t         | Donald
>   2 | Mouse     | t         | Mickey
>   3 | Mouse     | t         | Minnie
>   4 | Dog       | t         | Pluto
>   5 | Dog       | t         | Goofy
> (5 rows)
>
> As you can see, I tried various ways of expressing "False" but none of
them
> seemed to work. What am I missing? Also, why is the order of the columns
not
> the same as the order that was specified in the class definition?

I haven't looked this up, but this is probably what's happening:

When you create a subclass of an SQLObject, you're creating a new python
object. To create the table, SQLObject has to inspect this python object's
attributes to see which of them are Col() classes. Now, internally, python
stores all the attributes of a class in a dict(), so that when you look up
an attribute, it just does a lookup in the dict(). The reason they're out of
order is that dictionaries don't maintain any kind of order. The order of
the things in a dictionary depends on the order you inserted them. So,
basically, either SQLObject would have to pre-sort the dictionary, or it
would have to be psychic.

I've been doing some research into psychic computing, and the early results
have been promising, so don't rule that out! :)

Re: [SQLObject] Re: Inserting Boolean Columns Into PostgreSQL - Again

[CLIFFORD I. <cli...@di...>]

At 06:14 10/03/2004 -0500, Chris Gahan wrote:
>Ian answered your first question, so I'll field the second one. :)
>
>"Clifford Ilkay" <cli...@di...> wrote in message
>news:200403091343.17553.clifford_ilkay at dinamis.com...
> > testdb=# select * from person;
> >  id | last_name | is_member | first_name
> > ----+-----------+-----------+------------
> >   1 | Duck      | t         | Donald
> >   2 | Mouse     | t         | Mickey
> >   3 | Mouse     | t         | Minnie
> >   4 | Dog       | t         | Pluto
> >   5 | Dog       | t         | Goofy
> > (5 rows)
> >
> > As you can see, I tried various ways of expressing "False" but none of
>them
> > seemed to work. What am I missing? Also, why is the order of the columns
>not
> > the same as the order that was specified in the class definition?
>
>I haven't looked this up, but this is probably what's happening:
>
>When you create a subclass of an SQLObject, you're creating a new python
>object. To create the table, SQLObject has to inspect this python object's
>attributes to see which of them are Col() classes. Now, internally, python
>stores all the attributes of a class in a dict(), so that when you look up
>an attribute, it just does a lookup in the dict(). The reason they're out of
>order is that dictionaries don't maintain any kind of order. The order of
>the things in a dictionary depends on the order you inserted them. So,
>basically, either SQLObject would have to pre-sort the dictionary, or it
>would have to be psychic.

Hi Chris,

Thanks for explaining that. It is not a big deal for me since I do not 
intend to use SQLObject to create the tables anyway. I have a SQL script 
that I use to do that. Since one has to specify the columns/attributes in 
the new() method anyway, the order of columns makes no difference.

Regards,

Clifford Ilkay
Dinamis Corporation
3266 Yonge Street, Suite 1419
Toronto, Ontario
Canada M4N 3P6

Tel: 416-410-3326