それでは下記の記事の続きをやっていこう！
👇前回までは、“ジェネレータ(generator)とイテレータ(iterator)” について学習してきました。
今回は、データベース(DataBase)の操作について学習していきましょう！
Contents
- 1 データベース(database)とは
- 2 SQLite3
- 3 テーブルの作成(CREATE TABLE)
- 4 レコードの作成(INSERT INTO)
- 5 SELECT文
- 6 更新(UPDATE)と削除(DELETE)
- 7 DB情報出力(.dump)
- 8 Chinook data setのダウンロード&DB作成
- 9 並び替え(ORDER BY)
- 10 あいまい検索(LIKE)
- 11 表の結合(JOIN)
- 12 INNER JOIN
- 13 LEFT JOIN
- 14 RIGHT JOIN & OUTER JOIN
- 15 VIEW
- 16 sqlite3モジュールでDBを作成
- 17 PythonでDBからデータを取得する
- 18 Pythonでレコード情報を更新する
- 19 ロールバック(Rollback)
データベース(database)とは
✅データを保管する場所
✅ データ量や種類の多い場合はデータベース(DB)を用いて管理する
✅ほぼ全てのアプリやサービスのデータ管理にDBが使われている
✅ RDB(Relational DataBase)：表形式でデータを保存しているもの(SQLite, PostrreSQL, MySQLなど)
✅ NoSQL(not only SQL)：XMLやJSONファイルなどのドキュメント型やグラフ型、ディクショナリー型やカラム型がある
SQLite3
✅ 最もよく使われているDB
✅軽量かつ学習難易度が低い(※macにはデフォルトでインストール済)
✅ SQL(Structured Query Language)という言語を使ってRDBを操作
$ sqlite3 : splite3を起動
$ sqlite3 <dbfile.db> : <dbfile.db>というデータベースを作成する(拡張子: .db)
👇 SQLiteの起動のさせ方とdbファイルの選択方法です。まだこの段階では.dbが保存されるわけではなく、dbの中の定義が完了したら.dbファイルが保存されます。
|
1 2 3 4 5 6 7 8 |
<Command> # Desktopに移動 [~] $ cd ~/desktop # mydb.dbというファイルを選択(新規に作成するが、この段階ではまだ保存されていない) [~/desktop] $ sqlite3 mydb.db SQLite version 3.28.0 2019-04-15 14:49:49 Enter ".help" for usage hints. sqlite> |
次にDBの中について定義方法をみていきます。
テーブルの作成(CREATE TABLE)
👇テーブルの定義は以下のようになります。行を”record”や”row”、列を”column”、一マスを”cell”と言います。テーブルの作成は以下のコマンドになります。
👇 データ型の種類は以下の通りで、各カラムに対してデータ型を定義します。
👇 また各テーブルに対してconstraintを付帯させることができる。また、保存されているテーブルの確認は以下の通りです。
👇実際のコマンド入力は以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
# dbを起動 [~/desktop] $ sqlite3 mydb.db SQLite version 3.28.0 2019-04-15 14:49:49 Enter ".help" for usage hints. # Tableの作成 sqlite> CREATE TABLE User( ...> UseId INTEGER PRIMARY KEY NOT NULL, ...> Name TEXT DEFAULT 'anonymous', ...> Email TEXT NOT NULL, ...> Age INTEGER CHECK(Age > 0)); <-------");"を忘れずに # 今定義したdbが保存されているか確認 sqlite> .tables User # テーブルの構造を確認 sqlite> .schema CREATE TABLE User( UseId INTEGER PRIMARY KEY NOT NULL, Name TEXT DEFAULT 'anonymous', Email TEXT NOT NULL, Age INTEGER CHECK(Age > 0)); # sqlite3を停止する <meta charset="utf-8">sqlite> .quit |
レコードの作成(INSERT INTO)
先ほど作成したTableの各カラムに対して値を入れることができ、これをレコードの作成といいます。
👇レコードの作成方法は以下の通りです。各カラムに対してVALUEで値を書いていきます。
👇”INSERT INTO”でDBにレコードを作成しました。
|
1 2 3 4 5 6 7 8 9 |
[~/desktop] $ sqlite3 mydb.db SQLite version 3.28.0 2019-04-15 14:49:49 Enter ".help" for usage hints. sqlite> INSERT INTO User (UserId, Name, Email, Age) ...> VALUES (1, 'John', 'john@gmail.com', 30); sqlite> INSERT INTO User (UserId, Email, Age) ...> VALUES (2, 'sample@gmail.com', 40); sqlite> INSERT INTO User ...> VALUES (3, 'Hiroki', 'hiroki@gmail.com', 28) |
SELECT文
先ほどINSERTで作成したレコードを、SELECT文を用いて取り出すことができます。
👇SELECT文での取り出し方法は下記の通りです。
👇実際のコマンド入力は以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[~/desktop] $ sqlite3 mydb.db SQLite version 3.28.0 2019-04-15 14:49:49 Enter ".help" for usage hints. # Userテーブルから全てのカラムを表示 sqlite> SELECT * FROM User; 1|John|john@gmail.com|30 2|anonymous|sample@gmail.com|40 # ヘッダーの情報を表示する設定ON sqlite> .headers on # ヘッダーも含めて全てのカラムを表示 sqlite> SELECT * FROM User; UserId|Name|Email|Age 1|John|john@gmail.com|30 2|anonymous|sample@gmail.com|40 # Name, Emailのみ表示 sqlite> SELECT Name, Email FROM User; Name|Email John|john@gmail.com anonymous|sample@gmail.com |
更新(UPDATE)と削除(DELETE)
レコード内の更新と削除は以下の通りです。
👇実際のコマンド入力は以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# JohnのEmailをアップデート sqlite> UPDATE User SET Email="newjohn@gmail.com" WHERE Name="John"; # 確認 sqlite> SELECT * FROM User; UserId|Name|Email|Age 1|John|newjohn@gmail.com|30 2|anonymous|sample@gmail.com|40 # Johnのレコードを削除 sqlite> DELETE FROM User WHERE Name="John"; # 確認 sqlite> SELECT * FROM User; UserId|Name|Email|Age 2|anonymous|sample@gmail.com|40 |
DB情報出力(.dump)
👇実際のコマンド入力は以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 |
sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE User( UserId INTEGER PRIMARY KEY NOT NULL, Name TEXT DEFAULT 'anonymous', Email TEXT NOT NULL, Age INTEGER CHECK(Age > 0)); INSERT INTO User VALUES(2,'anonymous','sample@gmail.com',40); COMMIT; |
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にバックアップと取る
👇実際のコマンド入力は以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[~] $ cd desktop [~/desktop] $ sqlite3 SQLite version 3.28.0 2019-04-15 14:49:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. # Chinook_Sqlite.sqlを読み込み sqlite> .read Chinook_Sqlite.sql # データ構造を確認 sqlite> .tables Album Employee InvoiceLine PlaylistTrack Artist Genre MediaType Track Customer Invoice Playlist # chinook.dbとしてdbを保存 sqlite> .backup chinook.db |
並び替え(ORDER BY)
👇実際のコマンド入力は以下の通りです。この例はEmployeeテーブルの中でTitleが”Sales Support Agent”であるレコードをLastName順に表示した結果となります。
|
1 2 3 4 5 6 7 |
sqlite> SELECT * FROM Employee ...> WHERE Title="Sales Support Agent" ...> ORDER BY LastName; EmployeeId|LastName|FirstName|Title|ReportsTo|BirthDate|HireDate|Address|City|State|Country|PostalCode|Phone|Fax|Email 5|Johnson|Steve|Sales Support Agent|2|1965-03-03 00:00:00|2003-10-17 00:00:00|7727B 41 Ave|Calgary|AB|Canada|T3B 1Y7|1 (780) 836-9987|1 (780) 836-9543|steve@chinookcorp.com 4|Park|Margaret|Sales Support Agent|2|1947-09-19 00:00:00|2003-05-03 00:00:00|683 10 Street SW|Calgary|AB|Canada|T2P 5G3|+1 (403) 263-4423|+1 (403) 263-4289|margaret@chinookcorp.com 3|Peacock|Jane|Sales Support Agent|2|1973-08-29 00:00:00|2002-04-01 00:00:00|1111 6 Ave SW|Calgary|AB|Canada|T2P 5M5|+1 (403) 262-3443|+1 (403) 262-6712|jane@chinookcorp.com |
あいまい検索(LIKE)
👇実際のコマンド入力は以下の通りです。
|
1 2 3 4 5 6 |
# itを曖昧検索 3件ヒットしているのが分かる sqlite> SELECT * FROM Employee WHERE Title LIKE "%it%"; EmployeeId|LastName|FirstName|Title|ReportsTo|BirthDate|HireDate|Address|City|State|Country|PostalCode|Phone|Fax|Email 6|Mitchell|Michael|IT Manager|1|1973-07-01 00:00:00|2003-10-17 00:00:00|5827 Bowness Road NW|Calgary|AB|Canada|T3B 0C5|+1 (403) 246-9887|+1 (403) 246-9899|michael@chinookcorp.com 7|King|Robert|IT Staff|6|1970-05-29 00:00:00|2004-01-02 00:00:00|590 Columbia Boulevard West|Lethbridge|AB|Canada|T1K 5N8|+1 (403) 456-9986|+1 (403) 456-8485|robert@chinookcorp.com 8|Callahan|Laura|IT Staff|6|1968-01-09 00:00:00|2004-03-04 00:00:00|923 7 ST NW|Lethbridge|AB|Canada|T1H 1Y8|+1 (403) 467-3351|+1 (403) 467-8772|laura@chinookcorp.com |
表の結合(JOIN)
✅ 2つのテーブルから共通のkeyを指定し、このように結合することができる
✅表の結合方法は大きく分けて4つある
INNER JOIN
👇実際のコマンド入力は以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# TrackテーブルとGenreテーブルをGenreIdをkeyとしてINNER JOINする sqlite> SELECT g.Name AS GenreName, t.Name AS TrackName FROM Track t ...> INNER JOIN Genre g ON g.GenreId = t.GenreId LIMIT 10; GenreName|TrackName Rock|For Those About To Rock (We Salute You) Rock|Balls to the Wall Rock|Fast As a Shark Rock|Restless and Wild Rock|Princess of the Dawn Rock|Put The Finger On You Rock|Let's Get It Up Rock|Inject The Venom Rock|Snowballed Rock|Evil Walks |
LEFT JOIN
👇実際のコマンド入力は以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# CunstomerテーブルをA、EmployeegテーブルをBとしてLEFT JOINする sqlite> SELECT c.FirstName AS CustomerName, ...> e.FirstName AS SupportRepName, ...> e.Title AS SupportRepTitle ...> FROM Customer c ...> LEFT JOIN Employee e ON c.SupportRepId = e.EmployeeId; CustomerName|SupportRepName|SupportRepTitle Luís|Jane|Sales Support Agent Leonie|Steve|Sales Support Agent François|Jane|Sales Support Agent ... # カウント数を表示 sqlite> SELECT COUNT(*) ...> FROM Customer c ...> LEFT JOIN Employee e ON c.SupportRepId = e.EmployeeId; COUNT(*) 59 |
RIGHT JOIN & OUTER JOIN
RIGHT JOINとOUTER JOINはsqlite3には標準装備されていないが、以下のような方法で表現することができる。
VIEW
コマンド入力は以下の通りです。
|
1 2 3 4 5 6 7 |
sqlite> CREATE VIEW ITEmployee AS SELECT * FROM Employee WHERE Title LIKE "%IT%"; # 作成したITEmployeeのバーチャルテーブルを確認 sqlite> SELECT * FROM ITEmployee; EmployeeId|LastName|FirstName|Title|ReportsTo|BirthDate|HireDate|Address|City|State|Country|PostalCode|Phone|Fax|Email 6|Mitchell|Michael|IT Manager|1|1973-07-01 00:00:00|2003-10-17 00:00:00|5827 Bowness Road NW|Calgary|AB|Canada|T3B 0C5|+1 (403) 246-9887|+1 (403) 246-9899|michael@chinookcorp.com 7|King|Robert|IT Staff|6|1970-05-29 00:00:00|2004-01-02 00:00:00|590 Columbia Boulevard West|Lethbridge|AB|Canada|T1K 5N8|+1 (403) 456-9986|+1 (403) 456-8485|robert@chinookcorp.com 8|Callahan|Laura|IT Staff|6|1968-01-09 00:00:00|2004-03-04 00:00:00|923 7 ST NW|Lethbridge|AB|Canada|T1H 1Y8|+1 (403) 467-3351|+1 (403) 467-8772|laura@chinookcorp.com |
sqlite3モジュールでDBを作成
✅ sqlite3: pythonの標準ライブラリで入っている
✅ Pythonのsqlite3オブジェクトを使うことで簡単にDBを作成することができる
👇Pythonを使って”sample.db”を作成します
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
import sqlite3 # sample.dbが作成される con = sqlite3.connect("sample.db") print(con) # <sqlite3.Connection object at 0x109b524e0> cursor = con.cursor() # CREATE TABLE # "IF NOT EXISTS"をいれることでエラーが発生するのを防ぐ create_user_table_query = """ CREATE TABLE IF NOT EXISTS User ( UserId INTEGER PRIMARY KEY NOT NULL, Name TEXT DEFAULT 'annonymous', Email TEXT NOT NULL, Age INTEGER CHECK(Age > 0) ) """ cursor.execute(create_user_table_query) # INSERT insert_user_query = """ INSERT INTO User VALUES(1, 'Hiroki', 'hiroki@gmail.com', 28) INSERT INTO User VALUES(2, 'Misako', 'misako@gmail.com', 27) INSERT INTO User VALUES(3, 'Ryuki', 'ryuki@gmail.com', 25) """ # cursor.execute(insert_user_query) # ←INSERT文が一つの場合 cursor.executescript(insert_user_query) # ←INSERT文が複数の場合 # commitすることでinsertを反映させる(CREATE TABLEに関しては自動で補完してくれるので不要) con.commit() |
👇ターミナルから、作成したDBの確認をしてみます
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
sqlite> .tables User sqlite> .schema CREATE TABLE User ( UserId INTEGER PRIMARY KEY NOT NULL, Name TEXT DEFAULT 'annonymous', Email TEXT NOT NULL, Age INTEGER CHECK(Age > 0) ); sqlite> SELECT * FROM User; # ちゃんとレコードが作成されているのが確認できる 1|Hiroki|hiroki@gmail.com|28 2|Misako|misako@gmail.com|27 3|Ryuki|ryuki@gmail.com|25 |
PythonでDBからデータを取得する
✅ DBからのデータ取得方法は以下のものがある
・for文で回して取得
・.fetchall()を使い、現在のカーソル以下全てをタプルのリストで得る
・.fetchone()を使い、現在のカーソルのレコードをタプルで得る
👇サンプルコードは以下の通りです
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
import sqlite3 con = sqlite3.connect("sample.db") cursor = con.cursor() cursor.execute("SELECT * FROM User") # 全てのレコードをタプルで返す for row in cursor.execute("SELECT * FROM User"): print(row) # (1, 'Hiroki', 'hiroki@gmail.com', 28) (2, 'Misako', 'misako@gmail.com', 27) (3, 'Ryuki', 'ryuki@gmail.com', 25) # .fetchall():現在のカーソル以下全てをタプルのリストで返す print(cursor.fetchall()) # [(1, 'Hiroki', 'hiroki@gmail.com', 28), (2, 'Misako', 'misako@gmail.com', 27), (3, 'Ryuki', 'ryuki@gmail.com', 25)] # .fetchone(): 現在のcursorのレコードをタプルで返す print(cursor.fetchone()) # (1, 'Hiroki', 'hiroki@gmail.com', 28) print(cursor.fetchone()) # (2, 'Misako', 'misako@gmail.com', 27) |
Pythonでレコード情報を更新する
✅更新する手法によってはSQL ingectionの脆弱性に注意する必要がある(詳細は下記のコード参照)
✅ cursor.exectuteで渡す引数は必ずタプルにすること(引数が一つの場合は(new_age,)とする)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
import sqlite3 con = sqlite3.connect("sample.db") cursor = con.cursor() # 名前を聞き、その名前の年齢をアップデートするプログラムを組んでみる target_name = input("Whose 'age' do you want to update?: ") new_age = input("Tell me new age: ") print(target_name, new_age) # SQL injectionの脆弱性に注意(new_ageに'25; DROP TABLE User'などと入力されるとテーブルが消えてしまう) # update_query = f"UPDATE User SET age = {new_age} WHERE name = '{target_name}'" # cursor.executescript(update_query) # ↓必ずこちらの形にすること update_query = 'UPDATE User SET age = ? WHERE name = ?' # 渡す引数は必ずタプルにすること cursor.execute(update_query, (new_age, target_name)) con.commit() |
👇”Misako”のAgeが更新されているのが確認できる
|
1 2 3 4 5 6 7 8 9 10 11 |
sqlite> SELECT * FROM User; UserId|Name|Email|Age 1|Hiroki|hiroki@gmail.com|28 2|Misako|misako@gmail.com|28 3|Ryuki|ryuki@gmail.com|25 # =========更新============= sqlite> SELECT * FROM User; UserId|Name|Email|Age 1|Hiroki|hiroki@gmail.com|28 2|Misako|misako@gmail.com|18 3|Ryuki|ryuki@gmail.com|25 |
ロールバック(Rollback)
下の図のように、Userテーブルの変更を管理するHistoryテーブルを作成し、UserテーブルはUPDATE、HistoryテーブルはINSERTしたいとする。その時にどちらかの変更でエラーが発生した場合、変更されてしまった箇所を元に戻す(Rollback)する必要がある。そのやり方を見ていこう。
👇Rollbackの使用例は下記になります。try,exceptでエラーが出たらrollbackしてあげます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
import sqlite3 # Historyテーブルの作成 con = sqlite3.connect("sample.db") cursor = con.cursor() create_history_table_query = """ CREATE TABLE IF NOT EXISTS History ( Name TEXT, Age INTEGER ) """ cursor.execute(create_history_table_query) target_name = input("Whose 'Age' do you want to update?: ") new_age = input("Tell me new age: ") # Userテーブルを更新する update_User_query = 'UPDATE User SET Age = ? WHERE Name = ?' # Historyテーブルに入力する insert_history_query = 'INSERT INTO History VALUES (?, ?)' try: cursor.execute(insert_history_query, (target_name, new_age)) cursor.execute(update_User_query, (new_age, target_name)) except sqlite3.Error: print("sqlite3 error occured") # try文の中でエラーが発生したら必ずRollbackする(Userテーブルでエラーが発生すると、HistoryテーブルのUPDATEも取り消しになる) else: con.rollback() con.commit() |
いかがだったでしょうか？全部やるとかなりの量になりましたね(笑)
他の記事でも何度か言ってますが、覚えようとするのは効率が悪いので推奨しません！
仕事の業務、または個人開発でもなんでもいいですが、実際に使っていくことで慣れるのが正しい習得方法です。
この記事を何周もするより使うことで慣れていくのが一番の近道なので、どんどん新しいことに挑戦して新しいスキルを身につけていきましょう！
今回はこの辺で、ばいばい👋
HELLO. I can’t post a comment without it hanging.
I’m sorry, I didn’t set it so that viewers could post comments.
I would like to investigate and make it possible to post.