埋め込み SQL

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

教材の利用条件: クリエイティブコモンズ 表示-非営利-継承 4.0 国際ライセンス（CC BY-NC-SA 4.0）に基づき、著作者表示・非営利目的・同一ライセンスでの再配布を条件として自由に利用可能である。

前準備（Windows 上）

エディタのインストール

Windows の場合のみ，オプション）VS Code のインストール

エディタは何でもよいが，特に こだわりがない場合には， エディタとしては VS Code や IDLE を推奨している．

VS Code は優れたエディタである．Windows で動作する．

https://code.visualstudio.com/

Python 3.12 のインストール

以下のいずれかの方法で Python 3.12 をインストールする。

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

Python がインストール済みの場合、この手順は不要である。管理者権限のコマンドプロンプトで以下を実行する。管理者権限のコマンドプロンプトを起動するには、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：インストーラーによるインストール

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

Python の SQLite 3 モジュール

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

【関連する外部ページ】: https://docs.python.org/3/library/sqlite3.html

Python を使用してみる

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

Ubuntu の場合の手順例

Windows の場合の手順例

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

次のことを行うことができる．

SQLite 3の SQL に関する詳しい説明は：

SQLite 3 のデータ型 (SQL Data Types)

データ型の種類は，データベース管理システムごとに異なる． SQLite では，integer, real, text, BLOB などのデータ型を扱うことができる． 詳しい説明は https://www.sqlite.org/datatype3.html にある．要点は下の通りである．

* SQLite 3のデータ型と，SQL の標準が定めるデータ型の定義は異なる．大胆にまとめると，SQLite 3のデータ型の方がより大きな範囲のデータを扱える．

前準備

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

If you already define table 'E', you don't have to execute the following SQL again. 

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;

◆期待される結果

◆ ここでは，Sqliteman を使用するのではなく，Python のプログラムを作成してみる．

そこで，次の Python のプログラムをエディタで入力し，名前をつけて保存する 

import sqlite3
import os

DBNAME = "/home/ubuntuuser/mydb"

# データベースのディレクトリに移動
os.chdir(os.path.dirname(os.path.abspath(DBNAME)))
conn = sqlite3.connect(DBNAME)
cursor = conn.cursor()

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

conn.close()

* Ubuntu の場合の手順の例（Leafpad エディタを使用している）

  1. 「アクセサリ」→「Leafpad」
  2. 編集する
  3. 編集が終わったらファイルを保存する．

    ファイル名は英語が好ましい． ファイルを保存したディレクトリとファイル名を記憶しておくこと．

    下の実行例では，ディレクトリ名は「/home/ubuntuuser」，ファイル名は「hoge.py」である．

  4. 実行してみる

    端末 (LXTerminal) で，次のように実行し，実行結果を確認する．「python」は，Python言語処理系を呼び出すためのコマンドである． 

    cd ＜保存した Python プログラムのディレクトリ名 ＞
python ＜保存した Python プログラムのファイル名 ＞
  5. プログラムの要点を確認しておく
    • SQLプログラム「SELECT * FROM E;」を，文字列 のデータとして変数 sql に格納している部分 

      sql = """
SELECT * FROM E;
"""
    • 変数 sql に格納された SQL プログラムを評価させている部分 

      cursor.execute(sql)
    • 評価結果を１行ずつ処理し、表示する部分 

      for row in cursor:
    print(row)

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

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

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 でプログラムを作成するときの要点は：

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

import sqlite3
import os

DBNAME = "/home/ubuntuuser/mydb"

# データベースのディレクトリに移動
os.chdir(os.path.dirname(os.path.abspath(DBNAME)))
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 );
"""

try:
    cursor.executescript(sql)
    conn.commit()
except:
    conn.rollback()
    raise

conn.close()

SQLiteman などを使用して，テーブル E を確認しておく．確かに2行増加している．

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

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

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

select * from sqlite_master;

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

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

import sqlite3
import os

DBNAME = "/home/ubuntuuser/mydb"

# データベースのディレクトリに移動
os.chdir(os.path.dirname(os.path.abspath(DBNAME)))
conn = sqlite3.connect(DBNAME)
cursor = conn.cursor()

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

conn.close()