計算機科学のブログ

ほしい物リスト

SQL - SQLite - Python - 結合と複数テーブル操作 - みんなでうまくやれないの？ - INNER JOIN, AS, 別名

Head First SQL ―頭とからだで覚えるSQLの基本、 Lynn Beighley(著)、 佐藤 直生(監訳)、 松永 多苗子(翻訳)、 オライリージャパンの 8章(結合と複数テーブル操作 - みんなでうまくやれないの？)、p.360(自分で考えてみよう)の解答を求めてみる。

schema5.sql

drop table if exists profession;
create table profession(
    prof_id integer primary key autoincrement,
    profession text
);
insert into profession (profession) values
('アーティスト'),
('シェフ'),
('教授');

drop table if exists my_contacts;
create table my_contacts(
    concact_id integer primary key autoincrement,
    last_name text,
    first_name text,
    phone text,
    gender text,
    birthday text,
    prof_id integer,
    zip_code integer,
    status_id integer,
    foreign key (prof_id) references profession(prof_id),
    foreign key (zip_code) references zip_code(zip_code),
    foreign key (status_id) references status(status_id)
);
insert into my_contacts(
    last_name,
    first_name,
    prof_id
) values
('エヴェレット', 'ジョーン', 1),
('ボールドウィン', 'タラ', 2),
('シン', 'ポール', 3);

schema5_1.sql

select mc.last_name, mc.first_name, p.profession
from my_contacts as mc
inner join profession as p
on mc.prof_id = p.prof_id;

コード

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


with open('schema5.sql') as f:
    cur.executescript(f.read())

con.commit()
p(cur, 'profession')
p(cur, 'my_contacts')

with open('schema5_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)

% ./sample5.py
['prof_id', 'profession']
(1, 'アーティスト')
(2, 'シェフ')
(3, '教授')
['concact_id', 'last_name', 'first_name', 'phone', 'gender', 'birthday', 'prof_id', 'zip_code', 'status_id']
(1, 'エヴェレット', 'ジョーン', None, None, None, 1, None, None)
(2, 'ボールドウィン', 'タラ', None, None, None, 2, None, None)
(3, 'シン', 'ポール', None, None, None, 3, None, None)
['last_name', 'first_name', 'profession']
('エヴェレット', 'ジョーン', 'アーティスト')
('ボールドウィン', 'タラ', 'シェフ')
('シン', 'ポール', '教授')
%