1.はじめに

以前下記のような記事を書きましたが、この記事がLGTM数はさておき私の想定より閲覧数があった為、第2弾として私がSQLite3に関して知ってることを備忘録兼ねて追加で書くことにしました。

動作環境
- OS : Windows10 pro
- Python : 3.8.3// Miniconda 4.9.1
- sqlite3：2.6.0
- jupyter notebook
- DB Browser for SQLite(管理ツール)

2.今回説明に使うDB,テーブル作成

まずは本記事で使用するサンプルDB作成、サンプルテーブルを2個作成するまでの流れです。
前回と少し変えて書いてますが、コードの中に解説入れてますのでご参考ください。

※前回の記事でSQL命令にクオテーションを3つ使っていたが、あれは行をまたぐ命令を書く場合のやり方で基本的には文字列同様にクオテーション1個でいい。本記事では1個で行きます。

2-1.今回説明に使うDB,テーブル作成、確認

import sqlite3
import pandas as pd

dbname1 = ('test2.db')#データベース名.db拡張子で設定

con = sqlite3.connect(dbname1, isolation_level=None)#データベースを作成、自動コミット

cur = con.cursor() #データベースに接続する

# テーブルの作成
"""
今回はカラムに型を定義
PRIMARY KEY：主キー制約をかける(重複したデータを入れることが出来なくなる。今回は同じID番号を登録できなくしている)
NOT NULL：対象カラムにnullを入れることをできなくする
"""
#テーブル1作成
cur.execute("create table if not exists test1(id integer PRIMARY KEY, name text NOT NULL, date text)")
#テーブル2作成
cur.execute("create table if not exists test2(id integer PRIMARY KEY, sex text, height real)")

ここでテーブルがきちんと作成できたかを確認するが、前回記事と調べ方を変える。
本記事ではSELECT命令に関しては基本的にこのread_sql_queryを使用して表示させていく。

#テーブルが作成されたかを確認
"""
jupyterでSQL命令のSELECT命令を確認をする際にはpandasの「read_sql_query」を使う。
本記事では前回の記事では使わなかったこの関数を基本的に活用していく
"""
pd.read_sql_query('SELECT name FROM sqlite_master WHERE TYPE="table"', con)

	name
0	test1
1	test2

2-2.レコード挿入

まずはカラム作成時に制約をかけたNOT NULLの機能を確認してみる

data = [
    (0, None, 19980108), #nameカラムにnullを設定してみる
]
cur.executemany('INSERT INTO test1 VALUES(?,?,?)', data)

実行結果

IntegrityError: NOT NULL constraint failed: test1.name

次にカラム作成時に制約をかけたPRIMARY KEYの機能を確認してみる

data = [
    (0, "Bob", 19980108),
    (0, "Bobby", 19980109), #あえて同じidで登録させようとしてみる
]
cur.executemany('INSERT INTO test1 VALUES(?,?,?)', data)

実行結果

IntegrityError: UNIQUE constraint failed: test1.id

制約の確認はできたので、test1テーブルで使用するその他レコードを挿入する

#test1テーブルへ値挿入
data = [
   (1, "Taro", 19800810),
   (2, "Rena", 19921015),
   (3, "Masa", 20050505),
   (4, "Jiro", 19910510),
   (5, "Satoshi", 19880117)
]
cur.executemany('INSERT INTO test1 VALUES(?,?,?)', data)

pd.read_sql_query('SELECT * FROM test1', con).head(2) #長いので先頭の2行だけ表示

	id	name	date
0	0	Bob	19980108
1	1	Taro	19980810

同様にtest2テーブルで使用するレコードを挿入する
※test2は最後の結合にしか使用しませんので出番は当分先ですが。。

#test2テーブルへ値挿入
data = [
   (0, "Man", 179.5), 
   (1, "Man", 191.1), 
   (2, "Woman", 161.2), 
   (3, "Man", 171.7), 
   (4, "Man", 156.9), 
   (5, "Woman", 188), ]
cur.executemany('INSERT INTO test2 VALUES(?,?,?)', data)

pd.read_sql_query('SELECT * FROM test2', con).head(2)

	id	sex	height
0	0	Man	179.5
1	1	Man	191.1

3.様々なテーブル単体操作

前回の記事で紹介しなかった操作を色々記述していく。
なお、いちいちdf.head()で個別表示するのはシンドイので、ここからは基本的にIPython.displayモジュール(jupyterの表示に特化)を活用していく。

※補足(IPython.display)
jupyter notebookで連続したprint文が使えるくらいに思っていればいいです。
df1.head()
df2.head()
とjupyterでは2連続で表示できないが、これ(display)を使えば連続表示できる。

3-0.LIMIT句で抜粋

LIMITを使用するとレコードの抜粋場所を指定できる

from IPython.display import display

print('LIMIT句：先頭から2レコード抜粋', raw=True)
display(pd.read_sql_query('SELECT * FROM test1 LIMIT 2', con))
print('OFFSET句：先頭を2レコードずらしてから2レコード抜粋', raw=True)
display(pd.read_sql_query('SELECT * FROM test1 LIMIT 2 OFFSET 2', con))

※以下出力結果

LIMIT句：先頭から2レコード抜粋

	id	name	date
0	0	Bob	19980108
1	1	Taro	19980810

OFFSET句：先頭を2レコードずらしてから2レコード抜粋

	id	name	date
0	2	Rena	19921015
1	3	Masa	20050505

3-1.WHEREで条件を付けて検索する

WHEREの後に抽出する条件を入れてクエリーをかけるサンプル。
GLOB句は正規表現的なやつです。

print('WHERE句：id4以上のみ抜粋', raw=True)
display(pd.read_sql_query('SELECT * FROM test1 WHERE id > 3', con))
print('BETWEEN句：idが2～4を抽出', raw=True)
display(pd.read_sql_query('SELECT * FROM test1 WHERE id BETWEEN 2 and 4', con))
print('IN句：nameカラムでMasaなのをピンポイント抽出', raw=True)
display(pd.read_sql_query('SELECT * FROM test1 WHERE name IN("Masa")', con))
print('GLOB句：nameカラムで「Ma」とつくものを抽出', raw=True)
display(pd.read_sql_query('SELECT * FROM test1 WHERE name GLOB("Ma*")', con))

WHERE句：id4以上のみ抜粋

	id	name	date
0	4	Jiro	19910510
1	5	Maki	19880117

BETWEEN句：idが2～4を抽出

	id	name	data
0	2	Rena	19921015
1	3	Masa	20050505
1	4	Jiro	19910510

IN句：nameカラムでMasaなのをピンポイント抽出

	id	name	date
0	3	Masa	20050505

GLOB句：nameカラムで「Ma」とつくものを抽出

	id	name	date
0	3	Masa	20050505
1	5	Maki	19880117

3-2.ORDER BYでソート

昇順(ASC)と降順(DESC)で選択可能

print('idカラム昇順', raw=True)
display(pd.read_sql_query('SELECT * FROM test1 ORDER BY id ASC', con).head(2))
print('idカラム降順', raw=True)
display(pd.read_sql_query('SELECT * FROM test1 ORDER BY id DESC', con).head(2))

idカラム昇順

	id	name	date
0	0	Bob	19980108
1	1	Taro	19980810

idカラム降順

	id	name	date
5	5	Maki	19880117
4	4	Jiro	19910510

3-3.UPDATEで値を更新する

id=0のBobを最初に失敗したBobbyのデータにidはそのままで置き換える。
※名前と生年月日を入れ替える。

#SELECTでないので、カーソルオブジェクト使用。
cur.execute('UPDATE test1 SET name="Bobby", date="19880116" WHERE id=0')
#確認はread_sql_queryで
display(pd.read_sql_query('SELECT * FROM test1', con).head(2))

	id	name	date
0	0	Bobby	19980116
1	1	Taro	19980810

3-4.ALTER TABLEでカラムを追加する

新しく「age」カラムを追加する。
初期値は何も指定しないとNoneになる。(None以外にも指定できるが同じ値しか入れられない)
なお、各箇所に値を入れる方法は私はこれしか知らないが、もっといい方法あったらぜひ教えてください！

#新しくageカラムを追加
print('ageカラム挿入後初期状態', raw=True)
cur.execute('ALTER TABLE test1 ADD age integer')
display(pd.read_sql_query('SELECT * FROM test1', con).head(2))

#追加したageカラムにUPDATEを使用してデータを更新
data = [33, 41, 29, 16, 30, 33]
for i,row in enumerate(data):
    cur.execute('UPDATE test1 SET age = (?) WHERE id=(?)', [row, i])
print('ageカラムに値を代入後', raw=True)
display(pd.read_sql_query('SELECT * FROM test1', con).head(2))

ageカラム挿入後初期状態

	id	name	date	age
0	0	Bobby	19980116	None
1	1	Taro	19980810	None

ageカラムに値を代入後

	id	name	date	age
0	0	Bobby	19980116	33
1	1	Taro	19980810	41

3-5.カラムの削除

これはめんどくさい方法をしないとSQLiteの場合はなぜかできない。
よって今一番簡単だと考えている方法を書いておく。
まずはサンプルカラムを追加する(後で消す)

#新しくage2カラムを追加
cur.execute('ALTER TABLE test1 ADD age2 integer')
pd.read_sql_query('SELECT * FROM test1', con).head(2)

	id	name	date	age	age2
0	0	Bobby	19980116	33	None
1	1	Taro	19980810	41	None

このage2カラムを削除するのに簡単なのは「DB Browser for SQLite」を使用する方法である。
下図のようにテーブルの変更からage2を選んで削除すれば消すことが出来る。

#age2カラムが消えたか確認　※削除作業後
pd.read_sql_query('SELECT * FROM test1', con).head(2)

確かに消えていることがわかる。これが一番簡単だと思います。

	id	name	date	age
0	0	Bobby	19980116	33
1	1	Taro	19980810	41

3-6.GROUPBYでグループ化

いわゆるグループ化をやってみる。
今回は追加したageカラムをグループ分けする。33歳の人が2名いることが注目？

print('GROUPBY句でage別に表示', raw=True)
display(pd.read_sql_query('SELECT age, COUNT(*) AS Count FROM test1 GROUP BY age', con))
print('HAVING句でさらにGROUPBY後にカウント2以上を抜粋', raw=True)
display(pd.read_sql_query('SELECT age, COUNT(*) AS Count FROM test1 GROUP BY age HAVING COUNT(*) = 2', con))

GROUPBY句でage別に表示

	age	Count
0	16	1
1	29	1
2	30	1
3	33	2
4	41	1

HAVING句でさらにGROUPBY後にカウント2以上を抜粋

	age	Count
0	33	2

3-7.JOINでテーブルの結合

テーブル同士の結合を行う。
今回はtest1とtest2テーブルで共通のidを使用して横に結合させる。
結合のやり方はいくつかあるが、今回は2個だけ紹介する。
まずは結合前のテーブルを今一度確認しておく

print('test1テーブルを表示', raw=True)
display(pd.read_sql_query('SELECT * FROM test1', con))
print('test2テーブル', raw=True)
display(pd.read_sql_query('SELECT * FROM test2', con))

test1テーブルを表示

	id	name	date	age
0	0	Bobby	19980116	33
1	1	Taro	19980810	41
2	2	Rena	19921015	29
3	3	Masa	20050505	16
4	4	Jiro	19910510	30
5	5	Maki	19880117	33

test2テーブルを表示

	id	sex	height
0	0	Man	179.5
1	1	Man	191.1
2	2	Woman	161.2
3	3	Man	171.7
4	4	Man	156.9
5	5	Woman	188.0

確認が終わったので結合をしていく。
自然結合の場合、同じカラム名を自動抽出してくれる＋結合後にidがダブってないのでおすすめです。
長いので再び2行だけの表示です

#結合
print('INNER JOIN結合', raw=True)
display(pd.read_sql_query('SELECT * FROM test1 INNER JOIN test2 ON test1.id = test2.id', con).head(2))
print('自然結合', raw=True)
display(pd.read_sql_query('SELECT * FROM test1 NATURAL INNER JOIN test2', con).head(2))

INNER JOIN結合

	id	name	date	age	id	sex	height
0	0	Bobby	19980116	33	0	Man	179.5
1	1	Taro	19980810	41	1	Man	191.1

自然結合

	id	name	date	age	sex	height
0	0	Bobby	19980116	33	Man	179.5
1	1	Taro	19980810	41	Man	191.1

4.Jupyterで直接SQL命令を扱う

今までとは違って、jupyterで直接SQL命令を書く方法も記載しておく。
ただしpip install ipython-sqlでまずはインストールが必要。(jupyterでやるなら先頭に!つける)

# !pip install ipython-sql

%load_ext sql　#jupyterでSQL使えるようにするコマンド
%sql sqlite:///test2.db #.dbのあるpathを指定する。今回はjupyterと同じ階層のケース

これで何も出力されなければDBが読み出せている。
後はマジックコマンド%%sqlに続けてSQL命令を書くときちんと表示されていることがわかる。

#test1テーブルを表示
%%sql
SELECT * FROM test1

sqlite:///test2.db
Done.

id	name	date	age
0	Bobby	19980116	33
1	Taro	19980810	41
2	Rena	19921015	29
3	Masa	20050505	16
4	Jiro	19910510	30
5	Maki	19880117	33

#test1テーブルとtest2テーブルの自然結合を表示
%%sql
SELECT * FROM test1 NATURAL INNER JOIN test2