学生時代の残骸を掘り起こす[SQLite]
大学院の時のデータサイエンス系の講義で書いたSQLiteのコードがGoogleDriveを漁っていたところ発掘されたので，それを復習がてら解き明かそうと思いました．
講義で習ったSQLというかPythonのコードは殆ど同じですが，仮想のデータ部分は修正しております．
これで僕もあなたもsqlite使いです．嘘です．
SQLiteとは
サーバとしてではなくアプリケーションに組み込んで利用されるデータベースである
とWikipediaにありました．
MySQLとかとの大きな違いっぽいです．
詳しくはWikipediaへ
コードの中身
3年ほど前とはいえ，全然記憶にないです．そして読めない．
sqlite3をimportします．
import sqlite3
実際に作ってたらどうかと思うのですが，Friends.dbを作成します．
conn = sqlite3.connect("Friends.db")
作ったデータベースにfriendsというテーブルを作ります．
nameがSTRING型，ageはINTEGER型，hometownがVARCHAR型，yearがREAL型です．
VARCHARとは何ぞやと思ったのですが可変長文字列の型だそうです．
ちなみに括弧の中で可変長の文字数を制限しているのか？と思ったのですが，そうでもないっぽいです．
感覚的にyearはREALじゃなくINTEGERにすると思うのですが…
というかyearって何なんだ…友達になった年？友達になってから何年目？
色々と闇が深い．
query = """
CREATE TABLE friends(
name STRING,
age INTEGER,
hometown VARCHAR(20),
year REAL
)
"""
で，これをDBに反映します．
cur = conn.cursor()
cur.execute(query)
情報を入れていきます．
cur.execute("INSERT INTO friends VALUES('佐藤',25,'東京',1)")
cur.execute("INSERT INTO friends VALUES ('鈴木', 40, '京都', 12.0)")
cur.execute("INSERT INTO friends VALUES ('Mike', 33, 'New York', 6.25)")
コミットしていったんクローズします．
conn.commit()
conn.close()
今度はSELECTして中身を確認します．
fetchallで全てを取得．
fetchoneで一つ，fetchmany(N)でN個取得らしいです．
conn = sqlite3.connect("Friends.db")
cur = conn.cursor()
cur.execute("SELECT * FROM friends")
cur.fetchall()
ちょっとキレイに見たいので，ここからはpandasを使ってみます．
講義ではpandas使わなかったです．
import pandas as pd
query = "SELECT * FROM friends"
df = pd.read_sql(query,conn)
df
"friends"テーブルを消す場合はDROPするそうです．
この場合だと，友達全員と縁を切ったことになります…
query = "DROP TABLE friends"
cur.execute(query)
特定のレコードを消す場合はDELETE，WHEREで条件を絞ります．
鈴木さんと縁を切ってみましょう…
query = "DELETE FROM friends WHERE name='鈴木'"
cur.execute(query)
縁を切りまくってますが，僕にはそもそも友達が居たのか．
では，DROP，CREATE，INSERTしてテーブルを復元した後，レコードの情報を更新してみます．
UPDATEを使います．
佐藤さんとはネット上の友達で，年齢が25歳と聞いていたけど，実際に会ってみたら見た目が明らか…で蓋を開けてみたら年齢を偽られていた場合です．
query = "UPDATE friends SET age=55 WHERE name='佐藤'"
cur.execute(query)
次は，列を消す変更をかけてみます．ALTERを使ってみます．
佐藤さんが年齢を鯖を読んでいようと友達の年齢なんて気にしない！
そんな気持ちが芽生えた場合です．
query = "ALTER TABLE friends DROP age"
cur.execute(query)
列を追加する変更はALTERでADDです．
友達をIDで管理したくなった時が以下になります．
query = "ALTER TABLE friends ADD friends_id"
cur.execute(query)
真面目にSELECT文について講義でやった個所を羅列します．
平均，最大，最小を引っ張ってくる．
query = "SELECT MIN(age),AVG(age),MAX(age) FROM friends "
df = pd.read_sql(query,conn)
df
ORDER BY ASCは昇順，ORDER BY DESC降順らしいです．
年齢で昇順した例です．
query = "SELECT * FROM friends ORDER BY age ASC "
df = pd.read_sql(query,conn)
df
firends_idを全てNoneのままで良いので活用すると，同じfrieds_idでカウントもできます．
GROUP BYを使います．
query = "SELECT COUNT(*) FROM friends GROUP BY friends_id "
df = pd.read_sql(query,conn)
df
雑感
正規化や射影，直積とかも講義資料にガッツリ書いてあって笑いました．
習った記憶がない…
Discussion