[python] SQLite3を用いた簡単なDB構築

# 初めに
PythonでのDB構築に関して、簡便に使用できるSQLite3の簡単な使用方法をまとめる。

|対象|環境|
|---|---|
|OS|windows10|
|python|3.7.1|
|anaconda|4.8.5|
|splite|3.31.1|

公式ドキュメント

https://docs.python.org/ja/3/library/sqlite3.html

# SQLite
SQLiteとは、ライブラリを読みだす形式で使用可能なRDBMS。[@wiki](https://ja.wikipedia.org/wiki/SQLite)
一般的なDBサーバ環境を構築する必要がなく、アプリケーションに組み込みことで利用される。
データの保存は、単一のファイルを使用する。
基本的なSQL文を基準とした関数を利用することでデータの登録・呼び出しを行う。

## インストール

自身の環境では、anaconda環境で使用している。
splite3をanacondaへインストールする。

```bash
conda install -c blaze sqlite3
```

https://anaconda.org/blaze/sqlite3

## DataBase(ファイル)の作成

一般的に情報の保管のために、.dbファイルを作成する。
connect関数でファイルパスを引数とすることで、ファイルが存在しない場合は、新規作成する。
ファイルが存在する場合は、接続する。

```python
import sqlite3
DATABASE_URL = './hogehoge.db' # DBファイルの保管パス
con = sqlite3.connect(DATABASE_URL) #　DBへの接続(connection).ファイルが存在しない場合は新規に作成される.
con.commit() # DBの状態を確定
con.close() # 接続解除
```
※以降はslite3.connectとcommit、closeを省略する。

## Tableの作成
DB内にTABLEを作成する。

|a|b|c|
|---|---|---|
||||

```python
table_name = 'test'
table_header = 'a, b, c'
cur.executescript("CREATE TABLE %s(%s);" % (table_name , table_header)) # TABLEの作成
```

## Dataの更新

TABLEにdataを挿入する。

|a|b|c|
|---|---|---|
|1|2|3|
|4|5|6|
|7|8|9|

```python
data = [
    '1,  2,  3',
    '4,  5,  6',
    '7,  8,  9',
]
# dataを登録する
for val in data: 
    cur.execute("INSERT INTO %s VALUES(%s)" % (table_name ,  val))
```

## Dataの呼び出し

TABLEから登録したdataでkey, valueが一致するものを呼び出す。

```python
# dataを呼び出す(keyが'a'で、valueが'1'に一致するもの)
key, value  = 'a', '1'
cur.execute("SELECT * FROM %s where %s = %s" % (table_name , key, value))
info = cur.fetchall() # 検索結果を取得(list)
print(info)
```

## まとめ
簡単にRDBを構築することが可能。
情報は単一のファイルで保管される。
情報のやり取りはライブラリの組み込み関数を使用して簡単に行える。

```python
import sqlite3
DATABASE_URL = './hogehoge.db' # databaseファイルの保管パス
table_name = 'test'
table_header = 'a, b, c'

conn = sqlite3.connect(DATABASE_URL) #　databaseへの接続(connection).ファイルが存在しない場合は新規に作成される.
cur = conn.cursor()
cur.executescript("CREATE TABLE %s(%s);" % (table_name , table_header)) # TABLEの作成

# dataを登録する
for data in ['1,  2,  3', '4,  5,  6', '7,  8,  9', '10, 11, 12']:
    cur.execute("INSERT INTO %s VALUES(%s)" % (table_name ,  data))

# dataを呼び出す(keyが'a'で、valueが'1'に一致するもの)
key, value  = 'a', '1'
cur.execute("SELECT * FROM %s where %s = %s" % (table_name , key, value))
info = cur.fetchall() # 検索結果を取得(list)
print(info)

conn.commit() # databaseの状態を確定
conn.close() # 接続解除
```

# pandasの利用
RDBでのデータ管理は2次元テーブルの思想に類似している。
そのため、管理情報の操作に関して、簡便なものであれば、利用量の多いpandasと相性が良い。

## TABLE登録
既存のDataFrameを登録する場合、to_sqlで実施。

```python
import sqlite3
import pandas as pd
DATABASE_URL = './hogehoge.db'
table_name2 = 'test2'
df = pd.DataFrame({
    'd' : [1, 2, 3],
    'e' : [4, 5, 6]
})

conn = sqlite3.connect(DATABASE_URL)
cur = conn.cursor()

df.to_sql(table_name2, conn, if_exists = 'replace', index = False)
                
conn.commit()
conn.close()
```

## TABLE取得
登録されているTABLEを取得する場合、pandas.read_sql_queryで取得。

```python
import sqlite3
import pandas as pd
DATABASE_URL = './hogehoge.db'
table_name2 = 'test2'

conn = sqlite3.connect(DATABASE_URL)
cur = conn.cursor()

df = pd.read_sql_query('SELECT * FROM %s' % table_name2, conn)
print(df)
                
conn.commit()
conn.close()
```

#最後に
DBサーバやRDBMS、SQLに関する自身の記事を紹介。
初学の方の参考になれば幸い。

https://qiita.com/flcn-x/items/949b13d63a7b4eb9d1e5

DBとストレージ、ファイルサーバーに関する、違いを簡単にまとめたものも紹介。

https://qiita.com/flcn-x/items/3b15fcae62df2e6b6667