1. テーブル定義，テーブル生成，問い合わせ（SQLite3, Python を使用）

【概要】

Python言語からリレーショナルデータベースを操作する方法を説明する．リレーショナルデータベースは，複数のテーブルから構成される．テーブルは，数値，文字列，日時などの情報を行と列の形式で格納するデータ構造である．リレーショナルデータベースを操作する言語の世界標準がSQL（Structured Query Language）である．本教材では，PythonとSQLを組み合わせてデータベースを操作する．データベースへ接続するときは，データベース名（SQLite3ではデータベースファイルのパス）を指定する．

【目次】

• テーブル
• リレーションスキーマ
• テーブル定義
• SQLのデータ型
• 主キー
• SQLの集約関数
• CSVファイル（CSV File）
• Pythonの繰り返し処理
• PythonのSQLプレースホルダー
• 演習準備
• 演習1．SQLite3によるテーブル定義，データ挿入，問い合わせ

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

• データベース処理とデータ分析：SQL・Python・機械学習による演習のページ
• Windows環境でのPython：基本とプログラムの作成・実行
• データベース用語集のページ

テーブル

リレーショナルデータベースでは，データベースをテーブルの集まり（collection of tables）として表現する．各テーブルにはテーブル名があり，テーブル内の各列には列名がある．テーブル名と列名は，テーブルや列を識別するために用いる．

テーブルの例

リレーションスキーマ

リレーションスキーマ（スキーマともいう，relation schema）とは，テーブルの構造を形式的に表現したものである．Rをテーブル名，A1, A2, …, Anを属性名（列名）とするとき，リレーションスキーマを「R(A1, A2, …, An)」と記述する．本教材で使用するirisテーブルのリレーションスキーマは「iris(id, sepal_length, sepal_width, petal_length, petal_width, species)」である．

テーブル定義

テーブル定義とは，テーブル名，属性名の並び，各属性のデータ型，各属性の制約（例：主キー）を記述することである．

SQLのデータ型

SQLiteの代表的なデータ型を以下に示す．

主キー

テーブルの行を一意に識別できる属性または属性の組のうち，極小なもの（不要な属性を含まないもの）を候補キー（candidate key）という．候補キーの中から，リレーションスキーマの設計時に，データベース管理者が管理上適切と判断し，かつ空値（NULL）をとる可能性がないものとして選定したものが主キー（primary key）である．

SQLの集約関数

SQLiteの代表的な集約関数を以下に示す．集約関数は，複数行のデータをまとめて1つの値を返す関数である．

CSVファイル（CSV File）

CSV（Comma-Separated Values）ファイルとは，値をカンマで区切って並べたテキスト形式のデータファイルである．CSVファイルの列名は，データベースのテーブル定義における列名と異なる場合がある．

本教材では，Irisデータセット（150件のアヤメの計測データ）を使用する．本演習で取得するCSVファイルのヘッダー行の列名は sepal_length,sepal_width,petal_length,petal_width,species であり，id 列は含まれない．ファイルの先頭部分は以下のとおりである．

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
5.4,3.9,1.7,0.4,setosa
4.6,3.4,1.4,0.3,setosa
5.0,3.4,1.5,0.2,setosa

Pythonの繰り返し処理

Pythonでは，データの集まりに対して for 文で各要素を順に処理できる．以下の例では，変数 row が t の各要素を順に参照する．

for row in t:
    print(row)

PythonのSQLプレースホルダー

SQL文中にプレースホルダーを示す記号「?」を記述し，実行時に指定した値で置き換えることができる．これはPythonの sqlite3 モジュールが提供する機能である．以下の例では，「?」の部分が値 4 で置き換えられ，"select * from iris where id = 4" に相当するSQL文が実行される．プレースホルダーを使用すると，SQLインジェクション（外部からの入力を利用してSQL文を改ざんする攻撃）を防止できる．

sql = "select * from iris where id = ?"
conn.execute(sql, (4,))

演習準備

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

• テキストエディタで Python スクリプトを作成し，コマンドプロンプトから python ファイル名.py で実行する．本演習では，エディタでコードを記述し，まとまった単位で実行して結果を確認する方式を用いる．
• ライブラリのインストールには pip を使用する．具体的な手順は，下記の「必要なライブラリのインストール」に示す．

掲載している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を推奨する。

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')}"

python --version

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

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

pip install -U --no-user pandas

演習1．SQLite3によるテーブル定義，データ挿入，問い合わせ

本演習では，テキストエディタで Python スクリプトを作成し，コマンドプロンプトから実行して結果を確認する．スクリプトは段階的に作成し，各段階の追記後に実行して結果を確認する．作業ディレクトリ（プログラムを保存し実行するフォルダ）は任意の場所（例：C:\work）とする．Google Colab を使用する場合は，各段階のコードをセルに追記して実行する．

手順

①〜⑧の各手順では，次のコードを順に追記し，コマンドプロンプトで実行する（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）。①の段階で最初に実行する．Google Colab を使用する場合は，各段階のコードをセルに追記して実行する．

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

カレントディレクトリ（プログラムを実行した時点の作業ディレクトリ）を取得して表示する．このディレクトリが，これ以降に作成するデータベースファイルやCSVファイルの保存場所となる．

import os
print(os.getcwd())

② iris.csv のダウンロード

Python の標準ライブラリ urllib.request を使用して，GitHub 上で公開されている Iris データセットの CSV ファイルをダウンロードする．urllib.request.urlretrieve(URL, ファイル名) は，URL から取得した内容を，指定したファイル名でカレントディレクトリに保存する．

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

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

③ データベースのテーブル定義

sqlite3.connect(ファイル名) でSQLite3データベースに接続し，接続オブジェクト conn を取得する．SQLite3はファイルベースのデータベースであるため，接続時にデータベースファイルのパス（ここでは 'hoge.sqlite'）を指定する．ファイルが存在しない場合は新規作成される．接続オブジェクトはSQL文の実行やトランザクション（一連のデータベース操作をまとめて確定または取り消す仕組み）の管理に用いる．create table 文でテーブル iris を定義し，各列にデータ型を指定する．id 列には主キー制約（primary key）を設定する．トリプルクォート（""" … """）は複数行の文字列を表す．接続オブジェクトの execute メソッドでSQL文を実行する．

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)

④ CSVファイルの読み込みとテーブルへのデータ挿入

pandasの pd.read_csv(ファイル名, header=0) でCSVファイルを読み込む（header=0 は先頭行を列名として扱う指定）．本演習で使用するCSVファイルにはヘッダー行があるため header=0 を指定する．このCSVファイルには id 列が含まれていないため，挿入時にはDataFrameのインデックス（0始まりの行番号）に1を加えた値を id として使用する．

x.iterrows() は，DataFrame x の各行を（行番号, 行データ）の組として返す．SQL文中の「?」はプレースホルダーであり，execute の第2引数のタプルの値で順に置き換えられる．insert into iris values (?, ?, ?, ?, ?, ?) は，テーブルに1行挿入するSQL文である．プレースホルダー ? の数（6個）と，テーブルの列数（6列）および execute の第2引数のタプルの要素数（6個）は一致させる．最後に conn.commit() を実行し，挿入したデータをデータベースに確定（コミット）する．conn.commit() を実行しない場合，挿入したデータは保存されない．

import pandas as pd
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()

⑤ テーブルの全データの読み出し

select * from iris は，iris テーブルの全行・全列を取得するSQL問い合わせである．fetchall() は問い合わせ結果の全行をリストで返し，各行はタプルとして表現される．

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

⑥ 集約関数による合計・平均の取得

SUM()（合計値）と AVG()（平均値）はSQLの集約関数である．以下の例では sepal_length（がく片の長さ）を対象としている．sepal_width や petal_length など他の数値属性に置き換えることもできる．集約関数の対象は数値型の属性に限定する（species のような文字列型の属性に対して SUM() や AVG() を適用しても意味のある結果は得られない）．

result = conn.execute("select sum(sepal_length), avg(sepal_length) from iris").fetchall()
print(result)

⑦ 接続を閉じる

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

conn.close()

⑧ データベースファイルの確認

スクリプトの実行後，手順①で確認したカレントディレクトリに，データベースファイル hoge.sqlite が生成されていることを確認する．以下のPythonコードはWindows および Google Colab のいずれでも動作する．

import os
print(os.path.exists('hoge.sqlite'), os.path.getsize('hoge.sqlite'))

ヒント

• スクリプトを再実行する場合，create table 文は同名テーブルが存在すると失敗する．再実行する際は hoge.sqlite ファイルを削除してから実行する．
• CSVファイルの列名（sepal_length など）は，r['列名'] の形式で参照する．列名のスペル誤りに注意する．
• プレースホルダー ? の数と execute の第2引数のタプルの要素数を一致させる．iris テーブルは6列のため，プレースホルダーも6個指定する．

考察ポイント

• 手順⑤の select * の実行結果から，挿入された行数（150件）と各行のタプル構造を確認する．id 列の値が1から150まで連番で付与されていることを確認する．
• 手順⑥の集約関数の結果から，sum(sepal_length) を行数（COUNT() で取得可能）で割ると avg(sepal_length) と一致することを確認する．
• 手順⑧で生成された hoge.sqlite ファイルのサイズを確認し，テーブルにデータが格納されていることを確認する．スクリプトを再度実行すると，テーブルが既に存在するためエラーとなることを確認する．これはデータがファイルとして永続化されていることの確認になる．