【Sqlite3】PythonでSqlite3を操作する

Pythonで気軽に導入できるデータベースとして、Sqlite3があったのでこちらを使用してみようと思い、その備忘録を記載します。

今回は基本的な構文のみ使用しているため、カラムの制約や複雑なクエリなどは紹介していません。ただし、SQLの一連の流れとして

 * データベースと接続
 * テーブルの作成
 * テーブルの名前を変更
 * テーブルのカラムを追加
 * テーブルにレコードを追加
 * テーブルのレコードの値を変更
 * テーブルのレコードを削除
 * テーブルのカラムを削除
 * テーブルの削除

を最終的なまとめとして紹介します。

本記事はPythonの[Sqlite3チュートリアル](https://docs.python.org/ja/3/library/sqlite3.html#)と[Sqliteホームページ](https://www.sqlite.org/index.html)を参考にしています。

## 1. Databaseの作成

PythonでSqlite3を使用するにあたり、`sqlite3`モジュールを使用します。ただし、Pythonの標準モジュールのため特にインストールの必要はありません。

データベースと接続するには、`sqlite3#connect`メソッドを使用します。このとき、引数でデータベース`*.db`を指定しますが、データベースが存在しない場合は暗黙的に作成されます。

```python:データベース接続
import sqlite3

con = sqlite3.connect("Examples.db")
```

さて、データベースに対して何か処理を実行するためにはデータベースカーソルを使用する必要があります。そのため、以下のようにカーソルオブジェクトを生成します。

```python
cur = con.cursor()
```

今回の記事では次のメソッドを用いて、SQLを実行します：
 * `Cursor#execute()`
 * `Cursor#executemany()`

それぞれ1回、複数回クエリを実行することができます。

また、本記事ではあまり着目していませんが、レコードの登録といったテーブルへの変更が行われた際にその変更を確定させる`connect#commit()`メソッド：
```python
# INSERT文実行
con.commit()
```

やデータベースの接続に対して、データベース接続を終了する`connect#close()`メソッド：
```python
con = connect("MyDatabase.db")
# Any Process
con.close()
```
なども紹介すべき点です。（記事のコード内には記載していませんが...）

## 2. テーブルの作成

テーブルを作成するSQLは次の通り：
```sql
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
```

今回作成するカラムのデータタイプとPythonのデータタイプの対応関係は次のテーブルの通り：

| Python | Sqlite  |
|--------|---------|
| None   | NULL    |
| int    | INTEGER |
| float  | REAL    |
| str    | TEXT    |
| bytes  | BLOB    |

さて、テーブルがデータベース内で作成されたことを`sqlite_master`内にテーブル名`example`が格納されたことから確認します：

```python: テーブルの確認
import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
SELECT_NAME_FROM_MASTER = "SELECT name FROM sqlite_master;"

# クエリ実行
cur.execute(CREATE_TABLE)
res = cur.execute(SELECT_NAME_FROM_MASTER)
print(res.fetchone())
```

ここで、クエリの実行結果を取得する際に
 
 * `Cursor#fetchone()`
 * `Cursor#fetchAll()`

を使用します。それぞれ１つ、複数の結果を取得します。また、しれっと`IF NOT EXISTS`を含めているので、テーブル`example`がすでにデータベースに存在する場合は、テーブルが作成されません。なお戻り値はタプル（タプルのリスト）であることに注意してください。

上記クエリを実行すると、ターミナル上に

```
('example',)
```

と出力されます。このことからデータベースにテーブル`example`が作成されたことが確認できました。なお、テーブルが存在しない場合は`None`が返されます。

## 3. テーブルにレコードを追加

テーブル`example`にレコードを追加するクエリは次の通り：
```sql
INSERT_RECORDS = "INSERT INTO example VALUES (?, ?, ?, ?, ?);"
```

テーブル`example`のカラム数は5つなので、値は5つ格納できるように定義しています。ここで、`?`はパラメータのバインドのためプレースホルダーです。こちらは[SQLインジェクション]()の対策のために推奨されている方法です。SQLインジェクションについては詳しく説明しませんが、例えば次のようなコードに注意が必要です：

```python: SQLインジェクションの例
symbol = input()
sql = f"SELECT * FROM stocks WHERE symbol = '{symbol}'"
print(sql)
cur.execute(sql)
```

このコードで`symbol`に

```python
' OR TRUE; --
```

と入力すると、実行されるSELECT文は

```sql
SELECT * FROM stocks WHERE symbol = '' OR TRUE; --'
```

となります。WHERE句は常に`TRUE`となるのでテーブルのレコードが取得できてしまいます。このようなSELECT文がパスワードのような誰でも取得されてはいけない情報について使用されていたらまずいですよね。ざっくりこんな感じの問題です。

さて、プレースホルダー`?`の話に戻ると、パラメータは

```python
cur.execute('SQL includes placeholder', 'Parameter: tuple')
```

のように第2引数に与えます。このとき、パラメータがタプルであることに注意してください。（リストでもOK）

以上のことから、テーブルにレコードを追加するコードは次のようになります：

```python:テーブルにレコード追加
import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
INSERT_RECORDS = "INSERT INTO example VALUES (?, ?, ?, ?, ?);"
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"
SELECT_TYPE_OF_COLUMNS = "SELECT typeof(t1), typeof(t2),typeof(t3),typeof(t4),typeof(t5) FROM example;"

# パラメータ
INSERT_DATAS = [
    ('500.0', '500.0', '500.0', '500.0', '500.0'), # str
    (500.0, 500.0, 500.0, 500.0, 500.0), # float
    (500, 500, 500, 500, 500), # int
    (b'0500', b'0500', b'0500', b'0500', b'0500'), # bytes
    (None, None, None, None, None), # None
]

# クエリ実行
cur.execute(CREATE_TABLE)
cur.executemany(INSERT_RECORDS, INSERT_DATAS)
res_type = cur.execute(SELECT_TYPE_OF_COLUMNS)
for type in res_type:
    print(type)
res_value = cur.execute(SELECT_VALUE_OF_COLUMNS)
for value in res_value:
    print(value)
```

このコードを実行した結果はターミナルに以下のように表示されます。

```python: SELECT_TYPE_OF_COLUMNS
('text', 'integer', 'integer', 'real', 'text')
('text', 'integer', 'integer', 'real', 'real')
('text', 'integer', 'integer', 'real', 'integer')
('blob', 'blob', 'blob', 'blob', 'blob')
('null', 'null', 'null', 'null', 'null')
```
```python: SELECT_VALUE_OF_COLUMNS
('500.0', 500, 500, 500.0, '500.0')
('500.0', 500, 500, 500.0, 500.0)
('500', 500, 500, 500.0, 500)
(b'0500', b'0500', b'0500', b'0500', b'0500')
(None, None, None, None, None)
```

ここでもう一度、データタイプ対応表です。

| Python | Sqlite  |
|--------|---------|
| None   | NULL    |
| int    | INTEGER |
| float  | REAL    |
| str    | TEXT    |
| bytes  | BLOB    |

テーブル`example`のデータ型は

```sql
(t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB)
```

だったことを思い出しますと、データタイプはデータに応じてその型を変えています。わかりやすいのが、`Bytes`データと`None`データですね。それぞれ`BLOB`と`NULL`となっていることがわかります。

このようにSqliteではデータ型がざっくりとしているわけですが、これは[Sqliteの柔軟な型付け(flexible typing)機能](https://www.sqlite.org/flextypegood.html)で説明があるので参照してみてください。

以上までにレコードの追加ができました。

## 4. テーブルのレコードの値を変更

テーブル`example`のレコードの値を変更するためのクエリは次の通り：
```sql
UPDATE_RECORDS_BY_COLUMN_VALUE = "UPDATE example SET t1 = ? WHERE t2 = ?;"
```

上記のクエリはテーブルのカラム`t2`の値によって`t1`の値を変更します。例えば、現在のレコードには`500`という値が含まれていますので`t2=500`のときに、`t1='Changed'`と変更することができます。この例をコードに直すと、次の通り：

```python:レコードの値を更新
import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
UPDATE_RECORDS_BY_COLUMN_VALUE = "UPDATE example SET t1 = ? WHERE t2 = ?;"
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"

# クエリ実行
cur.execute(UPDATE_RECORDS_BY_COLUMN_VALUE, ("Changed", 500, ))
res_value = cur.execute(SELECT_VALUE_OF_COLUMNS)
for value in res_value:
    print(value)
```

上記コードを実行するとターミナルには以下のように表示されます：
```python: SELECT_VALUE_OF_COLUMNS
('Changed', 500, 500, 500.0, '500.0')
('Changed', 500, 500, 500.0, 500.0)
('Changed', 500, 500, 500.0, 500)
(b'0500', b'0500', b'0500', b'0500', b'0500')
(None, None, None, None, None)
```

`t2`の値が`500`となっているレコードが3つあるので、対応するレコードの`t1`の値が`'Changed'`という値に変更されていることがわかります。

## 5. テーブルのレコードを削除

そろそろテーブル`example`の値が変更されている、またはINSERT文を何度も使用しているとレコード数が増えすぎてしまっている状態かと思います。
なので、レコードを消去していきます。レコードをすべて削除するクエリは次の通り：

```sql
DELETE_ALL_RECORDS = "DELETE FROM example;"
```

上記クエリを実行するコードが

```python:
import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"
DELETE_ALL_RECORDS = "DELETE FROM example;"

# クエリ実行
cur.execute(DELETE_ALL_RECORDS)
res_value = cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall()

if not res_value:
    print("Records Deleted")
else:
    print("Records Exist")
```

となります。正常にレコードが削除された場合`res_value`は空のリストとなるはずですので、ターミナルは次のように表示されているはずです：

```python:リストが空かどうか
Records Deleted
```

次に、全レコードの削除ではなく、あるカラムの値によって削除するクエリを試します：
```sql
DELETE_RECORD_BY_COLUMN_VALUE = "DELETE FROM example WHERE t3 = ?;"
```

上記のクエリは`t3`の値とパラメータが一致しているレコードを削除します。今回は`t3`の値として`b'0500'`とした場合で考えます。コードは次の通り：
```python
import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
INSERT_RECORDS = "INSERT INTO example VALUES (?, ?, ?, ?, ?);"
DELETE_RECORD_BY_COLUMN_VALUE = "DELETE FROM example WHERE t3 = ?;"
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"

# パラメータ
INSERT_DATAS = [
    ('500.0', '500.0', '500.0', '500.0', '500.0'), # str
    (500.0, 500.0, 500.0, 500.0, 500.0), # float
    (500, 500, 500, 500, 500), # int
    (b'0500', b'0500', b'0500', b'0500', b'0500'), # bytes
    (None,None,None,None,None), # None
]

# クエリ実行
cur.executemany(INSERT_RECORDS, INSERT_DATAS)
cur.execute(DELETE_RECORD_BY_COLUMN_VALUE, (b'0500', ))

res_value = cur.execute(SELECT_VALUE_OF_COLUMNS)
for value in res_value:
    print(value)
```

上記コードを実行すると、4つ目のレコードが削除されて、
```python:SELECT_VALUE_OF_COLUMNS
('500.0', 500, 500, 500.0, '500.0')
('500.0', 500, 500, 500.0, 500.0)
('500', 500, 500, 500.0, 500)
(None, None, None, None, None)
```

となります。

## 6. テーブルの名前を変更

今度はテーブルの名前を変更してみます。テーブル名の変更を行うクエリは次の通り：
```sql
ALTER_TABLE_RENAME = "ALTER TABLE example RENAME TO renamed;"
```

上記クエリではテーブル`example`の名前を変更します。クエリを試すコードは次の通り：

```python
import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
ALTER_TABLE_RENAME = "ALTER TABLE example RENAME TO renamed;"
SELECT_MASTER_TABLE = "SELECT name FROM sqlite_master;"
SELECT_MASTER_TABLE_EXIST = "SELECT name FROM sqlite_master WHERE TYPE = 'table' AND name = ?;"

# クエリ実行
res = cur.execute(SELECT_MASTER_TABLE_EXIST, ("renamed", )).fetchone()
if res is None:
    cur.execute(ALTER_TABLE_RENAME)

cur.execute(CREATE_TABLE)
for table in cur.execute(SELECT_MASTER_TABLE).fetchall():
    print(table)
```

ここで、`ALTER TABLE`の構文の中に`IF NOT EXISTS`にあたる構文がなかったので、[こちら](https://lightgauge.net/database/sqlite/8446/)の記事を参考にしてIF文で代用しました。
上記コードを実行すると、テーブル`example`をテーブル`renamed`に変更した後に、`example`を新たに生成しているので、ターミナルは次のように表示されているはずです：

```python
('renamed',)
('example',)
```

## 7. テーブルにカラムを追加

現在のテーブル`example`は5カラムが定義されています：
```
(t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB)
```

このテーブルに新たに
```
t6 TEXT
```
を追加していきます。そのクエリが次の通り：

```sql
ALTER_TABLE_ADD_COLUMN = "ALTER TABLE example ADD COLUMN t6 TEXT;"
```

こちらを使用したコードは次の通り：
```python
import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
ALTER_TABLE_ADD_COLUMN = "ALTER TABLE example ADD COLUMN t6 TEXT;"
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
INSERT_RECORDS = "INSERT INTO example VALUES (?, ?, ?, ?, ?);"
INSERT_RECORDS_ADD = "INSERT INTO example VALUES (?, ?, ?, ?, ?, ?);"
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"

# パラメータ
INSERT_DATAS = [
    ('500.0', '500.0', '500.0', '500.0', '500.0'),
    (500.0, 500.0, 500.0, 500.0, 500.0),
    (500, 500, 500, 500, 500),
    (b'0500', b'0500', b'0500', b'0500', b'0500'),
    (None,None,None,None,None),
]

INSERT_DATAS_ADD = [
    ('500.0', '500.0', '500.0', '500.0', '500.0', '1000.0'),
    (500.0, 500.0, 500.0, 500.0, 500.0, 1000.0),
    (None,None,None,None,None,None),
]

# クエリ実行
cur.execute(CREATE_TABLE)
cur.executemany(INSERT_RECORDS, INSERT_DATAS)
for before in cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall():
    print("Before: ")
    print(before)

cur.execute(ALTER_TABLE_ADD_COLUMN)
cur.executemany(INSERT_RECORDS_ADD, INSERT_DATAS_ADD)
for after in cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall():
    print("After: ")
    print(after)
```

上記コードを実行するとターミナルは次のようになるはずです：
```python
Before: 
('500.0', 500, 500, 500.0, '500.0')
('500.0', 500, 500, 500.0, 500.0)
('500', 500, 500, 500.0, 500)
(b'0500', b'0500', b'0500', b'0500', b'0500')
(None, None, None, None, None)
After:
('500.0', 500, 500, 500.0, '500.0', None)
('500.0', 500, 500, 500.0, 500.0, None)
('500', 500, 500, 500.0, 500, None)
(b'0500', b'0500', b'0500', b'0500', b'0500', None)
(None, None, None, None, None, None)
('500.0', 500, 500, 500.0, '500.0', '1000.0')
('500.0', 500, 500, 500.0, 500.0, '1000.0')
(None, None, None, None, None, None)
```

カラム`t6`を追加前のカラム数が5つであるのに対して、追加後には6つのカラムが表示されていることがわかります。なお、カラム追加前に挿入したレコードの6カラム名は`None`となっています。

## 8. テーブルのカラムを削除

カラムを削除するためには、次のクエリを使用します：
```sql
ALTER_TABLE_DROP_COLUMN = "ALTER TABLE example DROP COLUMN t6"
```

先ほどのコードの最後の追加して実行することを考えてみます。

```python
import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
ALTER_TABLE_ADD_COLUMN = "ALTER TABLE example ADD COLUMN t6 TEXT;"
ALTER_TABLE_DROP_COLUMN = "ALTER TABLE example DROP COLUMN t6"
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
INSERT_RECORDS = "INSERT INTO example VALUES (?, ?, ?, ?, ?);"
INSERT_RECORDS_ADD = "INSERT INTO example VALUES (?, ?, ?, ?, ?, ?);"
SELECT_VALUE_OF_COLUMNS = "SELECT * FROM example;"

# パラメータ
INSERT_DATAS = [
    ('500.0', '500.0', '500.0', '500.0', '500.0'),
    (500.0, 500.0, 500.0, 500.0, 500.0),
    (500, 500, 500, 500, 500),
    (b'0500', b'0500', b'0500', b'0500', b'0500'),
    (None,None,None,None,None),
]

INSERT_DATAS_ADD = [
    ('500.0', '500.0', '500.0', '500.0', '500.0', '1000.0'),
    (500.0, 500.0, 500.0, 500.0, 500.0, 1000.0),
    (None,None,None,None,None,None),
]

# クエリ実行
cur.execute(CREATE_TABLE)
cur.executemany(INSERT_RECORDS, INSERT_DATAS)
for before in cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall():
    print("Before: ")
    print(before)

cur.execute(ALTER_TABLE_ADD_COLUMN)
cur.executemany(INSERT_RECORDS_ADD, INSERT_DATAS_ADD)
for after in cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall():
    print("After: ")
    print(after)

print("Column6 Deleted: ")
cur.execute(ALTER_TABLE_DROP_COLUMN)
for i in cur.execute(SELECT_VALUE_OF_COLUMNS).fetchall():
    print(i)
```

こちらを実行するとターミナルは先ほどの結果の後に
```python
Column6 Deleted:
('500.0', 500, 500, 500.0, '500.0')
('500.0', 500, 500, 500.0, 500.0)
('500', 500, 500, 500.0, 500)
(b'0500', b'0500', b'0500', b'0500', b'0500')
(None, None, None, None, None)
('500.0', 500, 500, 500.0, '500.0')
('500.0', 500, 500, 500.0, 500.0)
(None, None, None, None, None)
```

が表示されているはずです。

## 9. テーブルの削除

最後に、テーブルを削除するクエリは次の通り：
```sql
DROP_TABLE = "DROP TABLE IF EXISTS example;"
```

テーブル`example`が存在したら削除するというクエリです。これを実行し、確認するためのコードは次の通り：

```python
import sqlite3

con = sqlite3.connect("Examples.db")
cur = con.cursor()

# クエリ
CREATE_TABLE = "CREATE TABLE IF NOT EXISTS example (t1 TEXT, t2 NUMERIC, t3 INTEGER, t4 REAL, t5 BLOB);"
DROP_TABLE = "DROP TABLE IF EXISTS example;"
SELECT_MASTER_TABLE = "SELECT name FROM sqlite_master;"

cur.execute(CREATE_TABLE)
res_before = cur.execute(SELECT_MASTER_TABLE).fetchone()
print(res_before)
cur.execute(DROP_TABLE)
res_after = cur.execute(SELECT_MASTER_TABLE).fetchone()
print(res_after)
```

上記コードを実行すると、ターミナルは次のようになるはずです：
```python
('example',)
None
```

※テーブル`renamed`を放置したままの場合は、`res_before`と`res_after`の取得メソッドを`fetchall()`に変えて出力するとよいかと思います。（消し忘れていて初めはこちらを試しました。）

## まとめ
今回の記事はSqlite3をPythonで実行するというものでした。内容は基本的な構文ばかりですが、少し長くなったので一律ですべてのクエリを実行するのは次回の記事に回します。
今後は今回省略したカラムの制約やSqlite3で使用可能な関数などをまとめてみたいと思います。