✨

Lecture 4 : SQL, Models and Migrations

2024/04/29に公開

django

SQL

sqlite3

tech

SQL

SQL（Structured Qurey Language）はデータベースの更新とクエリを可能にするプログラミング言語。SQLを使用して、データベースにデータを追加、更新、削除することができる。複雑なクエリを実行して特定の情報を検索することも可能。

データベース

SQLはリレーショナルデータベースで使用される。

主なリレーショナル・データベース管理システムの種類：

MySQL
PostgreSQL
SQLite

MySQLとPostgreSQLは高負荷に耐えられるシステムで、通常はWebサイトを実行するサーバーとは別のサーバーで実行される。一方、SQLiteはすべてのデータを1つのファイルに格納できる軽量のシステム。このコースではDjangoが使用するデフォルト・システムであるSQLIteを使用する。

リレーショナルデータベースについての参考記事：
https://www.oracle.com/database/what-is-a-relational-database/#:~:text=A relational database is a,of representing data in tables.

データ型

■ SQLite

TEXT：文字列（例：人の名前）
NUMERIC：数値データのより一般的な形式（例：日付、ブール値）
INTEGER：任意の整数（例：年齢）
REAL：任意の実数（例：人の体重）
BLOB：その他のバイナリデータ（例：画像）

テーブルの操作

新規テーブルの作成

CREATE TABLE flights (
	id INTEGER PRIMARY KEY AUTOINCREMENT, 
	origin TEXT NOT NULL,
	destination TEXT NOT NULL,
	duration INTEGER NOT NULL
);

PRIMARY KEY ← 主キーとして一意の識別子になるように指定
NOT NULL ← 必須指定

INSERTクエリ（値を挿入）

INSERT INTO flights
	(origin, destination, duration)
	VALUES ("New York","London", 415);

flights()の中に更新するカラム名を指定し、VALUESに指定した順番どおりに値を入力する。

SELECTクエリ（値を取り出す）

フライトテーブルからすべてのデータを取得するクエリ

SELECT * FROM flights;

実際にはすべてのカラムが必要ではないことのほうが多い。特定のカラムのみ取得するには*をカラム名で置き換える。

SELECT origin, destination FROM flights;

テーブルが大きくなるとクエリが返す行を絞り込む必要も出てくる。これを行うにはWHEREのあとに条件を追加する。

SELECT * FROM flights WHERE id = 3;

ID以外にも任意の列でもフィルタリングできる

SELECT * FROM flights WHERE origin = "New York"

ターミナルでの実行

touch flights.sql #空のSQLファイルを作成
sqlite3 flights.sql #dbを実行

テーブルを作成するコマンドを実行する

sqlite> CREATE TABLE  flights (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> origin TEXT NOT NULL,
   ...> destination TEXT NOT NULL,
   ...> duration INTEGER NOT NULL
   ...> );

.tablesコマンドでデータベースに現在存在するすべてのテーブルが表示される

sqlite> .tables
flights

テーブルへの挿入しSELECTで情報を取得する

sqlite> INSERT INTO flights (origin, destination, duration) VALUES("New York", "London", 415)
   ...> ;
sqlite> SELECT * FROM flight
   ...> ;
Parse error: no such table: flight
sqlite> SELECT * FROM flights
   ...> ;
1|New York|London|415

他のデータも挿入

sqlite> INSERT INTO flights (origin, destination, duration) VALUES ("Istanbul", "Tokyo", 700);
sqlite> INSERT INTO flights (origin, destination, duration) VALUES ("New York", "Paris", 435);
sqlite> INSERT INTO flights (origin, destination, duration) VALUES ("Moscow", "Paris", 245);
sqlite> INSERT INTO flights (origin, destination, duration) VALUES ("Lima", "New York", 455);

sqlite> SELECT * FROM flights;
1|New York|London|415
2|Shanghai|Paris|760
3|Istanbul|Tokyo|700
4|New York|Paris|435
5|Moscow|Paris|245
6|Lima|New York|455

結果の表示をより見やすくすることもできる

sqlite> .mode columns
sqlite> .headers yes
sqlite> SELECT * FROM flights;
id  origin    destination  duration
--  --------  -----------  --------
1   New York  London       415     
2   Shanghai  Paris        760     
3   Istanbul  Tokyo        700     
4   New York  Paris        435     
5   Moscow    Paris        245     
6   Lima      New York     455

等号・不等号を使って絞り込むこともできる

sqlite> SELECT * FROM flights WHERE duration > 500;
2|Shanghai|Paris|760
3|Istanbul|Tokyo|700

AND , ORを使うこともできる

sqlite> SELECT * FROM flights WHERE duration > 500 And destination = "paris";
sqlite> SELECT * FROM flights WHERE duration > 500 OR destination = "paris";
2|Shanghai|Paris|760
3|Istanbul|Tokyo|700

INをつかってデータの一部が当てはまるものを確認することもできる

sqlite> SELECT * FROM flights WHERE origin IN ("New York", "Lima");
1|New York|London|415
4|New York|Paris|435
6|Lima|New York|455

LIKEをつかって正規表現を使用することもできる

sqlite> SELECT * FROM flights WHERE origin LIKE "%a%";
2|Shanghai|Paris|760
3|Istanbul|Tokyo|700
6|Lima|New York|455

関数

SQLには様々な計算をできる組み込み関数が用意されている

AVERAGE（平均）
COUNT（個数）
MAX（最大）
MIN（最小）
SUM（合計）

etc.

更新（UPDATE）

すでに追加されているデータを更新する方法

下記は、ニューヨークからロンドンへ行く便の時間を変更するクエリ

sqlite> SELECT * FROM flights WHERE origin LIKE "%a%";
2|Shanghai|Paris|760
3|Istanbul|Tokyo|700
6|Lima|New York|455
sqlite> UPDATE flights 
   ...> SET duration = 430
   ...> WHERE origin = "New York"
   ...> AND destination = "London";
sqlite> SELECT * FROM flights;
1|New York|London|430
2|Shanghai|Paris|760
3|Istanbul|Tokyo|700
4|New York|Paris|435
5|Moscow|Paris|245
6|Lima|New York|455

削除（DELETE）

データを削除するクエリ

sqlite> DELETE FROM flights WHERE destination = "Tokyo";
sqlite> SELECT * FROM flights;
1|New York|London|430
2|Shanghai|Paris|760
4|New York|Paris|435
5|Moscow|Paris|245
6|Lima|New York|455

その他の文

LIMIT：テーブルから戻って来る結果の数を制限する
ORDER BY：指定した列に基づいて結果を並べ変える
GROUP BY ：指定した列で結果をグループ化する
HAVING：結果の数に基づいて追加条件を与える
…

これらSQL文は知っておくと便利だが、Djangoが代わりに実行してくれるので実際に書くことはない。

テーブルの結合

多くのデータベースが相互に関連する複数のテーブルで構成されている。

フライトの例では、都市に合わせて空港コードを追加する。

これにより、テーブルに同じ都市名を格納せずに済むので容量の節約にもなる。

複数テーブルを連携させるようにDBを構築する理由に対するChat GPTの回答

データベースにおいて複数のテーブルを連携させるように構築する理由は、データ管理を効率化し、データの整合性を保ち、冗長性を減らすことにあります。具体的には以下のような利点があります。

1. データの正規化

データベースの設計で一般的に行われる「正規化」というプロセスを通じて、データの重複を避け、データの整合性を保つことができます。正規化により、各テーブルは特定の情報のみを保持し、テーブル間で情報が重複することがなくなります。

2. クエリの効率性

複数のテーブルが関連付けられていることで、必要なデータを効率的に取得することができます。SQLのJOIN操作を用いて、関連するテーブルからデータを組み合わせて取得することができるため、柔軟かつ強力なデータアクセスを実現します。

3. データの保守性とスケーラビリティ

データが複数のテーブルに分散していることで、データの更新、追加、削除が容易になります。特定のデータの変更が他のデータに影響を与えることが少なくなるため、データベースの保守が容易になります。また、データベースのスケールアップやスケールアウトが必要な場合にも、データの分散構造が役立ちます。

4. データ整合性の維持

関連テーブル間での参照整合性を保つための制約（外部キー制約など）を設定することができます。これにより、データベース内のデータが常に信頼できる状態を保つことができます。

5. セキュリティの向上

テーブルごとにアクセス権を設定することができるため、データのセキュリティ管理が向上します。特定のユーザーには一部のテーブルのみアクセスを許可するなどの細かいアクセス制御が可能です。

これらの理由から、データベース設計では複数のテーブルを効果的に連携させることが推奨されています。データの整理、取り扱いの効率化、およびアプリケーションの性能向上に寄与するため、このアプローチが広く採用されています。

peopleテーブル

このテーブルでは人の名前だけを管理する

id	first	last
1	Harry	Potter
2	Ron	Weasley

passengersテーブル

flightとpeopleテーブルを結ぶテーブルで、このようなテーブルは関連テーブルと呼ばれる。

このテーブルはプライマリーキーの指定されていないので、同じ人に対して複数のフライトを紐づけることが出来る。多対多のマッピング。

person_id	flight_id
1	1
2	1
2	4

JOINクエリ

複数のテーブルを取得して結合する。

たとえば、乗客名と出発地、目的地を検索する際に、名前はpassengers、出発地・目的地はdestinationテーブルに格納されている場合に、ターブルを結合して取得するクエリは下記のようになる。

SELECT first, origin, destination
FROM flights JOIN passengers
ON passengers.flight_id = flights.id;

これはINNER JOINと呼ばれる方法で、テーブル間で一致しない行は無視される。

インデックスの作成

CREATE INDEX name_index On passengers (last);

SQLの脆弱性

SQLインジェクション

SQLインジェクション攻撃とは、悪意のあるユーザーがサイトのセキュリティ対策を回避するためにSQLコードを入力すること。

SELECT * FROM users
WHERE username = "harry" AND password = "12345";

WEBサイトへログインするときに、アカウントとパスワードを入力するとこのようなクエリが発行される。そのためユーザー名に「harry”—」と入力すると以降はコメント文の扱いになり、パスワードチェックをパスすることが出来てしまう。

SELECT * FROM users
WHERE username = "harry"--" AND password = "12345";

この対策としては、エスケープ文字を使用すること、またはSQL上の抽象レイヤー使用することでSQLクエリを書かなくて済むようにすること。Djangoでは抽象レイヤーを使ってSQLを扱う。

競合状態

競合状態とは、データベースに対して複数のクエリが同時発生する状況のこと。

たとえば、SNSでいいねが同時にされる場合、対策をしていないと予想外の問題がおこる。

対策方法の一つは、一つのトランザクション（処理）が完了するまでデータベースをロックすること。

Djangoモデル

新しいプロジェクトを作成

 django-admin startproject airline

フライトを記録するためにアプリを作成

python manage.py startapp flights

setting.pyにアプリをインストール

INSTALLED_APPS = [
    'flights',
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
]

つづいて、flightsへのアクセスがあったら、アプリ内のurlsを参照するようにマッピング

from django.contrib import admin
from django.urls import include, path

urlpatterns = [
    path('admin/', admin.site.urls),
    path('flights/', include("flights.urls"))
]

アプリ内のurlsはデフォルトでつくられていないので、作成する

form django.urls import path

form . import views

urlpatterns = [
	
]

つづいて「models.py」を構築していく。models.pyはアプリに存在するモデルを定義するのに使われる。すべてのモデルはPythonクラス。

modelsライブラリのModelクラスを継承したflightsを作成する。これによりFlightsクラスはDjangoのORM機能を利用して、データベースのテーブルに対応するモデルとして機能する。

# Create your models here.
class Flights(models.Model):

そして、モデルのフィールドを定義する

class Flights(models.Model):
	origin = models.CharField(max_length=64)
	destination = models.CharField(max_length=64)
	duration = models.IntegerFiles()