よちよちpython

独習 python/Qpython/Pydroid3/termux/Linux

【JupyterとSqlite3】JupyterNotebookでSQL文を直接書きながら勉強できるようにする(メモ

JupyterNotebookSQLの勉強をできるようにする。メモ。



データベースをあまり触ってこなかったので、これから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



「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」
何度か同じコマンドを入力した為w 無事テーブルが作られたようで。カラム名は日本語でも大丈夫みたい。

商品番号の型は「INTEGER PRIMARY KEY AUTOINCREMENT」しています。整数型で自動入力するという意味。勝手に通し番号が振られます。



【注意点】

  • SQL文は大文字小文字のどちらでも動く。
  • 一番最後にセミコロン;を入力し忘れるとSQL文が終了しません。忘れないこと。
  • 改行やスペースは特に神経質になる必要はない。全部1行で書いてもよし、読みやすく改行してもよし。



データを挿入 (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 消しゴム

挿入のセルを二回実行してしまいw、データがかぶってしまっている。

※ 一番左の0から始まるインデックス番号はJupyterNotebookが自動で付けたものです。



データの削除 (DELETE FROM テーブル名 WHERE 条件文)

2回同じセルを実行したのでダブってしまった。消します。
削除は十分注意する必要がありますね。

重複を削除する方法もありますが、
「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

素晴らしい。2つのテーブルから結合させて一つのテーブルを作ることが出来ました!
ぶっちゃけ、Pandasの方が簡単っていう・・・(*'▽')



おわりに

.table.mode.outputなどのコマンドを使うとテーブル一覧や、テーブル表示方法、テーブルの出力先をファイルにしたりといったことができますが、その使い方が分からない。なんかエラーが出る。



データベースを使わないシステムは存在しないそうです。Pythonの求人をみると大体「Webアプリ開発」「データ分析」「AI、機械学習」が載っています。全部データベースをゴリゴリ使うでしょう。その割には『Pythonで動かすデータベース入門』のようなタイトルの書籍は非常に少ない。ついでに『Pythonで学ぶ統計学』系も少ない。R言語ExcelSQL単体のものはそこそこある。Pythonは「データ分析」でひっくるめたタイトルで売られている。入門書はもっとあって良さそうな気がします。

しばらくこれで遊べそうです。



【関連】
Androidsqliteを使う

【TermuxとsqliteとVim】全国の郵便番号CSVをsqliteにインポートする(メモ - よちよちpython



以上。