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

【概要】

本資料では，リレーショナルデータベースに対する SQL 問い合わせを，Python の sqlite3 モジュール（標準ライブラリ）と pandas を使って学ぶ．SQL 問い合わせの基本構文と条件指定を説明したうえで，Iris データセットを題材に，データベースの作成，CSV からのデータ挿入，select 文による検索（全件取得，条件指定，列の選択，複数条件の連結）を演習で確認する．

【目次】

【関連する外部ページ】

【サイト内の関連ページ】

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 のインストール」と「必要なライブラリのインストール」を実施すること．Google Colab で実行する場合は，sqlite3，pandas，urllib.request はあらかじめ利用可能であり，追加インストールは不要である．

Python 3.12 のインストール

Pythonのインストールを行い、Pythonのプログラムを実行する環境を整える。扱う環境は、Windows搭載パソコンである。金子研究室では、Python 3.12.10を推奨する。

[Windows での Python 3.12 のインストール手順を見るには、ここをクリック]

Windows での Python 3.12 のインストール

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

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

【インストールコマンドの実行方法】

管理者権限でコマンドプロンプトを起動する（手順：Windowsキーまたはスタートメニュー → cmd と入力 → 右クリック → 「管理者として実行」）。そして、コマンド全体をコマンドプロンプトにコピー＆ペーストする。

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

REM Python 3.12 をシステム領域にインストール
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\""

REM Python と Scripts を PATH 先頭に追加
powershell -NoProfile -Command "$p='C:\Program Files\Python312'; $s=\"$p\Scripts\"; $c=[Environment]::GetEnvironmentVariable('Path','Machine'); if((Test-Path $p) -and (';'+$c+';' -notlike \"*;$p;*\") -and (';'+$c+';' -notlike \"*;$s;*\")){[Environment]::SetEnvironmentVariable('Path',\"$p;$s;$c\",'Machine')}"

方法 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）に移動し，以下のように実行する（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）．Google Colab を使用する場合は，各プログラムをセルにコピーして実行する．

cd C:\work
python a.py

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

テーマ名：作業ディレクトリの確認と，Iris データセットのCSVファイルのダウンロード．

手順

  1. 作業ディレクトリ（プログラムを保存し実行するフォルダ）を作成する．
  2. 以下のプログラムを実行する（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）．
  3. 実行後，作業ディレクトリに iris.csv が保存されていることを確認する．
import os
import urllib.request

print(os.getcwd())

url = "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/iris.csv"
urllib.request.urlretrieve(url, "iris.csv")

ヒント

考察ポイント

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

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

テーマ名：データベースファイルの作成と，create table によるテーブル定義．

手順

  1. 以下のプログラムを実行する（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）．
  2. 作業ディレクトリに 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 テーブルに挿入する．

テーマ名：CSV ファイルの読み込みと，insert 文によるテーブルへのデータ挿入．

手順

  1. 以下のプログラムを実行する（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）．
  2. 表示された行数が 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 問い合わせである．

テーマ名：select * によるテーブルの全データの取得．

手順

  1. 以下のプログラムを実行する（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）．
import sqlite3

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

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

conn.close()

ヒント

考察ポイント

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

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

テーマ名：where 句による条件指定での行の取得．

手順

  1. 以下のプログラムを実行する（メモ帳を用いる場合は a.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 による条件の連結を行う．

テーマ名：取得する列の指定と，AND・OR による複数条件の連結．

手順

  1. 以下のプログラムを実行する（メモ帳を用いる場合は a.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()

ヒント

考察ポイント