【Python11】 データベース(DataBase)について

それでは下記の記事の続きをやっていこう!

👇前回までは、“ジェネレータ(generator)とイテレータ(iterator)” について学習してきました。

今回は、データベース(DataBase)の操作について学習していきましょう!

データベース(database)とは

✅データを保管する場所
✅ データ量や種類の多い場合はデータベース(DB)を用いて管理する
✅ほぼ全てのアプリやサービスのデータ管理にDBが使われている
✅ RDB(Relational DataBase):表形式でデータを保存しているもの(SQLite, PostrreSQL, MySQLなど)
✅ NoSQL(not only SQL):XMLやJSONファイルなどのドキュメント型やグラフ型、ディクショナリー型やカラム型がある

SQLite3

✅ 最もよく使われているDB
✅軽量かつ学習難易度が低い(※macにはデフォルトでインストール済)
SQL(Structured Query Language)という言語を使ってRDBを操作

sqlite3の基本的な使い方<Command>

$ sqlite3 : splite3を起動

$ sqlite3 <dbfile.db> : <dbfile.db>というデータベースを作成する(拡張子: .db)

👇 SQLiteの起動のさせ方とdbファイルの選択方法です。まだこの段階では.dbが保存されるわけではなく、dbの中の定義が完了したら.dbファイルが保存されます。

次にDBの中について定義方法をみていきます。

テーブルの作成(CREATE TABLE)

👇テーブルの定義は以下のようになります。行を”record”や”row”列を”column”一マスを”cell”と言います。テーブルの作成は以下のコマンドになります。

👇 データ型の種類は以下の通りで、各カラムに対してデータ型を定義します。

👇 また各テーブルに対してconstraintを付帯させることができる。また、保存されているテーブルの確認は以下の通りです。

👇実際のコマンド入力は以下の通りです。

レコードの作成(INSERT INTO)

先ほど作成したTableの各カラムに対して値を入れることができ、これをレコードの作成といいます。

👇レコードの作成方法は以下の通りです。各カラムに対してVALUEで値を書いていきます。

👇”INSERT INTO”でDBにレコードを作成しました。

SELECT文

先ほどINSERTで作成したレコードを、SELECT文を用いて取り出すことができます。

👇SELECT文での取り出し方法は下記の通りです。

👇実際のコマンド入力は以下の通りです。

更新(UPDATE)と削除(DELETE)

レコード内の更新と削除は以下の通りです。

👇実際のコマンド入力は以下の通りです。

DB情報出力(.dump)

👇実際のコマンド入力は以下の通りです。

Chinook data setのダウンロード&DB作成

✅ Open Data(👇大きなデータセットが用意されている)

リンク先⇨https://www.sqlitetutorial.net/sqlite-sample-database/


https://github.com/lerocha/chinook-databaseからchinook datasetを持ってくる
✅ Chinook_Sqlite.splファイルを使ってデータベースを作成
https://github.com/lerocha/chinook-database/tree/master/ChinookDatabase/DataSources/Chinook_Sqlite.spl

✅Chinook data set からデータベースとして保存する方法
1. Chinook_Splite.sqlファイルを上記からダウンロードする
2. $ sqlite3コマンドを実行してsqlite3を起動する
3. .read Chinook_Sqlite.sqlでSQLファイルを実行する
4. .tablesと.schemaでテーブル構造を確認
5. .backup chenook.dbでchinook.dbにバックアップと取る

👇実際のコマンド入力は以下の通りです。

並び替え(ORDER BY)

👇実際のコマンド入力は以下の通りです。この例はEmployeeテーブルの中でTitleが”Sales Support Agent”であるレコードをLastName順に表示した結果となります。

あいまい検索(LIKE)

👇実際のコマンド入力は以下の通りです。

表の結合(JOIN)

✅ 2つのテーブルから共通のkeyを指定し、このように結合することができる
 

✅表の結合方法は大きく分けて4つある
 

INNER JOIN

👇実際のコマンド入力は以下の通りです。

LEFT JOIN

👇実際のコマンド入力は以下の通りです。

RIGHT JOIN & OUTER JOIN

RIGHT JOINとOUTER JOINはsqlite3には標準装備されていないが、以下のような方法で表現することができる。

VIEW

コマンド入力は以下の通りです。

sqlite3モジュールでDBを作成

✅ sqlite3: pythonの標準ライブラリで入っている
✅ Pythonのsqlite3オブジェクトを使うことで簡単にDBを作成することができる

👇Pythonを使って”sample.db”を作成します

👇ターミナルから、作成したDBの確認をしてみます

PythonでDBからデータを取得する

✅ DBからのデータ取得方法は以下のものがある
 ・for文で回して取得
 ・.fetchall()を使い、現在のカーソル以下全てをタプルのリストで得る
 ・.fetchone()を使い、現在のカーソルのレコードをタプルで得る

👇サンプルコードは以下の通りです

Pythonでレコード情報を更新する

✅更新する手法によってはSQL ingectionの脆弱性に注意する必要がある(詳細は下記のコード参照)
✅ cursor.exectuteで渡す引数は必ずタプルにすること(引数が一つの場合は(new_age,)とする)

👇”Misako”のAgeが更新されているのが確認できる

ロールバック(Rollback)

下の図のように、Userテーブルの変更を管理するHistoryテーブルを作成し、UserテーブルはUPDATE、HistoryテーブルはINSERTしたいとする。その時にどちらかの変更でエラーが発生した場合、変更されてしまった箇所を元に戻す(Rollback)する必要がある。そのやり方を見ていこう。

👇Rollbackの使用例は下記になります。try,exceptでエラーが出たらrollbackしてあげます。

いかがだったでしょうか?全部やるとかなりの量になりましたね(笑)
他の記事でも何度か言ってますが、覚えようとするのは効率が悪いので推奨しません!
仕事の業務、または個人開発でもなんでもいいですが、実際に使っていくことで慣れるのが正しい習得方法です。

この記事を何周もするより使うことで慣れていくのが一番の近道なので、どんどん新しいことに挑戦して新しいスキルを身につけていきましょう!

今回はこの辺で、ばいばい👋