【SQLite + Python】個人的カンニングペーパー

主にSQLite自体の覚え書きとPythonからSQLiteを操作する時の覚え書き。
個人的によく使うもののみ。

前提

一応の前提として、これを書いている時の環境をまとめとく。

環境	ver
OS	macOS Mojave 10.14.3
SQLite	3.24.0
Python	3.7.0

SQLite

SQLiteそのもののカンペ。
SQLite自体の仕様とか、コマンドとか、独自のSQL構文とか。

記憶クラス

データ型のようなもの。
SQLiteにはカラム型だけでなく、データ自身の種類を表す概念がある。

記憶クラス	フォーマット表記例	説明
NULL	NULL	NULL値。
INTEGER	10	符号付き整数。
REAL	3.14	浮動小数点数。
TEXT	'abcdefg'	テキスト。
BLOB	※入力そのまま	バイナリ。

カラム型

記憶クラスと異なり、こちらはカラム型。

カラム型	対応する記憶クラス	説明
TEXT	NULL TEXT BLOB	テキスト。 数値データを入れようとするとテキストに変換される。
NUMERIC	すべて	すべてのデータ型が入る。CREATE TABLE時に指定しなかった時のデフォルト。 ただし、テキストデータを代入しようとした時、INTEGERかREALへの変換が行われ、失敗した場合にテキストデータと代入されるという動きなので注意。
INTEGER	すべて	整数。 小数点を含む値を格納しようとした場合は整数化される。 CREATE TABLE時に<カラム名> INTEGER PRIMIARY KEY AUTOINCREMENTとカラム型を指定することで自動採番列となる（※）。
REAL	すべて	浮動小数点数。 整数を含む値を格納しようとした場合は浮動小数点数化される。
NONE	すべて	すべてのデータ型が入る。 ただし、NUMERIC等とは違い、変換などは入らない。実質BLOB（？）に該当？

※AUTOINCREMENTなしでも自動採番は動くが、ユニークキーとして使うなら入れておいた方が良い。入れる/入れないの動作の違いは→https://www.dbonline.jp/sqlite/table/index9.html

SQLiteには記憶クラスにもカラム型にも日付型や日時型が存在しない点には注意。

主要なコマンド

普通にターミナルでsqlite3あるいはsqlite3 <接続するDBファイル名>と実行すれば、sqliteのCLIが起動して手動でクエリとかを入力できる。それ以外に.からはじまるコマンドで下記みたいなことができる。

コマンド	内容
.exit	sqliteを終了
.table	テーブル名の一覧表示
.backup <ファイル名>	<ファイル名>でバックアップファイルを生成する。
.restore <ファイル名>	<ファイル名>で指定したバックアップファイルを元にリストアを行う。

詳しく知りたい場合は下記参考
SQLiteコマンドの一覧 - SQLiteコマンドの使い方 - SQLite入門

.sqliterc

~/.sqlitercを作成しておくと、sqlite3コマンド実行時に.sqlitercのコマンドが実行される。
下記は個人的.sqliterc。

/* SELECT時にカラム名の表示をON */
.header on

/* SELECT時にカラム毎に左揃えで出力 */
.mode column

Pythonからの利用

Pythonからsqlite3モジュールを利用して、DBファイルtest.dbに接続し、テーブルname_listを対象にクエリ操作を行うものとする。
テーブルの内容は下記。

id          name      
----------  ----------
1           tanaka    
2           katou     
3           yoshida   

SELECT

import sqlite3

# SELECT
conn = sqlite3.connect("test.db")

query = "SELECT * FROM name_list"

# 1行ずつカーソルをループ
cur = conn.cursor()
cur.execute(query)
for row in cur:
    print(str(row[0]) + ", " + str(row[1]))
        #=> 1, tanaka
        #=> 2, katou
        #=> 3. yoshida
cur.close()

# 全行を取得（list）
cur = conn.cursor()
cur.execute(query)
lis = cur.fetchall()
print(lis)
    #=> [(1, 'tanaka'), (2, 'katou'), (3, 'yoshida')]

# カラム名で取得（Row）とクエリパラメータ指定
query = "SELECT * FROM name_list WHERE name = ?"
conn.row_factory = sqlite3.Row
cur = conn.cursor()
cur.execute(query, ('yoshida', ))
for row in cur:
    print("id:{0}, name:{1}".format(row['id'], row['name']))
        #=> id:3, name:yoshida
cur.close()

# 後始末
conn.close()

INSERT とか

INSERTとかUPDATEとか。
基本的にはSELCT同様にクエリを投げるのみ。あとはcommitを忘れないこととか。

import sqlite3

# INSERT とか
conn = sqlite3.connect("test.db")
cur = conn.cursor()

# プレースホルダによるクエリ形成
cur.execute("INSERT INTO name_list (id, name) VALUES (?, ?)", (4, 'ookubo'))
cur.execute("INSERT INTO name_list (id, name) VALUES (:id, :name)", {'id': 5, 'name': 'kitamura'})

# 複数レコードの挿入
params = [
    (6, 'hashimoto'),
    (7, 'saitou')
]
cur.executemany("INSERT INTO name_list (id, name) VALUES (?, ?)", params)

params = [
    {'id': 8, 'name': 'urashima'},
    {'id': 9, 'name': 'kujirai'}
]
cur.executemany("INSERT INTO name_list (id, name) VALUES (:id, :name)", params)

# SELECT（確認用）
cur.execute("SELECT * FROM name_list")
for row in cur:
    print(row)
        #=> (1, 'tanaka')
        #=> (2, 'katou')
        #=> (3, 'yoshida')
        #=> (4, 'ookubo')
        #=> (5, 'kitamura')
        #=> (6, 'hashimoto')
        #=> (7, 'saitou')
        #=> (8, 'urashima')
        #=> (9, 'kujirai')

# COMMIT
conn.commit()

# 後始末
conn.close()

余談

とりあえずこんなところかな？
また何か忘れやすいこととかあったら追記していくかも。

以前の職場で解析したデータをMDBファイル（MSOfficeのAccess用ファイル）に蓄積したりした経験があったので、今ではこうやってSQLiteを利用してお手軽にデータを蓄積させて再利用したりすることが多い。
なにかスクリプトを動かして結果をCSVとかで吐くのも良いけど、SQLiteに入れておくのもなかなかお手軽で良いと思う。
他の言語とか、他のOSとかからも操作しやすいし。あとは、データの入出力を楽にするラッパーメソッドを作ってしまえば、テキストベースの設定ファイルよりも扱いやすかったりするし。

もっと大量のデータを扱う場合は普通にPostgresqlみたいなDBMSを建てたり、pandasを使ったりするだろう。

SQLite入門 第2版