SQLwaterheadretard3 (Was: Is it just me, or is Sqlite3 goofy?)

[mensanator at aol.com]

Paul McNett wrote:
> mensanator at aol.com wrote:
> > Do you know what INNER JOIN means?
> >
> > Do you know how important it is to a relational database?
> >
> > Can you explain how an INNER JOIN can even work, in theory,
> > with dynamic data types?
>
> Let's stop the pissing contest and just see how it works. After all,
> this is Python and we can easily just try it out. Here's my example.
> Please tell me how this causes unexpected results,

When we use a best case scenario, we get what we expect.

> and why it isn't SQL.

It isn't SQL simply because SQL won't let you insert text
into a numeric field.

> Please modify my example to get it to cause a catastrophe,

Make it worse case? Sure, I can do that.

> and post it
> here so we can see the errors of our ways and be properly humbled.

#-- Preliminaries:
## from pysqlite2 import dbapi2 as sqlite
import sqlite3 as sqlite
## con = sqlite.connect("test.db")
con = sqlite.connect(":memory:")
cur = con.cursor()

#-- Create 3 tables for a M:M relationship between customers
#-- and categories:
cur.execute("create table customers (id integer primary key
autoincrement, name char)")
cur.execute("create table categories (id integer primary key
autoincrement, name char)")
cur.execute("create table cust_cat (id integer primary key
autoincrement, cust_id integer, cat_id integer)")

##    cur.execute("create table cust_cat (id integer, cust_id,
cat_id)")
##    cur.execute("create table customers (id, name char)")
##    cur.execute("create table categories (id, name char)")
##
##    # Ok, THIS fails. Because the JOINs were originally made against
fields
##    # that were cast as integers so the 'hinting' of sqlite must
operate in a JOIN
##    # allowing ints to JOIN against strings. Take away the casts and
the JOIN
##    # fails. Unfortunately, not every situation will involve JOINing
primary keys.
##
##    [(1, u'Ziggy Marley'), (2, u'David Bowie')]
##    [(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]
##    [(None, 1, 3), (None, 1, u'2'), (None, u'2', u'1'), (None, u'2',
u'3')]
##
##    [(1, u'Ziggy Marley', 3, u'Male Singers')]
##    []
##    []
##    []
##    []
##    [(1, u'Ziggy Marley', 3, u'Male Singers')]


#-- Insert some test data into customer and categories:
cur.execute("insert into customers (name) values ('Ziggy Marley')")
cur.execute("insert into customers (name) values ('David Bowie')")
cur.execute("insert into categories (name) values ('Glam Rock')")
cur.execute("insert into categories (name) values ('Nuevo Reggae')")
cur.execute("insert into categories (name) values ('Male Singers')")

## # if int cast removed, manually insert ids
##cur.execute("insert into customers (id, name) values (1,'Ziggy
Marley')")
##cur.execute("insert into customers (id, name) values (2,'David
Bowie')")
##cur.execute("insert into categories (id, name) values (1,'Glam
Rock')")
##cur.execute("insert into categories (id, name) values (2,'Nuevo
Reggae')")
##cur.execute("insert into categories (id, name) values (3,'Male
Singers')")

cur.execute("select * from customers")


#-- Take a look at the data (and retrieve the pk's):
print cur.fetchall()
#[(1, u'Ziggy Marley'), (2, u'David Bowie')]
cur.execute("select * from categories")
print cur.fetchall()
#[(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]


#-- Relate some customers to some categories. Note how I send strings
#-- in some places and ints in others:
##cur.execute("insert into cust_cat (cust_id, cat_id) values (1, 3)")
##cur.execute("insert into cust_cat (cust_id, cat_id) values (1, '2')")

##cur.execute("insert into cust_cat (cust_id, cat_id) values ('2',
'1')")
##cur.execute("insert into cust_cat (cust_id, cat_id) values ('2', 3)")


##cc = [(1,3),(1,'2'),('2','1'),('2','3')]
cc = [(1,3),(1,'>2'),('>2','>1'),('>2','>3')]

##    # And this also fails (differently). The 'hinting' of sqlite that
operates
##    # during a JOIN only works if the string looks like an integer.
And, no,
##    # the use of the '>' is not a contrived example like 'fred'. I
often get a
##    # result like '>200' in what is supposed to be a numeric field.
##
##    [(1, u'Ziggy Marley'), (2, u'David Bowie')]
##    [(1, u'Glam Rock'), (2, u'Nuevo Reggae'), (3, u'Male Singers')]
##    [(1, 1, 3), (2, 1, u'>2'), (3, u'>2', u'>1'), (4, u'>2', u'>3')]
##
##    [(1, u'Ziggy Marley', 3, u'Male Singers')]
##    []
##    []
##    []
##    [(1, u'Ziggy Marley', 3, u'Male Singers')]
##    [(1, u'Ziggy Marley', 3, u'Male Singers')]

cur.executemany("insert into cust_cat (cust_id, cat_id) values
(?,?)",cc)
cur.execute("select * from cust_cat")
print cur.fetchall()

print

#-- Run some queries:
cur.execute("""
select     customers.id as cust_id,
           customers.name as cust_name,
           categories.id as cat_id,
           categories.name as cat_name
from       customers
inner join cust_cat
on         cust_cat.cust_id = customers.id
inner join categories
on         categories.id = cust_cat.cat_id
order by   2,4""")

print cur.fetchall()
#[(2, u'David Bowie',  1, u'Glam Rock'),
# (2, u'David Bowie',  3, u'Male Singers'),
# (1, u'Ziggy Marley', 3, u'Male Singers'),
# (1, u'Ziggy Marley', 2, u'Nuevo Reggae')]

cur.execute("""
select     customers.id as cust_id,
           customers.name as cust_name,
           categories.id as cat_id,
           categories.name as cat_name
from       customers
inner join cust_cat
on         cust_cat.cust_id = customers.id
inner join categories
on         categories.id = cust_cat.cat_id
where      categories.id = 1
order by   2,4""")

print cur.fetchall()
#[(2, u'David Bowie', 1, u'Glam Rock')]

cur.execute("""
select     customers.id as cust_id,
           customers.name as cust_name,
           categories.id as cat_id,
           categories.name as cat_name
from       customers
inner join cust_cat
on         cust_cat.cust_id = customers.id
inner join categories
on         categories.id = cust_cat.cat_id
where      categories.id = '1'
order by   2,4""")

print cur.fetchall()
#[(2, u'David Bowie', 1, u'Glam Rock')]

cur.execute("""
select     customers.id as cust_id,
           customers.name as cust_name,
           categories.id as cat_id,
           categories.name as cat_name
from       customers
inner join cust_cat
on         cust_cat.cust_id = customers.id
inner join categories
on         categories.id = cust_cat.cat_id
where      categories.id = '2'
order by   2,4""")

print cur.fetchall()
#[(1, u'Ziggy Marley', 2, u'Nuevo Reggae')]

cur.execute("""
select     customers.id as cust_id,
           customers.name as cust_name,
           categories.id as cat_id,
           categories.name as cat_name
from       customers
inner join cust_cat
on         cust_cat.cust_id = customers.id
inner join categories
on         categories.id = cust_cat.cat_id
where      categories.id = '3'
order by   2,4""")

print cur.fetchall()
#[(2, u'David Bowie',  3, u'Male Singers'),
# (1, u'Ziggy Marley', 3, u'Male Singers')]

cur.execute("""
select     customers.id as cust_id,
           customers.name as cust_name,
           categories.id as cat_id,
           categories.name as cat_name
from       customers
inner join cust_cat
on         cust_cat.cust_id = customers.id
inner join categories
on         categories.id = cust_cat.cat_id
where      categories.id = 3
order by   2,4""")

print cur.fetchall()
#[(2, u'David Bowie',  3, u'Male Singers'),
# (1, u'Ziggy Marley', 3, u'Male Singers')]