データベース入門⌗

データベースとは、構造を持ったデータの集合のことです。簡単に言うと、あるルールに従って格納・取り出しができるデータの集まりです。

いろいろな種類のデータベースがありますが、今回はリレーショナルデータベース(RDB)と呼ばれるデータベースの1つであるSQLiteを使ってデータベースの使い方を学びましょう。

リレーショナルデータベース(RDB)とは⌗

リレーショナルデータベース(RDB)では、データの格納方法をエクセルやスプレッドシートのように、二次元（行と列）の表形式として事前に定義し、構造化データとして保存します。

RDBはSQLという言語を用いて、データの内容に応じた処理（登録・検索・更新・削除）を行うことができます。

また、複数の表を関係づけることで、情報を効率的に保存したり、複雑な条件での検索が可能になります。

具体的には下のようなアドレス帳のようなものです。

ID	名前	住所	電話番号
1	田中太郎	東京都江東区	1234
2	佐藤花子	宮城県仙台市	5678
3	佐藤三郎	東京都足立区	0123

各行が1つのデータ（レコード）に対応していて、各列（フィールド）がデータの種類を示します。

このような1つの表のことをテーブルと呼びます。

SQLとは⌗

SQLとはStructured Query Languageの略で、データベースを操作するためのプログラミング言語です。データの追加、削除、更新、検索と言った操作を行えます。

以下は検索を行うSQL文の例で「stockというテーブルからpriceというフィールドが1000以下のレコードを取り出して」の意味です。詳しい説明は後ほど行うので、ここではなんとなくイメージを掴んでもらえると良いです。

SELECT * FROM stock WHERE price <= 1000;

SQLite⌗

SQLiteは、専用のサーバーを必要とせず、パソコン上でも手軽に利用できるRDBで、小規模なデータ管理で広く利用されています。 SQLiteはデータベース全体を1つのファイルで管理します。そのため、バックアップや別の環境への移行も、ファイルをコピーするだけで簡単に行えます。

SQLiteのインストール⌗

Linux(Debian)ではSQLiteはインストール済みなので、あらためてインストールする必要はありません。確認のため、ターミナルから以下のコマンドを入力してみてください。

sqlite3 -version

バージョン番号が表示されれば、SQLiteがインストール済みです。

Windowsなど、SQLiteがインストールされていない場合は以下のサイトからダウンロードしてください。

https://www.sqlite.org/

上記サイトの「Download」タブからOSに対応したzipファイルをダウンロードし、適当なフォルダ上に解凍し、そのフォルダをPATHに追加し、コマンドラインからsplite3を実行すればSQLiteが起動します。

データベースの作成⌗

今回は果物の在庫表データベースを作ってみて理解を深めましょう。

コマンドラインからcdコマンドでデータベースを作成したいフォルダ（例えば、あらかじめホームディレクトリの下に「sqlite」というフォルダを作成し、そのフォルダ）に移動し、sqlite3 データベース名（新規）を実行してSQLiteにデータベースを作成します。 SQLiteはデータベース全体を1つのファイルで管理するので、「データベース名」がそのまま「ファイル名」になり、フォルダに新しいファイルが作成されます。

以下のようにコマンドラインから実行して、shop.dbというデータベースを作成しましょう。

sqlite3 shop.db

すると以下のように表示されます。

SQLite version XXX
Enter ".help" for usage hints.
sqlite>

この操作で、新しいデータベースが作成され、そのデータベースに接続され、sqlite>というSQLiteのシェルが起動されます。エクスプローラ等で見てみるとフォルダに「shop.db」という新しいファイルが作成されているのがわかります。

なお、すでに存在しているデータベースに接続する場合はsqlite3 既存のデータベース名（ファイル名）を実行します。

SQL文はsqlite>というSQLiteのシェルから入力します。SQL文の末尾には「;」を付けてSQL文が終了したことを示します。

今回作りたい在庫表テーブル⌗

今回作りたい果物の在庫表は以下のように、4つのフィールドを持っているものとします。

id	name	price	country
1	orange	100	us
2	apple	150	japan
3	mango	300	taiwan
4	orange	180	japan
5	grape	500	italy

nameは果実の名前、priceは値段、countryは産地（国名）を表します。ちなみにIDは、このテーブルにおける主キーと呼ばれるもので、レコードを一意に区別するためのものです。そのため主キーにおいては同じ値の重複と空の値が禁止されています。

テーブルの作成・削除⌗

テーブルを作成するにはCREATE TABLE テーブル名 (フィールド名型、...);というSQL文を入力します。

在庫表テーブルの作成には以下のようなSQL文をSQLiteのシェルに入力します。

CREATE TABLE stock
	(id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT,
	price INTEGER,
	country TEXT);

CREATE TABLE stockでテーブルの名前をstockと定義しています。

2行目以降は各フィールドのフィールド名とデータ型を定義しています。データ型にはNULL、INTEGER、REAL、TEXT、BLOBの5種類があります。通常は以下の3種類を使用します。

INTEGER 整数
REAL 実数、小数点のある数値
TEXT 文字列、日本語も可

2行目のPRIMARY KEYは、このフィールドが主キーであるという宣言です。また、AUTOINCREMENTとあるのは、このフィールドに対し、新たに追加されたレコードに対して、現在格納されているレコードのうち最大のidに1だけ足したidを与えるという設定です。これによって、他のレコードの値と被る（重複する）ことはありません。

なお、作成したテーブルを削除する場合はDROP TABLE テーブル名;というSQL文を入力します。

テーブル情報の確認⌗

SQLiteのシェルから.schema テーブル名コマンドを実行すると作成したテーブル情報を確認することができます。

sqlite> .schema stock

すると、以下のように表示されます

CREATE TABLE stock
(id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
price INTEGER,
country TEXT);
sqlite>

新しいデータ（レコード）を登録する⌗

新しいデータ（レコード）の登録はINSERT INTO テーブル名 (フィールド名,...) VALUES （値,...);のSQL文で行えます。

先ほどの果物の在庫表の1つめのorange, 100, usを追加してみます。

INSERT INTO stock
	(name, price, country)
	VALUES ('orange', 100, 'us');

これにならって表にある他の果物もすべて追加してください。

データ（レコード）の内容を確認する⌗

全てのデータ（レコード）の内容を確認するには以下のSQL文を入力します。

SELECT * FROM stock;

果物の在庫表すべてが登録できていれば、以下のように表示されるはずです。

1|orange|100|us
2|apple|150|japan
3|mango|300|taiwan
4|orange|180|japan
5|grape|500|italy

最初のフィールドのidはデータを追加した順に1から1つずつ増えた値が自動的に付与されています。

この表示が見にくいなと思う場合はSQLiteのシェルに以下のコマンドを入力してください。

sqlite> .header on
sqlite> .mode column

入力後、上記のSELECTコマンドを実行すると以下のようにフィールド名が付与され、インデントで整列されて表示されるようになります。

id          name        price       country   
----------  ----------  ----------  ----------
1           orange      100         us        
2           apple       150         japan     
3           mango       300         taiwan    
4           orange      180         japan     
5           grape       500         italy

データ（レコード）を検索する⌗

先ほどデータ（レコード）内容の確認で使用した、SELECTというコマンドはデータ（レコード）を取り出すためのものです。

SELECT フィールド名1, フィールド名2, ... FROM テーブル名;というSQL文で指定したテーブルから指定したフィールドの値を取り出すことができます。

なお、先ほどのデータ（レコード）内容の確認で使用した*はワイルドカードと呼ばれ、フィールド名の代わりにこれを指定するとすべてのフィールドの値を取り出すことができます。

WHEREによる条件付け⌗

作成したテーブルから日本産の果物だけを見たいときはどうすれば良いのでしょうか。ここで活躍するのが、WHERE句による条件づけです。

SELECT * FROM stock WHERE 条件;というSQL文で条件にあうデータ（レコード）だけを検索・出力できます。

日本産の果物だけという条件であれば、以下のようなSQL文になります。

SELECT * FROM stock
	WHERE country = 'japan';

以下のように、日本産の果物のデータ（レコード）のみが表示されます。

2|apple|150|japan
4|orange|180|japan

SQL文のWHERE country = 'japan'の部分が、countryフィールドが’japan’となるような条件づけを行っています。

例えば、150円以上の果物を表示したいときは、以下のようなSQLコマンドにします。

SELECT * FROM stock
	WHERE price >= 150;

すると、以下のように表示されます。

2|apple|150|japan
3|mango|300|taiwan
4|orange|180|japan
5|grape|500|italy

データ（レコード）を更新する⌗

データの更新を行ってみましょう。

例として、日本産のオレンジの値段を130円に下げたい場合の例です。

UPDATE stock
	SET price = 130
	WHERE name = 'orange' AND country = 'japan';

このようにUPDATE テーブル名 SET フィールド名 = 新しい値 WHERE 条件;というSQL文で更新を行います。

SET フィールド名 = 新しい値（上記例ではprice = 130）によって更新したいフィールド名(price)に新しい値(130)を渡しています。

WHERE 条件の部分で、上記例ではANDを使って2つの条件(name = 'orange'とcountry = 'japan'）がどちらも成り立つようなレコードを指定しています。 ANDの他にもOR（どちらか）やNOT（でない）などを使用することができます。

上記SQL文を実行した後、以下のSQL文を実行して、正しく更新されていることを確認してください。

SELECT * FROM stock
	WHERE name = 'orange' AND country = 'japan';

以下のように更新された値が表示されます。

4|orange|130|japan

今回の例では日本産のオレンジという条件にしましたが、たとえば日本産のオレンジが複数個あった場合は、複数個すべての値段が新しい値に更新されます。もし、特定のレコードだけ更新したい場合は、条件をid = 更新したいレコードのidにします。 idは一意（重複しない）なのでidを指定することで特定のレコードのみを更新することができます。

データ（レコード）を削除する⌗

作成したデータ（レコード）を削除するにはDELETE FROM テーブル名 WHERE 条件;というSQL文で削除します。

日本産のオレンジを削除したい場合は、以下のようなSQL文を入力します。

DELETE FROM stock
	WHERE name = 'orange' AND country = 'japan';

この処理は削除が必要な時に行う処理ですが、今回は実行しなくて結構です。

SQLiteのシェルを終了する⌗

SQLiteのシェルを終了して、データベースへのアクセスを終了するには.exitというコマンドを入力します。

データベースにアクセスし続けていると、他の人（アプリ）がそのデータベースに書き込むことができなくなってしまいます。データベースへの操作が完了したら、速やかにSQLiteのシェルを終了して、データベースへのアクセスを終了してください。

その他の機能⌗

今回は詳細までは触れませんが、SQLiteには以下のような様々な機能があります。

ビューの作成・削除

作成済みのテーブルの一部（例えば条件）を新たな仮想的なテーブルにします。たとえば果物の在庫表でいえば、国ごとにビューという仮想テーブルをつくることができます。
インデックスの作成・削除

テーブルに多くのフィールドがあったり、大量のデータが格納されている場合、検索に時間がかかる場合があります。このような場合、適当なフィールドでインデックス（並べ替え）を作っておくことで、検索を早くすることができます。
トリガーの作成・削除

特定のテーブルのデータが更新されたら、同時に別のテーブルのデータを更新したい場合に使用します。 BEFOREトリガーではデータ更新前にトリガーに記述されたSQL文が実行されます。 AFTERトリガーではデータ更新後にトリガーに記述されたSQL文が実行されます。
データ取得（SELECT）の追加機能
- 取得データをフィールドの値でソートする
- 指定した範囲の値を取得する
- 指定した値のリストに合致するものを取得する
- パターンマッチングしたものを取得する
- フィールド名に別名を付ける
- 重複するデータを削除して取得する
- 取得するデータの数と開始位置を指定する
- フィールドの値に対して演算を行った結果を取得する
- フィールドの値に応じて異なる結果を返す条件文を記述する
- データをグループ化して合計や平均などを計算する
テーブルの結合
- 共通のフィールドに対して同じ値が格納されているデータのみを結合して取得する（内部結合）
- 共通のフィールドに対してどちらかのデータがあれば全て結合して取得する（外部結合）
- ２つのテーブルのデータの全ての組み合わせを取得する
関数
- 数値の演算関数（合計、平均、最大、最小、四捨五入、絶対値、乱数）
- 日付や時刻の取得
- 文字列関数（文字数、大文字・小文字変換、置換、部分文字列、先頭・末尾の空白削除）
- その他（データ型の取得など）

他のアプリとの連携⌗

ユーザーが直接SQL文を使用して、データベースにアクセスすることはめったにありません。データベースはアプリケーションを介して利用されることが一般的です。

たとえば、果物の在庫表の場合、果物店の在庫検索アプリ、果物店の売上管理アプリ、果物店の在庫管理アプリ、果物店の発注管理アプリなど様々なアプリが共通の果物の在庫表情報を必要とします。このような場合に、共通の果物の在庫表情報を一元的に管理する仕組みがデータベースなのです。

各アプリはSQL文を使って果物在庫表データベースにアクセスし、必要な情報を取得したり、追加したり、変更したりします。

アプリからデータベースにアクセスする方法について、pythonを例にして以下で説明します。

pythonでSQLiteを使用する⌗

pythonを使ってSQLiteにアクセスする方法は以下のとおりです。

import sqlite3

con = sqlite3.connect("データベース名")	# データベースに接続する
cur = con.cursor()	# SQL文を使用するため、データベース・カーソルを作成する
cur.execute("SQL文")	# SQL文を実行する（INSERTなど結果がない場合）
res = cur.execute("SQL文")	# SQL文を実行する（SELECTなど結果がある場合）
res.fetchone()	# 1個の結果を取得する
res.fetchall() # すべての結果を配列で取得する
con.commit()	# データベースの更新を確定する（更新しない場合はなくてもよい）
cur.close()	# データベース・カーソルを閉じる
con.close()	# データベースの接続を閉じる

なお、cur.execute("SQL文")でSQL文を実行する際には、SQLiteのシェルから実行する時のように末尾にSQL文の終了を示す「;」を付ける必要はありません。

たとえば、先ほど作成したshopデータベースのstockテーブルから、全てのデータを取得してprintしてみましょう。

import sqlite3

con = sqlite3.connect("shop.db")
cur = con.cursor()
res = cur.execute("SELECT * FROM stock")
print(res.fetchall())
con.commit()	# 更新しないので、なくてもよい
cur.close()
con.close()

shop.dbファイルが置かれている場所で、上記プログラムを実行するとコンソールに以下のように表示されます。

[(1, 'orange', 100, 'us'), (2, 'apple', 150, 'japan'), (3, 'mango', 300, 'taiwan'), (4, 'orange', 130, 'japan'), (5, 'grape', 500, 'italy')]

全てのデータがレコードごとの配列として出力され、各レコードの内容はフィールドごとのタプル（変更できない配列）になっています。このようにテーブルのデータが二次元の配列として出力されているのがわかります。

したがって、data = res.fetchall()とした時、0から始まるi番目のレコードの0から始まるj番目のフィールドのデータはdata[i][j]で取得することができます。

cur.execute("SQL文")のSQL文をいろいろなSQL文に変えて、正しく実行され、出力されることを確認してみてください。

最後にpythonでこのデータベースを使った簡単なアプリを作ってみましょう。

在庫検索アプリの作成⌗

まず、最初のアプリは果実店の店頭に置く在庫検索アプリです。果実名と国名を入力すると、在庫があれば、「果実名、値段、国名」を表示し、在庫がなければ「在庫はありません！！」と表示します。

import sqlite3

con = sqlite3.connect("shop.db")
cur = con.cursor()
result = cur.execute("SELECT DISTINCT name FROM stock")
names = result.fetchall()
result = cur.execute("SELECT DISTINCT country FROM stock")
countries = result.fetchall()
for i, name in enumerate(names):
    print(f"{i+1}: {name[0]}", end=" ")
print("\n果物名の番号を入力してください： ", end='')
name = names[int(input())-1][0]
for i, country in enumerate(countries):
    print(f"{i+1}: {country[0]}", end=" ")
print("\n国名の番号を入力してください： ", end='')
country = countries[int(input())-1][0]
result = cur.execute(f"SELECT * FROM stock WHERE name = '{name}' AND country = '{country}'")
fruit = result.fetchone()
if fruit is None:
    print("在庫はありません！！")
else:
    print(f"果実名: {fruit[1]}, 値段: {fruit[2]}, 国名: {fruit[3]}")
con.commit()		# 更新しないのでなくてもよい
cur.close()
con.close()

データベースに接続した後、SELECT DISTINCT name FROM stockというSQL文で在庫の中の果実名の一覧を取得しています。なお、DISTINCTというのは重複なしに取得するという意味です。

次に、SELECT DISTINCT country FROM stockいうSQL文で在庫の中の国名の一覧を取得しています。

そして、番号付で果実名の一覧を表示し、番号を入力させ、検索したい果実名をname変数に入れます。

次に、番号付で国名の一覧を表示し、番号を入力させ、検索したい国名をcountry変数に入れます。

最後に、SELECT * FROM stock WHERE name = '{name}' AND country = '{country}'というSQL文で検索したい果実名かつ検索したい国名のデータ（レコード）を取得します。

取得した1個目のデータを取得し、fruit変数に入れます。もし、該当するデータがなければfruit変数にはNoneが入るので「在庫がありません！！」と出力し、該当するデータがあれば、fruit変数の値を表示しています。（fruit[0]にid、fruit[1]に名前、fruit[2]に値段、fruit[3]に国名が入っています）

最後にカーソルとデータベースを閉じます。

このアプリの実行結果は以下のとおりです。

1: orange 2: apple 3: mango 4: grape 
果物名の番号を入力してください： 1
1: us 2: japan 3: taiwan 4: italy 
国名の番号を入力してください： 2
果実名: orange, 値段: 130, 国名: japan

1: orange 2: apple 3: mango 4: grape 
果物名の番号を入力してください： 1
1: us 2: japan 3: taiwan 4: italy 
国名の番号を入力してください： 3
在庫表にありません！！

在庫管理アプリの作成⌗

次に、簡単な在庫管理アプリを作成してみます。

このアプリには、以下の機能があります。

在庫の一覧表示
在庫の追加
在庫の削除

import sqlite3

con = sqlite3.connect("shop.db")
cur = con.cursor()

items = ["在庫の一覧表示", "在庫の追加", "在庫の削除"]

for i, item in enumerate(items):
    print(f"{i+1}: {item}", end=" ")
print("\nどの番号のメニューにしますか？ ", end="")
no = int(input())

if no == 1:
    result = cur.execute("SELECT * FROM stock")
    data = result.fetchall()
    print("id\t果実名\t値段\t国名")
    for record in data:
        print(f"{record[0]}\t{record[1]}\t{record[2]}\t\t{record[3]}")
elif no == 2:
    result = cur.execute("SELECT DISTINCT name FROM stock")
    names = result.fetchall()
    names.append(["その他"])
    for i, name in enumerate(names):
        print(f"{i+1}: {name[0]}", end=" ")
    print("\n果物名の番号を入力してください： ", end='')
    i = int(input())
    if i == len(names):
        print("果実名を入力してください： ", end='')
        name = input()
    else:
        name = names[i-1][0]
    print("値段を入力してください： ", end='')
    price = int(input())
    result = cur.execute("SELECT DISTINCT country FROM stock")
    countries = result.fetchall()
    countries.append(["その他"])
    for i, country in enumerate(countries):
        print(f"{i+1}: {country[0]}", end=" ")
    print("\n国名の番号を入力してください： ", end='')
    i = int(input())
    if i == len(countries):
        print("国名を入力してください： ", end='')
        country = input()
    else:
        country = countries[i-1][0]
    cur.execute(f"INSERT INTO stock (name, price, country) VALUES ('{name}', {price}, '{country}')")
elif no == 3:
    result = cur.execute("SELECT * FROM stock")
    data = result.fetchall()
    print("id\t果実名\t値段\t国名")
    for record in data:
        print(f"{record[0]}\t{record[1]}\t{record[2]}\t{record[3]}")
    print("\n削除するレコードのidを入力してください： ", end='')
    id = int(input())
    cur.execute(f"DELETE FROM stock WHERE id = {id}")
    
con.commit()
cur.close()
con.close()

実行すると、最初のメニューに従って、在庫の一覧表示、在庫の追加、在庫の削除が行えます。

在庫の一覧表示の実行例⌗

1: 在庫の一覧表示 2: 在庫の追加 3: 在庫の削除 
どの番号のメニューにしますか？ 1
id	果実名	値段	国名
1	orange	100		us
2	apple	150		japan
3	mango	300		taiwan
4	orange	130		japan
5	grape	500		italy

在庫の追加の実行例⌗

1: 在庫の一覧表示 2: 在庫の追加 3: 在庫の削除 
どの番号のメニューにしますか？ 2
1: orange 2: apple 3: mango 4: grape 5: その他 
果物名の番号を入力してください： 5
果実名を入力してください： banana
値段を入力してください： 200
1: us 2: japan 3: taiwan 4: italy 5: その他 
国名の番号を入力してください： 3

在庫の追加では、果実名と値段と国名を入力します。果実名と国名はそれぞれ在庫にある果実名と国名の一覧およびその他を選択させ、その他を選択した場合は、名称を直接入力します。

上記の例では、台湾産のバナナ（バナナは在庫表にはない）200円を追加しています。

在庫の削除の実行例⌗

1: 在庫の一覧表示 2: 在庫の追加 3: 在庫の削除 
どの番号のメニューにしますか？ 3
id	果実名	値段	国名
1	orange	100	us
2	apple	150	japan
3	mango	300	taiwan
4	orange	130	japan
5	grape	500	italy
7	banana	200	taiwan

削除するレコードのidを入力してください： 7

在庫の削除では在庫の一覧を表示し、削除する在庫をidで選択します。

上記の例ではさきほど追加したばかりの台湾産のバナナを削除しました。

データベースの利点⌗

在庫検索アプリでは、データベースから在庫を検索します。

在庫管理アプリでは新しい在庫が増えたらデータベースに追加し、在庫がなくなったらデータベースから削除します。

このように、在庫検索アプリと在庫管理アプリは果物の在庫表という共通のデータベースを使っているので、別々にアプリを作ってもデータの整合性が確保され、在庫検索アプリでは常に最新の在庫状況が確認できます。これが、アプリとデータベースを分けておく利点です。

なお、データの整合性を確保するため、どちらかのアプリがデータベースにアクセスしている間はデータベースへの書き込みはロックされます（読み込みはできます）。

まとめ⌗

SQLiteをつかってリレーショナルデータベース(RDB)とSQLの基本を学習しました。また、pythonからSQLiteを呼び出して使用する方法も学習しました。

データベース管理システムは別名「ミドルウェア」とも呼ばれ、ユーザーが直接使用することはありませんが、アプリケーションを介していろいろな場面で使用されています。

pythonなどでアプリケーションを作成する際、「〇〇帳」のようなデータテーブルが必要になったらデータベースの活用を検討してみてください。複数のアプリとの共通化やデータの整合性（常に最新のデータが得られる）などデータベースを活用することで様々な利点を得られます。

なお、SQL文については簡単な解説しかしていませんが、その他の機能も含め、興味のある人はぜひ勉強してみてください。 paizaなどの学習サイトでも学ぶこともできます。

レッスン121