pythonでSQLite3を使う手順のメモ


pythonの標準ライブラリであるsqlite3を使ってSQLite3のDBを使う手順のメモです
https://docs.python.org/ja/3/library/sqlite3.html


# 1. install


標準ライブラリなのでインストールしなくても使えます


# 2. DBをつくる


sqlite3.connect()すると指定したPATHにあるSQLite3 DBファイルに接続します。
SQLite3 DBファイルがない場合は自動で作ってくれます。


```python
import sqlite3
conn = sqlite3.connect('./TEST.db')
conn.close()
```


PATHを':memory:'にするとメモリ上にDBを作ってclose()した時点で消えるように出来ます。操作方法の確認をやりたいときに便利です。


```python
conn = sqlite3.connect(':memory:')
conn.close()
```


# 3. table


## 3-1. tableを追加する


カーソルを作ってexecute()内にSQL文を渡すことで実行内容を指示できます。一通り指示してからconnectをcommit()すると変更がSQLite3 DBファイルに適用されます。下の場合、整数型のid列と文字列型のname列をもつpersonsという名前のtableを作成します。


```python
import sqlite3
conn = sqlite3.connect('./TEST.db')

cur = conn.cursor()  # カーソルを作成
cur.execute('CREATE TABLE persons(id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING)')  # tableを作成する指示

conn.commit()  # commit()した時点でDBファイルが更新されます
conn.close()
```


tableが既にあるとエラーになりますが、IF NOT EXISTSを付けておくと存在しない場合に限り追加するようになります


```python
cur.execute('CREATE TABLE IF NOT EXISTS persons(id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING)')
```


## 3-2. table一覧を取得する


table一覧を取得するにはsqlite_masterを読み込みます。読み込みはexecute()でSELECTした後にfetchall()することで実行され、listで受け取ることが出来ます。


```python
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")
cur.execute("CREATE TABLE fuga (fuga1, fuga2, fuga3)")

cur.execute("SELECT name from sqlite_master where type='table';")
print('table一覧: ', cur.fetchall())

con.close()
```


## 3-3. tableをrenameする


ALTER TABLEで既存のtableについて変更出来ますので、table名を変更したい場合は以下のように書けます。


```python
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")
cur.execute("CREATE TABLE fuga (fuga1, fuga2, fuga3)")

cur.execute("ALTER TABLE hoge rename to hogehoge")
cur.execute("ALTER TABLE fuga rename to fugafuga")

cur.execute("SELECT name from sqlite_master where type='table';")
print('table一覧: ', cur.fetchall())

con.close()
```


## 3-4. tableを削除する


tableの削除はDROP TABLEです


```python
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")
cur.execute("CREATE TABLE fuga (fuga1, fuga2, fuga3)")

cur.execute('DROP TABLE hoge')

cur.execute("SELECT name from sqlite_master where type='table';")
print('table一覧: ', cur.fetchall())

con.close()
```


# 4. column


## 4-1. column一覧を取得する


PRAGMA TABLE_INFO(table名)でcolumn情報をlistで受け取れます


```python
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1 STRING, hoge2 STRING)")

cur.execute("PRAGMA TABLE_INFO(hoge)")
print(cur.fetchall())

con.close()
```


cid, name, type, notnull, dflt_valueの順で出力されます


```python:出力
[(0, 'hoge1', 'STRING', 0, None, 0), (1, 'hoge2', 'STRING', 0, None, 0)]
```


## 4-2. columnを追加する


tableのrenameで使ったALTER TABLEコマンドでcolumnを追加することが出来ます


```python
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")

cur.execute("ALTER TABLE hoge ADD COLUMN hoge_added")

cur.execute("PRAGMA TABLE_INFO(hoge)")
print(cur.fetchall())

con.close()
```


## 4-3. columnをrenameする


SQLite3ではcolumnのrenameが出来ないので、新しくtableを作って移し替えることになります。基本的にcolumn名の変更はやらないという思想だと思います。


```python
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")

cur.execute("ALTER TABLE hoge RENAME TO hoge_tmp")
cur.execute("CREATE TABLE hoge (hogehoge1, hogehoge2)")
cur.execute("INSERT INTO hoge (hogehoge1, hogehoge2) SELECT hoge1, hoge2 FROM hoge_tmp")
cur.execute("DROP TABLE hoge_tmp")

cur.execute("PRAGMA TABLE_INFO(hoge)")
print(cur.fetchall())

con.close()
```


## 4-4. columnを削除する


削除も出来ませんので新しくtableを作って移し替えることになります


```python
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE hoge (hoge1, hoge2)")

cur.execute("ALTER TABLE hoge RENAME TO hoge_tmp")
cur.execute("CREATE TABLE hoge (hogehoge2)")
cur.execute("INSERT INTO hoge (hogehoge2) SELECT hoge2 FROM hoge_tmp")
cur.execute("DROP TABLE hoge_tmp")

cur.execute("PRAGMA TABLE_INFO(hoge)")
print(cur.fetchall())

con.close()
```


# 5. recordを書き込む


INSERTコマンドで書き込むことが出来ます。columnが存在しても書き込まれなかった場合はdflt_valueで指定された値が入ります。また、AUTOINCREMENTなcolumnを省略して書きこむと、自動的に+1した値を入れてくれます(指定することも出来ます)。


```python
conn = sqlite3.connect(dbname)
conn = sqlite3.connect(":memory:")

cur = conn.cursor()

cur.execute('CREATE TABLE hoge(id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING, age INTEGER)')
cur.execute('INSERT INTO hoge(name, age) values("Taro", 12)')
cur.execute('INSERT INTO hoge(name) values("Hanako")')
cur.execute('INSERT INTO hoge(id, name, age) values(20, "Akiko", 2)')
cur.execute('INSERT INTO hoge(name, age) values("Jiro", 10)')

conn.commit()

cur.execute('SELECT * FROM hoge')  # hogeという名前のtableからすべて読んでくる
print(cur.fetchall())  # 上記で指定した範囲のrecordを取得する

cur.close()
conn.close()
```


```python:出力
[(1, 'Taro', 12), (2, 'Hanako', None), (20, 'Akiko', 2), (21, 'Jiro', 10)]
```


## placeholderを使う


ユーザーから受け取った値など外部から来た値を登録する場合などは、そのままINSERTコマンドに渡してしまうとSQLインジェクション攻撃される可能性が出てきます。


```python:SQLインジェクション攻撃に無防備な書き方
cur.execute('INSERT INTO hoge(name, age) values({}, {})'.format(name_input, age_input))
```


placeholderにする事でこれを回避することが出来ます。placeholderとして受け取った値にコマンドが書いてあっても文字列として扱ってくれますのでSQLインジェクションが働かなくなります。以下のように挿入したい場所に?を書いておいて、execute()の第2引数にtupleでplaceholderの値を渡します。


```python:推奨される書き方
cur.execute('INSERT INTO hoge(name, age) values(?, ?)', (name_input, age_input))
```


コマンド、table名、column名はplaceholderに出来ませんがこれはコマンドやtableを外部からの指示で実行するような書き方自体が非推奨という事だと思います。


# 6. 読み込む


SELECTコマンドで選択してfetchall()すると読んできてくれます。以下ではhogeという名前のtableの内容すべてを読んでくるように指定しています。


```python
cur.execute('SELECT * FROM hoge')
print(cur.fetchall())
```


fetchall()でなくfetchone()すればrecordを1つだけ、fetchmany()すれば指定した数のrecordを読んでくれます。


```python:1つだけ読む
print(cur.fetchall())
```


```python:5つ読む
print(cur.fetchmany(5))
```


## 指定のcolumnだけ読み込む


SELECT * ではすべてのcolumnを読み込みますが、読み込むcolumnを指定することも出来ます。以下ではid列とname列のみ読み込みます。


```python
cur.execute('SELECT id, name FROM hoge')
```


```python:出力
[(1, 'Taro'), (2, 'Hanako'), (20, 'Akiko'), (21, 'Jiro')]
```


## 条件に一致したものだけ読み込む


SELECTコマンドのオプションにWHEREをつけると指定した条件に一致したものだけ読み込むように出来ます


```python
conn = sqlite3.connect(dbname)
conn = sqlite3.connect(":memory:")

cur = conn.cursor()

cur.execute('CREATE TABLE hoge(id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING, age INTEGER)')
cur.execute('INSERT INTO hoge(name, age) values("Taro", 12)')
cur.execute('INSERT INTO hoge(name) values("Hanako")')
cur.execute('INSERT INTO hoge(id, name, age) values(20, "Akiko", 2)')
cur.execute('INSERT INTO hoge(name, age) values("Jiro", 10)')

conn.commit()

cur.execute('SELECT * FROM hoge WHERE age <= 12')
print(cur.fetchall())

cur.execute('SELECT * FROM hoge WHERE name = "Hanako"')
print(cur.fetchall())

cur.execute('SELECT * FROM hoge WHERE name <> "Jiro"')
print(cur.fetchall())

cur.close()
conn.close()
```


```python:出力
[(1, 'Taro', 12), (20, 'Akiko', 2), (21, 'Jiro', 10)]
[(2, 'Hanako', None)]
[(1, 'Taro', 12), (2, 'Hanako', None), (20, 'Akiko', 2)]
```


# 7. pandas.DataFrameで読み書き


pandas.DataFrameから読み書きすることも出来ます


```python
import sqlite3
import numpy as np
import pandas as pd

df = pd.DataFrame({'a': np.arange(5),
                   'b': np.arange(5) + 1,
                   'c': np.arange(5) + 2})
display(df)

conn = sqlite3.connect(':memory:')
cur = conn.cursor()

df.to_sql('sample', conn, if_exists='replace')

cur.execute('SELECT * FROM sample')
print(cur.fetchall())

df2 = pd.read_sql('SELECT * FROM sample', conn).set_index('index')
display(df2)

cur.close()
conn.close()
```


<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/277233/f5ee9e92-ee20-33e4-e9c7-27a1e41f60b2.png" width=70%>


# まとめ


pandas.DataFrameで読み書き出来る機能が感動的に使いやすいです
レッツトライ