埋め込み SQL

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

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

前準備（Windows 上）

エディタのインストール

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

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

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

https://code.visualstudio.com/

Python のインストール（Windows上）

注：既にPython（バージョン3.12を推奨）がインストール済みの場合は，この手順は不要である．

winget（Windowsパッケージマネージャー）を使用してインストールを行う

  1. Windowsで，コマンドプロンプトを管理者権限で起動する（例：Windowsキーを押し，「cmd」と入力し，「管理者として実行」を選択）
  2. winget（Windowsパッケージマネージャー）が利用可能か確認する： 
    winget --version
  3. Pythonのインストール（下のコマンドにより Python 3.12 がインストールされる）． 
    winget install --scope machine Python.Launcher
winget install --scope machine Python.Python.3.12

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()