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

SQL問い合わせ (SQL Query)

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

SELECT A₁, A₂, ..., A_n
FROM   T₁, T₂, ..., T_m
WHERE  <expression>

条件 (Condition)

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

Iris データセット

Iris データセットのファイルは次のようなCSVファイルである．
Kaggle のデータセット（https://www.kaggle.com/datasets/uciml/iris）から入手できる．

Id,SepalLengthCm,SepalWidthCm,PetalLengthCm,PetalWidthCm,Species
1,5.1,3.5,1.4,0.2,Iris-setosa
2,4.9,3.0,1.4,0.2,Iris-setosa
3,4.7,3.2,1.3,0.2,Iris-setosa
4,4.6,3.1,1.5,0.2,Iris-setosa
5,5.0,3.6,1.4,0.2,Iris-setosa
6,5.4,3.9,1.7,0.4,Iris-setosa
7,4.6,3.4,1.4,0.3,Iris-setosa
8,5.0,3.4,1.5,0.2,Iris-setosa

なお，このCSVファイルの列名（Id, SepalLengthCm, SepalWidthCm, PetalLengthCm, PetalWidthCm, Species）と，後述のテーブル定義で使用する列名（id, sepal_length, sepal_width, petal_length, petal_width, species）は異なる．CSVファイルの読み込み時には列名ではなく列の順序（位置）でデータを挿入するため，列名の違いは問題にならない．

演習準備

この演習では Python を使用する。Python がインストールされていない場合は，下記の「Python 3.12 のインストール（Windows 上）」を展開し，手順に従いインストールすること。下記の「必要なライブラリのインストール」を実施すること。

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

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

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

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

winget install -e --id Python.Python.3.12 --scope machine --silent --accept-source-agreements --accept-package-agreements --override "/quiet InstallAllUsers=1 PrependPath=1 AssociateFiles=1 InstallLauncherAllUsers=1"

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

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

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

インストールの確認

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

python --version

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

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

pip install -U pandas

演習

① Pythonを起動する

python

② カレントディレクトリの確認

os モジュールを使用してカレントディレクトリ（現在の作業ディレクトリ）を取得する．このパスは，データベースファイルやCSVファイルの保存場所となる．

import os
os.getcwd()

③ iris.csv をダウンロードし保存

Python の標準ライブラリである urllib.request を使用して，GitHub 上で公開されている Iris データセットの CSV ファイルをダウンロードする．urllib.request.urlretrieve は，第1引数に指定した URL からファイルを取得し，第2引数に指定したファイル名でカレントディレクトリに保存する関数である．この方法により，外部サイトへのアカウント登録なしにデータを取得できる．

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

実行後，カレントディレクトリに iris.csv が保存される．このファイルには 150 件の Iris（アヤメ）のデータが含まれており，ヘッダー行の列名は sepal_length,sepal_width,petal_length,petal_width,species である．

④ データベースのテーブル定義を行う

以下のプログラムでは，sqlite3.connect でSQLite3データベースに接続し，接続オブジェクト c を取得する．SQLite3はファイルベースのデータベースであるため，接続時にはデータベースファイルのパス（ここでは 'hoge.sqlite'）を指定する．接続オブジェクトは，SQL文の実行やトランザクション（一連のデータベース操作をまとめて確定または取り消す仕組み）の管理に用いる．create table 文でテーブル iris を定義し，各列にデータ型を指定する．id 列には主キー制約（primary key）を設定する．トリプルクォート（""" … """）を用いることで，複数行のSQL文を見やすく記述している．なお，ここでは接続オブジェクト c の execute メソッドでSQL文を直接実行している．（これは sqlite3 モジュールが提供するショートカットであり，内部的にはカーソルが自動生成される．）

import sqlite3
c = 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 );
"""
c.execute(sql)

⑤ テーブル生成（空のテーブルにレコードを挿入する）

以下のプログラムでは，pandasの pd.read_csv でCSVファイルを読み込み，SQLプレースホルダー（?）と iterrows() を組み合わせて，各行をSQLiteテーブルに挿入する．

(1) オブジェクト x にCSVファイルを読み込む

読み込むCSVファイルにヘッダー行がない場合は header=None を指定する．今回使用するCSVファイルには sepal_length,sepal_width,petal_length,petal_width,species というヘッダー行があるため，header=0（先頭行をヘッダーとして扱う）を指定している．なお，このCSVファイルには id 列が含まれていないため，挿入時には DataFrame のインデックス（0始まり）に1を加えた値を id として使用する．

import pandas as pd
x = pd.read_csv('iris.csv', header=0)

(2) オブジェクト x に格納されたデータを iris テーブルに挿入する

for index, r in x.iterrows(): は，DataFrameオブジェクト x の各行について繰り返す構文である．insert into iris values は，テーブルに1行挿入するSQL文である．「?」はSQLプレースホルダーである．CSVファイルに id 列は含まれていないため，index + 1（DataFrame のインデックスに1を加えた値）を id として使用している．r['sepal_length'] 等は CSV ファイルの各列の値に対応する．最後に c.commit() を実行し，挿入したデータをデータベースに確定（コミット）させる．c.commit() を実行しない場合，挿入したデータは保存されない．

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

c.commit()

⑥ テーブルの全データを読み出す

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

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

⑦ 条件を指定してレコードを取得する

テーブルから，条件に合致するレコードを取得する．

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

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

⑧ より進んだSQL問い合わせの演習

特定の列の選択や，複数条件の組み合わせなど，SQLの基本機能を使用した問い合わせを実行する．

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

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

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

⑨ 接続を閉じる

データベース操作の終了後は c.close() で接続を閉じる．

c.close()

⑩ Pythonの終了

exit() でPythonを終了する．

exit()