python3でsqlite3の操作。作成や読み出しなどの基礎。

この記事にはpython3でsqlite3を操作して、データベースの作成や、編集の基礎的なことをまとめてます。家計簿や収入、株式投資のためにデータベースを利用していきたい。
本当に基礎的なことなので、この辺りを理解すれば、やりたいことに必要なことがクリアになると思います。

- DBを作成する
- tableを作成する
- tableへデータのINSERT
- tableの内容を確認する (terminal)
- tableの内容を確認する (python)
- csvからtableを作成する (pandas利用)
- DBをpandasで読み出す
- DBをメモリ上に作成する

## DBを作成する
カレントディレクトリにTEST.dbというデータベースを作成する。

```python:create_db.py
import sqlite3

# TEST.dbを作成する
# すでに存在していれば、それにアスセスする。
dbname = 'TEST.db'
conn = sqlite3.connect(dbname)

# データベースへのコネクションを閉じる。(必須)
conn.close()
```









## tableを作成する
```python:create_table.py
import sqlite3

dbname = 'TEST.db'
conn = sqlite3.connect(dbname)
# sqliteを操作するカーソルオブジェクトを作成
cur = conn.cursor()

# personsというtableを作成してみる
# 大文字部はSQL文。小文字でも問題ない。
cur.execute(
    'CREATE TABLE persons(id INTEGER PRIMARY KEY AUTOINCREMENT,
     name STRING)')

# データベースへコミット。これで変更が反映される。
conn.commit()
conn.close()
```

上記のSQL文は、**"id"**と**"name"**を格納するテーブルを作成する。**"INTEGER"(整数)**, **"STRING"(文字列)**は、絡むへ入力するデータ型を指定している。
**"AUTOINCREMENT"**は、**"name"**をテーブルへ追加すると自動で**"id"**が追加されていくようにしてくれる。








## tableへデータのINSERT

```python:create_table.py
import sqlite3

dbname = 'TEST.db'
conn = sqlite3.connect(dbname)
cur = conn.cursor()

# "name"に"Taro"を入れる
cur.execute('INSERT INTO persons(name) values("Taro")')
# 同様に
cur.execute('INSERT INTO persons(name) values("Hanako")')
cur.execute('INSERT INTO persons(name) values("Hiroki")')

conn.commit()

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






## tableの内容を確認する (terminal)

terminalを開いて、$ "sqlite3 TEST.db" を実行する。
sqliteの対話型モードに入る。

```terminal:terminal
sqlite> .tables #.tablesでデータベースのテーブル一覧を取得
persons 
sqlite> SELECT * FROM persons; # personsの中身を確認。"id"が自動で追加されている.
1|Taro
2|Hanako
3|Hiroki
```




## tableの中身を確認する (python)

```python:check_table.py
import sqlite3

dbname = 'TEST.db'
conn = sqlite3.connect(dbname)
cur = conn.cursor()

# terminalで実行したSQL文と同じようにexecute()に書く
cur.execute('SELECT * FROM persons')

# 中身を全て取得するfetchall()を使って、printする。
print(cur.fetchall())

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

```terminal:結果
[(1,"Taro"), (2,"Hanako"), (3,"Hiroki")]
```
リストとして返ってくる。








## csvからtableを作成する (pandas利用)
csv内のデータをpandasデータフレームとして読み出し、データベースへ書き込む。

```python:create_DB.py
import sqlite3
import pandas as pd

# pandasでカレントディレクトリにあるcsvファイルを読み込む
# csvには、1列目にyear, 2列目にmonth, 3列目にdayが入っているとする。
df = pd.read_csv("calendar.csv")

# カラム名（列ラベル）を作成。csv file内にcolumn名がある場合は、下記は不要
# pandasが自動で1行目をカラム名として認識してくれる。
df.columns = ['year', 'month', 'day']

dbname = 'TEST.db'

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

# dbのnameをsampleとし、読み込んだcsvファイルをsqlに書き込む
# if_existsで、もしすでにexpenseが存在していたら、書き換えるように指示
df.to_sql('sample', conn, if_exists='replace')

# 作成したデータベースを1行ずつ見る
select_sql = 'SELECT * FROM sample'
for row in cur.execute(select_sql):
    print(row)

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

```console:結果
(0, 2000, 1, 1)
(1, 2000, 1, 2)
(2, 2000, 1, 3)
...
```



## DBをpandasで読み出す

```python:read_DB_from_pandas.py
import sqlite3
import pandas as pd

dbname = "TEST.db"
conn = sqlite3.connect(dbname)
cur = conn.cursor()

# dbをpandasで読み出す。
df = pd.read_sql('SELECT * FROM sample', conn)

print(df)

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



## DBをメモリ上に作成する
connectionがcloseされた時点で内容は消失する。

```python:
import sqlite3

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

# データベースを色々操作

conn.close()
# 閉じたら消える。
```