fetch_stock_price.py

### 株価の保存
import urllib3
import pandas as pd
from io import StringIO

## データベースの準備
import psycopg2

conn = psycopg2.connect("user=USER_NAME dbname=dev host=127.0.0.1")
cur = conn.cursor()

#  csvのダウンロード
url = "https://kabuoji3.com/stock/file.php"

# HTTPクライアントの作成
mgr = urllib3.PoolManager()

# 保存した企業情報を取得する
cur.execute("SELECT * FROM company;")
for r in cur.fetchall():
    code = r[0]

    # リクエストを投げる
    r = mgr.request("POST", url, fields={"code": code, "year": 2018})
    # csvをパースする
    body = r.data.decode("shift_jis")
    csv_data = StringIO(body)
    df = pd.read_csv(csv_data, sep=",", skiprows=1)
    for i, row in df.iterrows():
        d = row["日付"]
        o = row["始値"]
        c = row["終値"]
        h = row["高値"]
        l = row["安値"]
        t = row["出来高"]
        a = row["終値調整値"]
        cur.execute("INSERT INTO price (date, code, open, close, high, low, turnover, adjustment_close) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)", (d, code, o, c, h, l, t, a))

                
cur.execute("SELECT * FROM price;")
cur.fetchall()
conn.commit()
cur.close()
conn.close()