SQL - Python - SELECT文 - 天賦のデータ検索 - where句, ワイルドカード, LIKE, アンダースコア(_)

Head First SQL ―頭とからだで覚えるSQLの基本
 Amazon
原著

開発環境

Head First SQL ―頭とからだで覚えるSQLの基本、 Lynn Beighley(著)、佐藤直生(監訳)、松永多苗子(翻訳)、オライリージャパンの 2章(SELECT文 - 天賦のデータ検索)、p.103(マグネット合わせ)の解答を求めてみる。

schema10.sql

drop table if exists temp;
create table temp(
    state text,
    cow_name text,
    title text,
    rhyme_word text,
    first_name text
);
insert into temp
(
    state,
    cow_name,
    title,
    rhyme_word,
    first_name
) values
(
    'New Jersey',
    'Elsie',
    'Head First SQL',
    'Blender',
    'John'
),
(
    'Michigan',
    null,
    null,
    'Pineapple',
    'Joshua'
),
(
    'Alabama',
    null,
    null,
    'splendid',
    null
),
(
    'Montana',
    null,
    null,
    'splendor',
    null
),
(
    'Maine',
    null,
    null,
    'Liver',
    null
),
(
    'New York',
    null,
    null,
    null,
    null
);

schema10_0.sql

select state from temp
where state like 'New %';

schema10_1.sql

select cow_name from temp
where cow_name like '__sie';

schema10_2.sql

select title from temp
where title like 'HEad First%';

schema10_3.sql

select rhyme_word from temp
where rhyme_word like '%ender';

schema10_4.sql

select first_name from temp
where first_name like 'Jo%';

schema10_5.sql

select rhyme_word from temp
where rhyme_word like 'splen%';

schema10_6.sql

select state from temp
where state like 'A%' 
or state like 'M%';

schema10_7.sql

select rhyme_word from temp
where rhyme_word like '_i%';

コード

sample10.py

#! /usr/bin/env python3
import sqlite3

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

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

for i in range(8):
    with open(f'schema10_{i}.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)

% ./sample10.py
['state']
('New Jersey',)
('New York',)
['cow_name']
('Elsie',)
['title']
('Head First SQL',)
['rhyme_word']
('Blender',)
['first_name']
('John',)
('Joshua',)
['rhyme_word']
('splendid',)
('splendor',)
['state']
('Michigan',)
('Alabama',)
('Montana',)
('Maine',)
['rhyme_word']
('Pineapple',)
('Liver',)
%