0.背景
個人的に投資を副業でやっていますが、自宅PCにてそろそろ分析を自動でやりたくなった。
まずはデータを格納する箱(データベース)を学んだので、そのド基礎の部分だけ記録として残しておく。
- 動作環境
- OS : Windows10 pro
- Python : 3.8.3// Miniconda 4.9.1
- sqlite3：2.6.0
- (管理ツール：DB Browser for SQLite ※すぐに中身を見るときに便利)
※sqlite3 モジュールをインストールする必要はありません。Python(2.5以降)の標準ライブラリに含まれています。
1.データベース作成～操作
データベースをExcelで考えるととりあえずは理解しやすいと思ったので、今回は最初の部分に関してExcelに置き換えて説明する。
①まずはデータベースを新規作成して、接続する
Excelファイルを作成して、Excelを開くのと同等なイメージ
isolation_level=Noneは、データベースを保存するコミットという操作を忘れたときに自動保存してくれる設定
import sqlite3
dbname = ('test.db')#データベース名.db拡張子で設定
conn = sqlite3.connect(dbname, isolation_level=None)#データベースを作成、自動コミット機能ON
②テーブルを作成する
Excelを操作する為にマウスを使用するが、sqliteはカーソルオブジェクトで行を操作する
※カーソル≒Excelの行みたいなイメージ？
テーブルとは、ExcelでいうSheetのことを指す
なお操作が終わったら毎回Excelを上書き保存をする(コミット)
cursor = conn.cursor() #カーソルオブジェクトを作成
"""
・create table テーブル名（作成したいデータカラム）というSQL文でテーブルを宣言
※SQL命令は大文字でも小文字でもいい
・今回はtestテーブルに「id,name,date」カラム(列名称)を定義する※今回dateは生年月日という列
・「if not exists」はエラー防止の部分。すでに同じテーブルが作成されてるとエラーになる為
・カラム型は指定しなくても特には問題ない
※NULL, INTEGER(整数), REAL(浮動小数点), TEXT(文字列), BLOB(バイナリ)の5種類
"""
sql = """CREATE TABLE IF NOT EXISTS test(id, name, date)"""
cursor.execute(sql)#executeコマンドでSQL文を実行
conn.commit()#データベースにコミット(Excelでいう上書き保存。自動コミット設定なので不要だが一応・・)
③作成したテーブル名を取得する
Excelは画面上に作成したSheet名がすぐ見えるが、SQLは当然わからないので問い合わせをする
#データベース中のテーブル名を取得するSQL関数
sql = """SELECT name FROM sqlite_master WHERE TYPE='table'"""
for t in cursor.execute(sql):#for文で作成した全テーブルを確認していく
print(t)
('test',)
④-1作成したテーブルにレコードを1行格納する
行ごとのデータのことをSQLではレコードと呼ぶ
executeで1行追加
"""
レコードを追加する場合はinsert文を使う。
SQLインジェクションという不正SQL命令への脆弱性対策でpythonの場合は「？」を使用して記載するのが基本。
"""
sql = """INSERT INTO test VALUES(?, ?, ?)"""#?は後で値を受け取るよという意味
data = ((1, 'Taro', 19800810))#挿入するレコードを指定
cursor.execute(sql, data)#executeコマンドでSQL文を実行
conn.commit()#コミットする
④-2作成したテーブルにレコードを複数行格納する
executeではなく、executemanyを使用する
sql = """INSERT INTO test VALUES(?,?,?)"""
data = [
(1, "Taro", 19800810),
(2, "Bob", 19921015),
(3, "Masa", 20050505),
(4, "Jiro", 19910510),
(5, "Satoshi", 19880117)
]
cursor.executemany(sql, data)#複数のデータを追加したい場合はexecutemanyメソッドを使う
conn.commit()#コミットする
⑤-1作成したテーブル内の全レコードを取り出す
fetchallを使用する
"""
select * ですべてのデータを参照し、fromでどのテーブルからデータを呼ぶのか指定
fetchallですべての行のデータを取り出す
"""
sql = """SELECT * FROM test"""
cursor.execute(sql)
print(cursor.fetchall())#全レコードを取り出す
[(1, "Taro", 19800810),(2, "Bob", 19921015),(3, "Masa", 20050505),(4, "Jiro", 19910510),(5, "Satoshi", 19880117)]
⑤-2作成したテーブル内の全レコードを1行ずつ取り出す
fetchoneを使用する。実行結果は⑤-1と同じ。
select_sql = """SELECT * FROM test"""
cursor.execute(select_sql)
while True:
result=cursor.fetchone()#データを1行抽出
if result is None :#ループ離脱条件(データを抽出しきって空になったら)
break #breakでループ離脱
print(result)
⑥レコードを削除する
"""
whereのあとに消したいデータの条件を書いて指定
このテーブルの1行目の要素はidなので例としてidが2のデータを指定
"""
cursor.execute('delete from test where id=?', (2,))
conn.commit()#コミットする
cursor.execute('select * from test')
print(cursor.fetchall())
[(1, "Taro", 19800810),(3, "Masa", 20050505),(4, "Jiro", 19910510),(5, "Satoshi", 19880117)]
⑦作成したテーブル名称を変更する
"""
ALTER TABLE 変更前のテーブル名 RENAME TO 変更後のテーブル名
"""
sql = """ALTER TABLE test RENAME TO test1"""
#命令を実行
conn.execute(sql)
conn.commit()#コミットする
⑧作成したテーブルを削除する
"""
DROP TABLE if exists 削除テーブル名
"""
#sql = """DROP if exists TABLE test1"""
sql = """DROP TABLE if exists test1""" #2021/9/19修正
#命令を実行
conn.execute(sql)
conn.commit()#コミットする
⑨データベースのコネクション(接続)を遮断する
#作業完了したらDB接続を閉じる
conn.close()
⑩(おまけ)作成したDBをテーブルを指定したうえでPandasで読み出す
①～⑥までで作成した「test」というテーブルを読み出す場合の例です。 ⑦でtest1に改名したり、⑧でそもそもテーブル削除してますのでそのあとにやってもエラーになります。
import pandas as pd
dbname = "test.db"
conn = sqlite3.connect(dbname)
# dbをread_sqlを使用してpandasとして読み出す。
df = pd.read_sql('SELECT * FROM test', conn)
df.head()
⑥の結果をPandasで表示する
| id | name | date | |
|---|---|---|---|
| 0 | 1 | Taro | 19800810 |
| 1 | 3 | Masa | 20050505 |
| 2 | 4 | Jiro | 19910510 |
| 3 | 5 | Satoshi | 19880117 |
2.最後に
本記事は投資活用のための記事の第1弾です。今後も投資関連に役立つPython記事を書いていきます
ほんの少しでも役立つと思った方はLGTM、ストックいただけると励みになります！
3.追記
本記事の続編も書きましたので読み終わった後にどうぞ！