a gadfly SQL question --- table creation

[Andy Todd]

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.
>
>If I create a few tables like this:
>
>#####
>import gadfly
>
>connection = gadfly.gadfly()
>connection.startup("jnso", ".\\ndata")
>cursor = connection.cursor()
>cursor.execute("create table students (st_id varchar(6),gr_level
>varchar(1), grcard_type varchar(2), last_name varchar(40), first_name
>varchar(40))") 
>
>connection.commit()
>####
>
>The tables will *not* be in this order in the database. Now, I expect
>that rows will be inserted at random, but *tables* also?
>
>It does not get in the way all that much, but my "pretty print" looks
>like hell. The first name is all the way to the left while the last name
>is all the way to the right.
>
>Does anyone know how to say "in this darned order" in SQL ????
>
>Regards, Joe 
>

Joe,

You're running into the major problem people first have when dealing with 
RDBMS and SQL. This isn't a Gadfly problem, allow me to explain.

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.

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).

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
-- 
Content free posts a speciality