計算機科学のブログ

ほしい物リスト

SQL - SQLite - Python - 外部結合、自己結合、UNION - 新しい戦略 - 左側外部結合, 合致するテーブル

Head First SQL ―頭とからだで覚えるSQLの基本、 Lynn Beighley(著)、 佐藤 直生(監訳)、 松永 多苗子(翻訳)、 オライリージャパンの 10章(外部結合、自己結合、UNION - 新しい戦略)、p.423(自分で考えてみよう)の解答を求めてみる。

schema2_1.sql

drop table if exists girls;
drop table if exists toys;

create table girls(
    girl_id integer primary key autoincrement,
    girl text,
    toy_id integer
);
insert into girls(
    girl,
    toy_id
) values
('ジェン', 1),
('クレオ', 2),
('マンディ', 3);

create table toys(
    toy_id integer primary key autoincrement,
    toy text
);
insert into toys(
    toy
) values
('水鉄砲'),
('クレイジー・ストロー');

schema2_1_1.sql

drop table if exists girls;
drop table if exists toys;

create table girls(
    girl_id integer primary key autoincrement,
    girl text,
    toy_id integer
);
insert into girls(
    girl,
    toy_id
) values
('ジェン', 1),
('クレオ', 2),
('マンディ', 3);

create table toys(
    toy_id integer primary key autoincrement,
    toy text
);
insert into toys(
    toy
) values
('水鉄砲'),
('クレイジー・ストロー');

schema2_2.sql

drop table if exists girls;
drop table if exists toys;

create table girls(
    girl_id integer primary key autoincrement,
    girl text,
    toy_id integer
);
insert into girls(
    girl,
    toy_id
) values
('ジェン', 1),
('クレオ', 1),
('サリー', 3),
('マーサ', 3);

create table toys(
    toy_id integer primary key autoincrement,
    toy text
);
insert into toys(
    toy
) values
('水鉄砲'),
('クレイジー・ストロー'),
('スリンキー');

schema2_2_1.sql

drop table if exists girls;
drop table if exists toys;

create table girls(
    girl_id integer primary key autoincrement,
    girl text,
    toy_id integer
);
insert into girls(
    girl,
    toy_id
) values
('ジェン', 1),
('クレオ', 1),
('サリー', 3),
('マーサ', 3);

create table toys(
    toy_id integer primary key autoincrement,
    toy text
);
insert into toys(
    toy
) values
('水鉄砲'),
('クレイジー・ストロー'),
('スリンキー');

コード

sample2.py

#! /usr/bin/env python3
import sqlite3

con = sqlite3.connect('sample.db')
cur = con.cursor()


def p(cur: sqlite3.Cursor, table: str):
    cur.execute(
        f"""
select * from {table}
"""
    )
    if (d := cur.description) is not None:
        print([t[0] for t in d])
    for row in cur.fetchall():
        print(row)


for i in range(1, 3):
    with open(f'schema2_{i}.sql') as f:
        cur.executescript(f.read())
    con.commit()
    p(cur, 'girls')
    p(cur, 'toys')
    with open(f'schema2_{i}_1.sql') as f:
        cur.execute(f.read())
    if (d := cur.description) is not None:
        print([t[0] for t in d])
    for row in cur.fetchall():
        print(row)

cur.close()
con.close()

入出力結果(Terminal, Zsh)

% ./sample2.py
['girl_id', 'girl', 'toy_id']
(1, 'ジェン', 1)
(2, 'クレオ', 2)
(3, 'マンディ', 3)
['toy_id', 'toy']
(1, '水鉄砲')
(2, 'クレイジー・ストロー')
['girl', 'toy']
('ジェン', '水鉄砲')
('クレオ', 'クレイジー・ストロー')
('マンディ', None)
['girl_id', 'girl', 'toy_id']
(1, 'ジェン', 1)
(2, 'クレオ', 1)
(3, 'サリー', 3)
(4, 'マーサ', 3)
['toy_id', 'toy']
(1, '水鉄砲')
(2, 'クレイジー・ストロー')
(3, 'スリンキー')
['girl', 'toy']
('クレオ', '水鉄砲')
('ジェン', '水鉄砲')
(None, 'クレイジー・ストロー')
('サリー', 'スリンキー')
('マーサ', 'スリンキー')
%