【JupyterとSqlite3】JupyterNotebookでSQL文を直接書きながら勉強できるようにする（メモ
JupyterNotebookでSQLの勉強をできるようにする。メモ。
データベースをあまり触ってこなかったので、これからSQLを少しずつでも覚えようかなと思います。
JupyterNotebookのセルに直接SQL文を書けるようにすれば覚えるのも早いかも。Pythonのsqlite3ライブラリは使いません。
直でSQLだけを書く！
実行環境
- Windows10
- Anaconda 4.11.0
- Python 3.9.7
- JupyterNotebook 6.4.6
- ネット接続（ライブラリ追加）
※ この投稿は全てJupyterNotebookで執筆・実行しています。ライブラリのインストール等も全てJupyterです。
JupyterLabやVSCodeのJupyter拡張は実行確認していません。
!conda -V
conda 4.11.0
!python -V
Python 3.9.7
!jupyter notebook -V
6.4.6
必要なライブラリのインストールと環境構築
ipython-sqlのインストールが必要とのこと
# condaなら conda install -c conda-forge ipython-sql # pipなら pip install ipython-sql
#%conda install -c conda-forge ipython-sql # 出力省略
上を実行すると以下のライブラリがインストールされました。
The following packages will be downloaded:
package | build
---------------------------|-----------------
ipython-sql-0.3.9 | pyhd8ed1ab_1004 18 KB conda-forge
prettytable-3.0.0 | pyhd8ed1ab_0 25 KB conda-forge
sqlparse-0.4.2 | pyhd8ed1ab_0 34 KB conda-forge
------------------------------------------------------------
Total: 77 KB
- SQLマジックコマンド拡張設定
%sqlと%%sqlのマジックコマンドを使えるようにする。
%load_ext sql
- sqliteに接続
「sample.db」というファイル名のsqlite3データベースを作ります。
%sql sqlite:///sample.db
'Connected: @sample.db'
カレントディレクトリに「sample.db」が作られます（テーブルを作成した後）。
- Sqlite3以外のデータベースを使う場合
sqlalchemyやデータベースごとの接続用のライブラリが必要なようです。
参考 ：
https://towardsdatascience.com/heres-how-to-run-sql-in-jupyter-notebooks-f26eb90f3259
よし、これでCodeセルにSQLを直接書いて実行できる環境が整った。
SQLを書く
ここからは実際に、Codeセルにマジックコマンド%sqlか%%sqlを書いて、SQL文を書いていきます。
詳しいことは抜きに、ざっくりやります。
テーブル新規作成 (CREATE TABLE テーブル名(カラム名1 型, カラム名2 型, ...) )
- 商品番号のテーブル「ProductCode」を作る
| 商品番号 | 商品名 |
|---|---|
| 1 | 値1 |
| 2 | 値2 |
このようなテーブルを作成します。まずは「テーブル名」と「カラム名、型」を作成します。
Excelでいうところの入力規則みたいなもの。
%%sql create table ProductCode ( 商品番号 INTEGER PRIMARY KEY AUTOINCREMENT, 商品名 TEXT );
sqlite://
* sqlite:///sample.db
(sqlite3.OperationalError) table ProductCode already exists
[SQL: create table ProductCode (
商品番号 INTEGER PRIMARY KEY AUTOINCREMENT,
商品名 TEXT
);]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
エラーが出ています。「table ProductCode already exists」
何度か同じコマンドを入力した為ｗ 無事テーブルが作られたようで。カラム名は日本語でも大丈夫みたい。
商品番号の型は「INTEGER PRIMARY KEY AUTOINCREMENT」しています。整数型で自動入力するという意味。勝手に通し番号が振られます。
【注意点】
- SQL文は大文字小文字のどちらでも動く。
- 一番最後にセミコロン
;を入力し忘れるとSQL文が終了しません。忘れないこと。 - 改行やスペースは特に神経質になる必要はない。全部１行で書いてもよし、読みやすく改行してもよし。
データを挿入 (INSERT INTO テーブル名(カラム名1, カラム名2,...) VALUES(値1, 値2,...) )
- 「ProductCode」テーブルの商品名を入れていきます。
%%sql insert into ProductCode(商品名) values('ノート'); insert into ProductCode(商品名) values('鉛筆'); insert into ProductCode(商品名) values('赤鉛筆'); insert into ProductCode(商品名) values('ボールペン'); insert into ProductCode(商品名) values('シャープペンシル'); insert into ProductCode(商品名) values('消しゴム');
sqlite://
* sqlite:///sample.db
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
valuesの文字列はダブルクォーテーション"かシングルクオーテーション'で囲みます。無いとエラー。
テーブルの中身を確認 (SELECT * FROM テーブル名)
%%sql select * from ProductCode
sqlite://
* sqlite:///sample.db
Done.
| 商品番号 | 商品名 | |
|---|---|---|
| 0 | 1 | ノート |
| 1 | 2 | 鉛筆 |
| 2 | 3 | 赤鉛筆 |
| 3 | 4 | ボールペン |
| 4 | 5 | シャープペンシル |
| 5 | 6 | 消しゴム |
| 6 | 7 | ノート |
| 7 | 8 | 鉛筆 |
| 8 | 9 | 赤鉛筆 |
| 9 | 10 | ボールペン |
| 10 | 11 | シャープペンシル |
| 11 | 12 | 消しゴム |
挿入のセルを二回実行してしまいｗ、データがかぶってしまっている。
※ 一番左の0から始まるインデックス番号はJupyterNotebookが自動で付けたものです。
データの削除 (DELETE FROM テーブル名 WHERE 条件文)
２回同じセルを実行したのでダブってしまった。消します。
削除は十分注意する必要がありますね。
重複を削除する方法もありますが、
「ProductCodeテーブル」の「商品番号」が7～12が被っているので条件を抽出してレコード（行）ごと消します。
%%sql delete from ProductCode where 商品番号 >= 7;
sqlite://
* sqlite:///sample.db
6 rows affected.
- 削除されたかテーブルを確認
%sql select * from ProductCode
sqlite://
* sqlite:///sample.db
Done.
| 商品番号 | 商品名 | |
|---|---|---|
| 0 | 1 | ノート |
| 1 | 2 | 鉛筆 |
| 2 | 3 | 赤鉛筆 |
| 3 | 4 | ボールペン |
| 4 | 5 | シャープペンシル |
| 5 | 6 | 消しゴム |
- 別なデータを入れてみる
%%sql insert into ProductCode(商品名) values('定規');
sqlite://
* sqlite:///sample.db
1 rows affected.
- 追加されたかテーブルを確認
%sql select * from ProductCode
sqlite://
* sqlite:///sample.db
Done.
| 商品番号 | 商品名 | |
|---|---|---|
| 0 | 1 | ノート |
| 1 | 2 | 鉛筆 |
| 2 | 3 | 赤鉛筆 |
| 3 | 4 | ボールペン |
| 4 | 5 | シャープペンシル |
| 5 | 6 | 消しゴム |
| 6 | 13 | 定規 |
削除した分の商品番号のつづきで番号が自動入力されています。削除されていることが分かる仕組みになっているようです。
【注意】
「DELETE FROM テーブル名;」としてしまうとテーブル丸ごと吹き飛ばします。星一徹です。
データの更新 (UPDATE テーブル名 SET カラム名 = '新規値' WHERE カラム名 = 値)
上のデータの商品番号13番の「定規」を「三角定規」に変更したいと思います。
%%sql UPDATE ProductCode SET 商品名 = '三角定規' WHERE 商品番号 = 13;
sqlite://
* sqlite:///sample.db
1 rows affected.
%%sql select * from ProductCode;
sqlite://
* sqlite:///sample.db
Done.
| 商品番号 | 商品名 | |
|---|---|---|
| 0 | 1 | ノート |
| 1 | 2 | 鉛筆 |
| 2 | 3 | 赤鉛筆 |
| 3 | 4 | ボールペン |
| 4 | 5 | シャープペンシル |
| 5 | 6 | 消しゴム |
| 6 | 13 | 三角定規 |
一応、ここまででデータベースの基本操作CRUD（Create Read Update Delete）を簡単に行いました。
最後に、もう一つ「商品価格」テーブルを作ります。そして上で作った「商品コード」のテーブルと結合（内部結合）させます。ExcelでいうVLOOKUP関数みたいなことをして今回は終わりにしようと思います。
テーブルの内部結合
SELECT テーブル名.カラム名, ... FROM テーブル名1 INNER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;
- 新規テーブル作成 ProductPrice（商品価格テーブル）
次のようなテーブルを作る。
| 商品名 | 商品価格 |
|---|---|
| ノート | 130 |
| 三角定規 | 450 |
%%sql create table ProductPrice ( 商品名 TEXT, 商品価格 INTEGER );
sqlite://
* sqlite:///sample.db
Done.
上で作ったテーブル「ProductCode」の入った「sample.db」という名前のsqliteファイルの中に、別のテーブル「ProductPrice」が作られました。
- データ追加
%%sql insert into ProductPrice(商品名, 商品価格) values('ノート', 130); insert into ProductPrice(商品名, 商品価格) values('消しゴム', 100); insert into ProductPrice(商品名, 商品価格) values('鉛筆', 60); insert into ProductPrice(商品名, 商品価格) values('三角定規', 450); insert into ProductPrice(商品名, 商品価格) values('シャープペンシル', 230); insert into ProductPrice(商品名, 商品価格) values('赤鉛筆', 120); insert into ProductPrice(商品名, 商品価格) values('ボールペン', 120); insert into ProductPrice(商品名, 商品価格) values('下敷き', 150); insert into ProductPrice(商品名, 商品価格) values('連絡帳', 200); insert into ProductPrice(商品名, 商品価格) values('スティックのり', 130); insert into ProductPrice(商品名, 商品価格) values('はさみ', 250);
sqlite://
* sqlite:///sample.db
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
- 二つのテーブルを表示させる
%%sql select * from ProductCode;
sqlite://
* sqlite:///sample.db
Done.
| 商品番号 | 商品名 | |
|---|---|---|
| 0 | 1 | ノート |
| 1 | 2 | 鉛筆 |
| 2 | 3 | 赤鉛筆 |
| 3 | 4 | ボールペン |
| 4 | 5 | シャープペンシル |
| 5 | 6 | 消しゴム |
| 6 | 13 | 三角定規 |
%sql select * from ProductPrice;
sqlite://
* sqlite:///sample.db
Done.
| 商品名 | 商品価格 | |
|---|---|---|
| 0 | ノート | 130 |
| 1 | 消しゴム | 100 |
| 2 | 鉛筆 | 60 |
| 3 | 三角定規 | 450 |
| 4 | シャープペンシル | 230 |
| 5 | 赤鉛筆 | 120 |
| 6 | ボールペン | 120 |
| 7 | 下敷き | 150 |
| 8 | 連絡帳 | 200 |
| 9 | スティックのり | 130 |
| 10 | はさみ | 250 |
順番は商品コードテーブルと違うようにバラバラに入れました。
- 内部結合
上下のテーブルで「商品名」カラムが共通しているので、これで内部結合します。
書き方は、
SELECT テーブル名.カラム名, ... FROM テーブル名1 INNER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;
%%sql SELECT ProductCode.商品番号, ProductCode.商品名, ProductPrice.商品価格 FROM ProductCode INNER JOIN ProductPrice ON ProductCode.商品名 = ProductPrice.商品名;
sqlite://
* sqlite:///sample.db
Done.
| 商品番号 | 商品名 | 商品価格 | |
|---|---|---|---|
| 0 | 1 | ノート | 130 |
| 1 | 2 | 鉛筆 | 60 |
| 2 | 3 | 赤鉛筆 | 120 |
| 3 | 4 | ボールペン | 120 |
| 4 | 5 | シャープペンシル | 230 |
| 5 | 6 | 消しゴム | 100 |
| 6 | 13 | 三角定規 | 450 |
素晴らしい。２つのテーブルから結合させて一つのテーブルを作ることが出来ました！
ぶっちゃけ、Pandasの方が簡単っていう・・・(*'▽')
おわりに
.table、.mode、.outputなどのコマンドを使うとテーブル一覧や、テーブル表示方法、テーブルの出力先をファイルにしたりといったことができますが、その使い方が分からない。なんかエラーが出る。
データベースを使わないシステムは存在しないそうです。Pythonの求人をみると大体「Webアプリ開発」「データ分析」「AI、機械学習」が載っています。全部データベースをゴリゴリ使うでしょう。その割には『Pythonで動かすデータベース入門』のようなタイトルの書籍は非常に少ない。ついでに『Pythonで学ぶ統計学』系も少ない。R言語やExcelかSQL単体のものはそこそこある。Pythonは「データ分析」でひっくるめたタイトルで売られている。入門書はもっとあって良さそうな気がします。
しばらくこれで遊べそうです。
【TermuxとsqliteとVim】全国の郵便番号CSVをsqliteにインポートする(メモ - よちよちpython
以上。
