この記事にはpython3でsqlite3を操作して、データベースの作成や、編集の基礎的なことをまとめてます。家計簿や収入、株式投資のためにデータベースを利用していきたい。
本当に基礎的なことなので、この辺りを理解すれば、やりたいことに必要なことがクリアになると思います。
- DBを作成する
- tableを作成する
- tableへデータのINSERT
- tableの内容を確認する (terminal)
- tableの内容を確認する (python)
- csvからtableを作成する (pandas利用)
- DBをpandasで読み出す
- DBをメモリ上に作成する
DBを作成する
カレントディレクトリにTEST.dbというデータベースを作成する。
create_db.py
import sqlite3
# TEST.dbを作成する
# すでに存在していれば、それにアスセスする。
dbname = 'TEST.db'
conn = sqlite3.connect(dbname)
# データベースへのコネクションを閉じる。(必須)
conn.close()
tableを作成する
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
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
sqlite> .tables #.tablesでデータベースのテーブル一覧を取得
persons
sqlite> SELECT * FROM persons; # personsの中身を確認。"id"が自動で追加されている.
1|Taro
2|Hanako
3|Hiroki
tableの中身を確認する (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()
結果
[(1,"Taro"), (2,"Hanako"), (3,"Hiroki")]
リストとして返ってくる。
csvからtableを作成する (pandas利用)
csv内のデータをpandasデータフレームとして読み出し、データベースへ書き込む。
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()
結果
(0, 2000, 1, 1)
(1, 2000, 1, 2)
(2, 2000, 1, 3)
...
DBをpandasで読み出す
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された時点で内容は消失する。
import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
# データベースを色々操作
conn.close()
# 閉じたら消える。