a gadfly SQL question --- table creation

[Joe Potter]

On Thu, 05 Jul 2001 00:55:36 GMT, andy_todd at spam.free.yahoo.com (Andy Todd) wrote:

>Joe Potter <jm7potter at hotmail.com> wrote in
><n237ktc10fithjhm8vj4pfkmrq7k4fjop8 at 4ax.com>: 
>
>>Hello all,
>>
>>In working with the Python SQL database called Gadfly I have discovered
>>something that I do not understand.
>>

<snip>



>
>SQL is a set based language. It is not a procedural (or OO) language and 
>the most important aspect of this is that *order does not matter*. With set 
>based operations (ie DML or DDL in SQL - is that enough TLAs ;-) you 
>specify what you want to happen, not how to do it (with suitable exceptions 
>for implementation specific hints, but that doesn't matter to use here).
>
>Let me repeat - you specify what you want to do, the database takes care of 
>the how.
>

Even the tables! My God! 

Ok, I'll live with it.


>Therefore in your table creation statement you are saying "please create a 
>table called students which has columns called st_id, gr_level, 
>grcard_type, last_name and first_name". How gadfly organises this table is 
>up to it (you could almost say that it is none of your business).
>

Hmmm. Seems like SQL is in charge, not me. Oh well, I knew it would take 
some time to shed my dbase/clipper bad habits.

>Likewise when you create records in the table *there is no guarantee that 
>they will be stored in the order you insert them*. So, if I create two 
>students;
>
>"""
>INSERT INTO students 
>  ( st_id, gr_level, grcard_type, last_name, first_name )
>VALUES
>  ( '1', 'C', 'X', 'Andy', 'Todd')
>"""
>and 
>
>"""
>INSERT INTO students 
>  ( st_id, gr_level, grcard_type, last_name, first_name )
>VALUES
>  ( '2', 'C', 'X', 'Joe', 'Potter')
>"""
>
>Gadfly (and gadfly alone) determines how this data is physically stored. 
>When I retrieve these rows from the table I may get Andy first and Joe 
>second, or vice versa. The next time I retrieve the rows they may be in the 
>same or a different order (although practically they will always be in the 
>same order YOU CANNOT RELY ON THIS). If you want to control the order of 
>retrieval you have to tell the database what you want (you still really 
>cannot tell it how to do it), eg;
>
>"""
>SELECT st_id, first_name, last_name
>FROM   students
>ORDER BY last_name, first_name
>"""
>
>Will always return the result set;
>
>"""
>1, Andy, Todd
>2, Joe, Potter
>"""
>
>in that order - unless the data changes of course.
>
>Also note that in each SQL statement I have specified which columns to use, 
>the order there are physically stored then becomes irrelevant.
>
>This can be confusing at first, but stick with it and pretty soon you will 
>be jumping out of the bath and running down the street ...
>
>Regards,
>Andy

Thanks Andy. That really did help.

Now, might I ask how one gets "1, Andy, Todd" out of Gadfly and back into a Python
object for direct manipulation?



Regards, Joe