2. SQL問い合わせ（SQLite3, Python を使用）

SQL問い合わせ (SQL Query)

SQL問い合わせは，リレーショナルデータベースに格納された1つまたは複数のテーブルを使用する．次のように書くと，m 個のテーブル T1, T2, ..., Tm の直積集合（各テーブルから1行ずつ取り出したすべての組み合わせ）から条件 <expression> を満足する行を選び，得られたテーブルの列 A1, A2, ..., An を出力するという意味になる．

SELECT A1, A2, ..., An
FROM   T1, T2, ..., Tm
WHERE  <expression>

条件 (Condition)

テーブル名とドット「.」と列名の並びを列の修飾名と呼ぶ．テーブル R の列 A の修飾名は R.A である．文字定数は，'X' のようにシングルクォーテーションマーク「'」で囲む．比較演算子は =, >, <, >=, <=, <> の6種類がある．探索条件は，列の修飾名と比較演算子と値（定数または列の修飾名）の並びである（「R.A > 20」は列と定数の比較，「R.A > S.B」は列同士の比較）．扱うテーブルが1つのときは，列の修飾名の代わりに列名を使用できる（「A > 20」のように）．条件は AND や OR で連結できる．

Iris データセット

本演習では，Iris データセット（150件のアヤメの計測データ）を使用する．次のような形式のCSVファイルである．

sepal_length,sepal_width,petal_length,petal_width,species
5.1,3.5,1.4,0.2,setosa
4.9,3.0,1.4,0.2,setosa
4.7,3.2,1.3,0.2,setosa
4.6,3.1,1.5,0.2,setosa
5.0,3.6,1.4,0.2,setosa

列は，がく片の長さ（sepal_length），がく片の幅（sepal_width），花弁の長さ（petal_length），花弁の幅（petal_width），品種（species）である．本演習で使用するCSVファイルには id 列が含まれていないため，テーブルへ挿入する際に行番号から id を生成する．

演習準備

本演習では Python を使用する．以下のやり方が分からない場合は，先に「Windows環境でのPython：基本とプログラムの作成・実行」を参照すること．

掲載されているPythonコードは，Windows 上の Python および Google Colab のいずれの環境でも動作する．Windows で実行する場合は，下記の「Python 3.12 のインストール（Windows 上）」と「必要なライブラリのインストール」を実施すること．Google Colab で実行する場合は，sqlite3，pandas，urllib.request はあらかじめ利用可能であり，追加インストールは不要である．

Python 3.12 のインストール（Windows 上） [クリックして展開]

以下のいずれかの方法で Python 3.12 をインストールする．Python がインストール済みの場合，この手順は不要である．

方法1：winget によるインストール

管理者権限のコマンドプロンプトで以下を実行する．管理者権限のコマンドプロンプトを起動するには，Windows キーまたはスタートメニューから「cmd」と入力し，表示された「コマンドプロンプト」を右クリックして「管理者として実行」を選択する．

winget install --id Python.Python.3.12 -e --scope machine --silent --accept-source-agreements --accept-package-agreements --override "/quiet InstallAllUsers=1 PrependPath=1 Include_test=0 Include_pip=1 Include_launcher=1 InstallLauncherAllUsers=1 TargetDir=\"C:\Program Files\Python312\""
powershell -Command "$p='C:\Program Files\Python312'; $s=\"$p\Scripts\"; $m=[Environment]::GetEnvironmentVariable('Path','Machine'); if($m -notlike \"*$s*\") { [Environment]::SetEnvironmentVariable('Path', \"$p;$s;$m\", 'Machine') }"

--scope machine を指定することで，システム全体（全ユーザー向け）にインストールされる．このオプションの実行には管理者権限が必要である．インストール完了後，コマンドプロンプトを再起動すると PATH が反映される．

方法2：インストーラーによるインストール

  1. Python 公式サイト（https://www.python.org/downloads/）にアクセスし，「Download Python 3.x.x」ボタンから Windows 用インストーラーをダウンロードする．
  2. ダウンロードしたインストーラーを実行する．
  3. 初期画面の下部に表示される「Add python.exe to PATH」にチェックを入れてから「Customize installation」を選択する．このチェックを入れない場合，コマンドプロンプトから python コマンドを実行できない．
  4. 「Install Python 3.xx for all users」にチェックを入れ，「Install」をクリックする．

インストールの確認

コマンドプロンプトで以下を実行する．

python --version

バージョン番号（例：Python 3.12.x）が表示されればインストール成功である．「'python' は，内部コマンドまたは外部コマンドとして認識されていません．」と表示される場合は，インストールが完了していない．

必要なライブラリのインストール

管理者権限のコマンドプロンプトで以下を実行する．管理者権限のコマンドプロンプトを起動するには，Windows キーまたはスタートメニューから「cmd」と入力し，表示された「コマンドプロンプト」を右クリックして「管理者として実行」を選択する．--no-user オプションは，ユーザーローカル領域ではなくシステム全体（全ユーザー向け）にインストールするための指定である．

pip install -U --no-user pandas

演習の進め方

以降の演習では，テキストエディタでプログラムをファイルに保存し，コマンドプロンプトから実行する．各演習のプログラムは，作業ディレクトリ（例：C:\work）にファイル名（例：ex1.py）で保存し，コマンドプロンプトで以下のように実行する．Google Colab を使用する場合は，各プログラムをセルにコピーして実行する．

cd C:\work
python ex1.py

演習

演習1．作業ディレクトリの確認とCSVファイルのダウンロード

手順

  1. 作業ディレクトリ（プログラムを保存し実行するフォルダ）を作成する．
  2. 以下のプログラムをファイル ex1.py として保存する．
  3. コマンドプロンプトで作業ディレクトリに移動し，python ex1.py で実行する．
  4. 実行後，作業ディレクトリに iris.csv が保存されていることを確認する．
import os
import urllib.request

print(os.getcwd())

url = "https://gist.githubusercontent.com/curran/a08a1080b88344b0c8a7/raw/0e7a9b0a5d22642a06d3d5b9bcbad9890c8ee534/iris.csv"
urllib.request.urlretrieve(url, "iris.csv")

ヒント

考察ポイント

演習2．データベースとテーブルの作成

SQLite3 はファイルベースのデータベースであり，1つのデータベースが1つのファイルとして保存される．以下のプログラムでは，データベースファイル hoge.sqlite を作成し，テーブル iris を定義する．

手順

  1. 以下のプログラムをファイル ex2.py として保存する．
  2. python ex2.py で実行する．
  3. 作業ディレクトリに hoge.sqlite が作成されていることを確認する．
import sqlite3

conn = sqlite3.connect('hoge.sqlite')
sql = """
create table iris (
  id integer     primary key,
  sepal_length   real,
  sepal_width    real,
  petal_length   real,
  petal_width    real,
  species        text );
"""
conn.execute(sql)
conn.commit()
conn.close()

ヒント

考察ポイント

演習3．CSVファイルからテーブルへのデータ挿入

pandas で CSV ファイルを読み込み，各行を iris テーブルに挿入する．

手順

  1. 以下のプログラムをファイル ex3.py として保存する．
  2. python ex3.py で実行する．
  3. 表示された行数が 150 であることを確認する．
import sqlite3
import pandas as pd

conn = sqlite3.connect('hoge.sqlite')
x = pd.read_csv('iris.csv', header=0)

for index, r in x.iterrows():
    sql = "insert into iris values (?, ?, ?, ?, ?, ?)"
    conn.execute(sql, (index + 1, r['sepal_length'], r['sepal_width'],
                       r['petal_length'], r['petal_width'], r['species']))

conn.commit()

result = conn.execute("select count(*) from iris").fetchall()
print(result)

conn.close()

ヒント

考察ポイント

演習4．テーブルの全データの取得

select * from iris は，iris テーブルの全行・全列を取得する SQL 問い合わせである．

手順

  1. 以下のプログラムをファイル ex4.py として保存する．
  2. python ex4.py で実行する．
import sqlite3

conn = sqlite3.connect('hoge.sqlite')

result = conn.execute("select * from iris").fetchall()
print(result)

conn.close()

ヒント

考察ポイント

演習5．条件を指定した行の取得

where 句に条件を指定して，条件に合致する行のみを取得する．

手順

  1. 以下のプログラムをファイル ex5.py として保存する．
  2. python ex5.py で実行する．
import sqlite3

conn = sqlite3.connect('hoge.sqlite')

result = conn.execute("select * from iris where id = 2").fetchall()
print(result)

result = conn.execute("select * from iris where sepal_length > 7").fetchall()
print(result)

conn.close()

ヒント

考察ポイント

演習6．列の選択と複数条件の組み合わせ

取得する列の指定，AND および OR による条件の連結を行う．

手順

  1. 以下のプログラムをファイル ex6.py として保存する．
  2. python ex6.py で実行する．
import sqlite3

conn = sqlite3.connect('hoge.sqlite')

result = conn.execute("select species, sepal_length, sepal_width from iris where id < 5").fetchall()
print("特定の列を選択")
print(result)

result = conn.execute("select * from iris where sepal_length > 5.0 and sepal_width > 3.5").fetchall()
print("AND を使用した複数条件の組み合わせ")
print(result)

result = conn.execute("select id, species from iris where sepal_length < 4.5 or sepal_width > 4.0").fetchall()
print("OR を使用した複数条件の組み合わせ")
print(result)

conn.close()

ヒント

考察ポイント