埋め込み SQL

【概要】

埋め込みSQLは他のプログラム言語内にSQLコードを埋め込むことである。SQLite 3は軽量でサーバレスなデータベース管理システムで、単一ファイルに全データを格納する。トランザクションはデータベース操作を一つの単位として扱う機能で、全操作が成功すればコミット、一つでも失敗するとロールバックされ、データの整合性を保つ。

【目次】

前準備（Windows 上）
Python の SQLite 3 モジュール
Python を使用してみる
Python の中に埋め込まれた SQL による問い合わせ
Python プログラムの中に埋め込まれた SQL を用いたデータベース更新
Python プログラムの中に埋め込まれた SQL を用いたテーブル一覧表示

【関連する外部ページ】

Python の sqlite3 モジュールの説明は https://docs.python.org/3/library/sqlite3.html にある。 SQLite 3 の SQL の説明は https://www.sqlite.org/lang.html にある。 SQLite 3 のデータ型の説明は https://www.sqlite.org/datatype3.html にある。

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

リレーショナルデータベースの種々の概念と実装技術の探究のページ

公開資料はクリエイティブコモンズ表示-非営利-継承 4.0 国際ライセンス（CC BY-NC-SA 4.0）で提供しており，事前の許可なく自由に利用できます．条件は著作者表示（BY），非営利目的のみ（NC），同一ライセンスでの再配布（SA）です．

前準備（Windows 上）

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：インストーラーによるインストール

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' は、内部コマンドまたは外部コマンドとして認識されていません。」と表示される場合は、インストールが正常に完了していない。

Python の開発環境 Visual Studio Code のインストールと Python 用の設定

Python の開発環境Visual Studio Code（プログラムを編集するソフトウェア。以下、VS Code）を整える。

[Windows での Visual Studio Code のインストールと Python 用の設定手順を見るには、ここをクリック]

Windows での Visual Studio Code のインストールと Python 用の設定手順

1. VS Code と拡張機能のインストール

以下のコマンドにより，既存の VS Code を削除し，全ユーザー共有の設定で再インストールしたうえで，拡張機能（VS Code に機能を追加するソフトウェア）をまとめて導入する．

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

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

インストールコマンド


REM ============================================================
REM Microsoft Visual Studio Code
REM ============================================================
winget uninstall -e --id Microsoft.VisualStudioCode --silent --disable-interactivity --accept-source-agreements
rmdir /s /q C:\ProgramData\vscode-extensions 2>nul
rmdir /s /q "%APPDATA%\Code" 2>nul
rmdir /s /q "%USERPROFILE%\.vscode" 2>nul
rmdir /s /q "%LOCALAPPDATA%\Microsoft\vscode-update" 2>nul

REM VS Code をシステム領域に新規インストール
winget install --scope machine --id Microsoft.VisualStudioCode -e --silent --accept-source-agreements --accept-package-agreements

REM 全ユーザー共有の拡張機能フォルダ
mkdir C:\ProgramData\vscode-extensions 2>nul
icacls "C:\ProgramData\vscode-extensions" /grant "Everyone:(OI)(CI)M" /T

REM スタートメニューのショートカットを --extensions-dir 付きで再作成
rmdir /s /q "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Visual Studio Code" 2>nul
del "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Visual Studio Code.lnk" 2>nul
powershell -NoProfile -Command "$s=New-Object -ComObject WScript.Shell; $lnk=$s.CreateShortcut('C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Visual Studio Code.lnk'); $lnk.TargetPath='C:\Program Files\Microsoft VS Code\Code.exe'; $lnk.Arguments='--extensions-dir \"C:\ProgramData\vscode-extensions\"'; $lnk.Save()"
REM ショートカットの検証
powershell -NoProfile -Command "$s=New-Object -ComObject WScript.Shell; $lnk=$s.CreateShortcut('C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Visual Studio Code.lnk'); Write-Host 'TargetPath:' $lnk.TargetPath; Write-Host 'Arguments:' $lnk.Arguments"

REM ファイル / フォルダ右クリックの「Code で開く」を登録
reg add "HKLM\SOFTWARE\Classes\*\shell\VSCode\command" /ve /d "\"C:\Program Files\Microsoft VS Code\Code.exe\" --extensions-dir \"C:\ProgramData\vscode-extensions\" \"%1\"" /f
reg add "HKLM\SOFTWARE\Classes\Directory\shell\VSCode\command" /ve /d "\"C:\Program Files\Microsoft VS Code\Code.exe\" --extensions-dir \"C:\ProgramData\vscode-extensions\" \"%1\"" /f
reg add "HKLM\SOFTWARE\Classes\Directory\Background\shell\VSCode\command" /ve /d "\"C:\Program Files\Microsoft VS Code\Code.exe\" --extensions-dir \"C:\ProgramData\vscode-extensions\" \"%V\"" /f

REM --extensions-dir 付きで起動する code.cmd ラッパを作成
REM （%* を echo で書くと対話的 cmd で失われるため、PowerShell で [char]37+'*' を書き出す）
powershell -NoProfile -Command "$pct=[char]37; $q=[char]34; $c='@echo off'+[char]13+[char]10+$q+'C:\Program Files\Microsoft VS Code\bin\code.cmd'+$q+' --extensions-dir '+$q+'C:\ProgramData\vscode-extensions'+$q+' '+$pct+'*'+[char]13+[char]10; [IO.File]::WriteAllText('C:\ProgramData\vscode-extensions\vscode.cmd',$c,[Text.Encoding]::ASCII)"

REM 拡張機能のインストール
set "CODE=C:\Program Files\Microsoft VS Code\bin\code.cmd"
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --uninstall-extension GitHub.copilot
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --uninstall-extension GitHub.copilot-chat
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --install-extension ms-python.python
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --install-extension ms-python.vscode-pylance
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --install-extension ms-python.debugpy
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --install-extension MS-CEINTL.vscode-language-pack-ja
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --install-extension saoudrizwan.claude-dev
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --install-extension rust-lang.rust-analyzer
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --install-extension tamasfe.even-better-toml
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --install-extension anthropic.claude-code
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --install-extension almenon.arepl
"%CODE%" --extensions-dir "C:\ProgramData\vscode-extensions" --list-extensions --show-versions
echo === セットアップ完了 ===

2. Python インタプリタの選択

同一マシンに複数の Python がインストールされている場合，VS Code で使用する Python 本体（インタプリタ：Python プログラムを解釈・実行するソフトウェア）を選択する必要がある．

コマンドパレット（コマンド名で機能を呼び出す VS Code の入力欄）を開く（Ctrl+Shift+P）
Python: Select Interpreter と入力する
表示される一覧から，使用する Python（例：C:\Program Files\Python312\python.exe）を選択する．

Python プログラム実行手順

[Windows での Python プログラム実行手順を見るには、ここをクリック]

Windows での Python 実行手順（Visual Studio Codeを使用）

プログラムファイルの作成と保存

左サイドバーの「エクスプローラー」アイコン（Ctrl+Shift+E）をクリックする
「NO FOLDER OPENED」（作業対象フォルダが未選択の状態）と表示される場合は，「Open Folder」をクリックし，プログラムを保存するフォルダを選択する

続いて「フォルダを信用するか」を確認する画面（フォルダ内のコードを実行してよいか確認する VS Code の仕組み）が表示されるので，チェックして Yes を選択する
フォルダ名の右側に表示される「新しいファイル」アイコンをクリックする
ファイル名（例：aitask.py．ファイル名は何でも良い）を入力し Enter を押す．拡張子は .py（Python ファイルを示す拡張子）とする
実行したいコードを選択し，Ctrl+C でコピーする．VS Code のエディタ領域に Ctrl+V で貼り付ける
Ctrl+S で保存する

プログラムの実行

エディタ右上の三角形「▷」アイコン（Run Python File：現在開いている Python ファイルを実行するボタン）をクリックする．または，エディタ上で右クリックし「ターミナルで Python ファイルを実行」を選択する
VS Code 下部のターミナル（コマンドの入出力を表示する画面）に，実行結果（print 関数の出力等）が表示される
tkinter（Python 標準の GUI ライブラリ）のファイル選択ダイアログを使うプログラムを実行した場合は，ダイアログが開くので対象画像を選択する
VS Code 下部のターミナルで実行結果を確認する．OpenCV ウィンドウ（OpenCV が画像を表示するために開く専用ウィンドウ）が開いた場合はそちらも確認する．OpenCV ウィンドウは，マウスクリックでウィンドウをアクティブ（操作対象の状態）にしてからキーを押すと終了する

Python の SQLite 3 モジュール

Python の標準ライブラリには sqlite3 モジュールが含まれているため、通常は追加のインストールは不要である。

Python を使用してみる

対話型の処理系は、コンソールでコマンドを入力するたびに Python プログラムの評価結果が表示されるものである。

Ubuntu の場合の手順例

Ubuntu で端末を起動する。
端末の中で「python」コマンドを実行する。

足し算

1 + 2 + 3
1234567890123456789 + 1234567890123456789

sin, sqrt, log
「import math」は、sin などの数学関数の機能を取り込むための操作である。
```
import math
math.sin(1.57)
math.sqrt(2)
math.log(10)
```
変数
```
x = 100
y = 200
x + y
```
Python インタープリタを終了したいときは exit() で終了する。
```
exit()
```

Windows の場合の手順例

Windows の「スタート」から「Python 3.x」を選び、「Python 3.x (64-bit)」を選択する。または、コマンドプロンプトで「python」と入力する。

足し算

1 + 2 + 3
1234567890123456789 + 1234567890123456789

sin, sqrt, log

import math
math.sin(1.57)
math.sqrt(2)
math.log(10)

変数
```
x = 100
y = 200
x + y
```
Python インタープリタを終了したいときは exit() で終了する。
```
exit()
```

Python の中に埋め込まれた SQL による問い合わせ

この節と以降の節では、次のことを行う。

Python の中に埋め込まれた SQL を用いた問い合わせ
Python の中に埋め込まれた SQL を用いたテーブルへの行の挿入
テーブルの一覧表示

SQLite 3 のデータ型 (data types)

データ型の種類はデータベース管理システムごとに異なる。SQLite では、integer, real, text, BLOB などのデータ型を扱える。要点は次のとおりである。

NULL: 空値 (a NULL value)
integer: 符号付きの整数 (signed integer)。SQLite では BIGINT と書いても integer と書いても同じ「8バイトの整数」の意味である。
real: 浮動小数点値 (floating point value)
text: 文字列 (text string)。日時を示す datetime については、SQLite では datetime と書いても text と書いても同じ「可変長文字列」の意味である。ただし使い分けるほうが分かりやすい。
BLOB: バイナリ・ラージ・オブジェクト (Binary Large Object)。入力がそのままの形で格納される。

SQLite 3 のデータ型と SQL の標準が定めるデータ型の定義は異なる。おおまかにまとめると、SQLite 3 のデータ型のほうがより大きな範囲のデータを扱える。

前準備

演習では E テーブルを用いる。「テーブルの更新とトランザクション」の回の授業で作成した E テーブルが残っている場合には、次の SQL を実行する必要はない（実行すると、同じ名前のテーブルを二重に定義できないのでエラーになる）。残っていない場合には、SQLite Manager Tool などで次の SQL を実行する。

create table E (
    name          text     not null,
    score         integer  not null check ( score >= 0 AND score <= 100 ),
    student_name  text     not null,
    created_at    datetime not null,
    updated_at    datetime,
    unique (name, student_name) );

begin transaction;
insert into E values( 'Database',    80, 'KK', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    95, 'AA', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    80, 'LL', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 85, 'KK', datetime('now', 'localtime'), NULL );
insert into E values( 'Programming', 75, 'LL', datetime('now', 'localtime'), NULL );
commit;
SELECT * FROM E;

Python の中に埋め込まれた SQL 問い合わせプログラムの例

SQL を用いてテーブルのすべての行を表示する

SQLプログラムの例

SELECT * FROM E;

ここでは、SQLite Manager Tool を使うのではなく、Python のプログラムを作成する。次の Python のプログラムをエディタで入力し、名前を付けて保存する。

import sqlite3

DBNAME = "/home/ubuntuuser/mydb"

conn = sqlite3.connect(DBNAME)
cursor = conn.cursor()

sql = """
SELECT * FROM E;
"""
cursor.execute(sql)
for row in cursor:
    print(row)

conn.close()

DBNAME にはデータベースファイルのパスを設定する。Windows の場合は「C:/SQLite/mydb」のように書き換える。

Ubuntu の場合の手順の例（Leafpad エディタを使用）

「アクセサリ」→「Leafpad」
編集する。
編集が終わったらファイルを保存する。
ファイル名は英語が好ましい。ファイルを保存したディレクトリとファイル名を記憶しておくこと。下の実行例では、ディレクトリ名は「/home/ubuntuuser」、ファイル名は「hoge.py」である。
実行してみる
端末で次のように実行し、実行結果を確認する。「python」は Python 言語処理系を呼び出すためのコマンドである。
```
cd ＜保存した Python プログラムのディレクトリ名＞
python ＜保存した Python プログラムのファイル名＞
```
プログラムの要点を確認しておく
- SQLプログラム「SELECT * FROM E;」を、文字列のデータとして変数 sql に格納している部分
```
sql = """
SELECT * FROM E;
"""
```
- 変数 sql に格納された SQL プログラムを評価させている部分
```
cursor.execute(sql)
```
- 評価結果を一行ずつ処理して表示する部分
```
for row in cursor:
    print(row)
```

いろいろな SQL による問い合わせ

先ほど作成した Python のプログラムを書き換えて、様々な処理を実行してみる。

条件に合致する行のみの表示（その1）

先ほどのプログラムの sql 部分の一行を書き換えるだけである。

import sqlite3

DBNAME = "/home/ubuntuuser/mydb"

conn = sqlite3.connect(DBNAME)
cursor = conn.cursor()

sql = """
SELECT * FROM E WHERE student_name = 'KK';
"""
cursor.execute(sql)
for row in cursor:
    print(row)

conn.close()

条件に合致する行のみの表示（その2）

同様に、sql 部分の一行を書き換えるだけである。

import sqlite3

DBNAME = "/home/ubuntuuser/mydb"

conn = sqlite3.connect(DBNAME)
cursor = conn.cursor()

sql = """
SELECT * FROM E WHERE score > 80;
"""
cursor.execute(sql)
for row in cursor:
    print(row)

conn.close()

プレースホルダを用いた問い合わせ
埋め込み SQL では、SQL 文の中に「?」（プレースホルダ）を書き、値をプログラムから渡すことができる。SQL 文とデータを分離できるので、値を変えて同じ SQL を実行するときに便利であり、SQL インジェクション対策にもなる。
```
import sqlite3

DBNAME = "/home/ubuntuuser/mydb"

conn = sqlite3.connect(DBNAME)
cursor = conn.cursor()

sql = """
SELECT * FROM E WHERE student_name = ?;
"""
cursor.execute(sql, ("KK",))
for row in cursor:
    print(row)

conn.close()
```

Python プログラムの中に埋め込まれた SQL を用いたデータベース更新

次のような SQL を Python プログラムの中で実行することを考える。

insert into E values( 'Database',    90, 'BB', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    85, 'CC', datetime('now', 'localtime'), NULL );

Python でプログラムを作成するときの要点は次のとおりである。

cursor.executescript(...) は、複数の SQL を一度に実行するためのものである（cursor.execute(...) は一度に一つの SQL しか実行できない）。
conn.commit() はトランザクションをコミットするためのものである。

Python のプログラムは次のようになる。

import sqlite3

DBNAME = "/home/ubuntuuser/mydb"

conn = sqlite3.connect(DBNAME)
cursor = conn.cursor()

sql = """
insert into E values( 'Database',    90, 'BB', datetime('now', 'localtime'), NULL );
insert into E values( 'Database',    85, 'CC', datetime('now', 'localtime'), NULL );
"""
cursor.executescript(sql)
conn.commit()

conn.close()

SQLite Manager Tool などを使って、テーブル E を確認しておく。確かに2行増加している。

Python プログラムの中に埋め込まれた SQL を用いたテーブル一覧表示

SQLite 3 でデータベース内のテーブル一覧を表示するには、sqlite_master、sqlite_temp_master という名前の付いた特別なテーブルを使用する。

sqlite_master: TEMPORARY テーブル以外のデータベーススキーマが格納されている。
sqlite_temp_master: TEMPORARY テーブルのデータベーススキーマが格納されている。

データベーススキーマを確認したいときは、次のような SQL を実行する。

select * from sqlite_master;

sqlite_master、sqlite_temp_master に対して DROP TABLE、UPDATE、INSERT、DELETE 操作を行うことは許可されていない。

Python のプログラムは次のようになる。

import sqlite3

DBNAME = "/home/ubuntuuser/mydb"

conn = sqlite3.connect(DBNAME)
cursor = conn.cursor()

sql = """
SELECT * FROM sqlite_master;
"""
cursor.execute(sql)
for row in cursor:
    print(row)

conn.close()