PythonでAWS-RDSのMySQLをテスト（Appleの株価プロット)

PythonでAWS-RDSのMySQLをテスト（Appleの株価プロット):

ちょっとしたアプリを作りたくRDSを勉強中

趣旨を簡単に説明

既存のCSVファイル（今回は株価）から時系列データと株価を抽出し、RDS-MySQLに入れ、そこから更に参照し、webでグラフプロットしたい、今回はその前段階で単に上げたデータをまたローカルに戻すという作業のメモ。

必要なもの

• 株価と時系列を対照させたデータ
• AWSアカウント
• Python

PythonでCSVデータの送信準備

stocks = pd.read_csv('stock_px_2.csv', 
                           parse_dates=True, index_col=0) 
 
# Appleの株価のみ参照 
aapl_stock = stocks[['AAPL']] 
# 営業日ごとに欠損値を前参照 
aapl_stock = aapl_stock.resample('B').ffill() 
aapl_stock.plot() 

ffillでデータ欠損がある場合に埋めます。

250日の移動平均線もありますが、今回は値動きのみにします。

AWSにログイン&UP

rds = 'xxxxxxxxxxxxxxxx' 
con = mysql.connector.connect( 
    host=rds, 
    user="xxxx", 
    password="xxxxxxxx", 
    database='xxxxxxxx' 
) 
 
# 例外処理 
try: 
    cur = con.cursor() 
    with open('stock_px_mini.csv') as f: 
        reader = csv.reader(f) 
        heder = next(reader, None) 
 
        cur.execute("DROP TABLE IF EXISTS `stock_table_csv`") 
        cur.execute("""CREATE TABLE IF NOT EXISTS `stock_table_csv`( 
                        `_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
                        `datetime` DATETIME, 
                        `value` FLOAT);""") 
        con.commit() 
 
        sql = "INSERT INTO `stock_table_csv` (datetime, value) VALUES (%s, %s);" 
        for i in reader: 
            cur.execute(sql, (i[0], i[1])) 
        con.commit() 
 
except mysql.connector.Error as e: 
    print("Error code:", e.errno)  # error number 
    print("SQLSTATE value:", e.sqlstate)  # SQLSTATE value 
    print("Error message:", e.msg)  # error message 
    print("Error:", e)  # errno, sqlstate, msg values 
    s = str(e) 
    print("Error:", s) 
 
#Pandasでも辞書リストを経由してできました。 
 sql = "INSERT INTO stock_table (datetime, value) VALUES (%s, %s);" 
    for i in list(df.AAPL.to_dict().items()): 
        cur.execute(sql, i) 
    con.commit() 

ちゃんと格納されているか確認します。

cursor = con.cursor() 
 
sql ='SELECT * FROM stock_table_csv1' 
cursor.execute(sql) 
rows = cursor.fetchall() 
for i in rows: 
    print(i) 
''' 
(1, datetime.datetime(2003, 1, 2, 0, 0), 7.4) 
(2, datetime.datetime(2003, 1, 3, 0, 0), 7.45) 
(3, datetime.datetime(2003, 1, 6, 0, 0), 7.45) 
(4, datetime.datetime(2003, 1, 7, 0, 0), 7.43) 
(5, datetime.datetime(2003, 1, 8, 0, 0), 7.28) 
(6, datetime.datetime(2003, 1, 9, 0, 0), 7.34) 
(7, datetime.datetime(2003, 1, 10, 0, 0), 7.36) 
(8, datetime.datetime(2003, 1, 13, 0, 0), 7.32) 
(9, datetime.datetime(2003, 1, 14, 0, 0), 7.3) 
(10, datetime.datetime(2003, 1, 15, 0, 0), 7.22) 
(11, datetime.datetime(2003, 1, 16, 0, 0), 7.31) 
(12, datetime.datetime(2003, 1, 17, 0, 0), 7.05) 
''' 

格納されていましたね。

さて、これをちまちま手順を踏んでDataFrameの中に入れることもできますが、もっと便利な方法があります。

topandas

import pandas.io.sql as pdsql 
df = pdsql.read_sql(sql, con, index_col='_id') 
print(df) 
''' 
       datetime   value 
_id                     
1    2003-01-02    7.40 
2    2003-01-03    7.45 
3    2003-01-06    7.45 
4    2003-01-07    7.43 
5    2003-01-08    7.28 
6    2003-01-09    7.34 
7    2003-01-10    7.36 
8    2003-01-13    7.32 
9    2003-01-14    7.30 
10   2003-01-15    7.22 
11   2003-01-16    7.31 
12   2003-01-17    7.05 
13   2003-01-21    7.01 
14   2003-01-22    6.94 
15   2003-01-23    7.09 
16   2003-01-24    6.90 
17   2003-01-27    7.07 
18   2003-01-28    7.29 
19   2003-01-29    7.47 
''' 

ダイレクトにSQLからdf形式に変換してくれる便利なモジュールです。index_col=でインデックスも指定できます。

MySQLから取得したデータをプロット

forplot

df.plot() 
''' 
ValueError: view limit minimum -36714.312 is less than 1 and is an invalid Matplotlib date value. This often happens if you pass a non-datetime value to an axis that has datetime units 
''' 

Matplotlibでエラーが出てしまいました。

そうか、インデックスをdatetimeにし忘れたのかと思ったら

df.set_index('datetime', inplace=True) 

x軸がid、y軸が年代になってしまいました。

datetime列のキャストを忘れていたんですね。Javaだと型は常に意識していたのですが、Pythonですっかり忘れていました。そしてdatiteimeをset_indexでインデキシングします。

# 下記だとSeriesで返されるのでNG 
# df = pd.to_datetime(df['datetime']) 
 
df['datetime'] = pd.to_datetime(df['datetime']) 
df.set_index('datetime', inplace=True) 

きれいに再プロットすることができました！

詰まったこと

アップするときに、下記のようにPandasのイテレータで回しながら入れていこうとしたが、キャストしてもうまくいかなかった

for column_name, item in df.AAPL.iteritems(): 
    cur.execute(str(column_name), item) 
    con.commit() 

苦渋の選択として辞書化させリストに戻しました。

うまい方法があれば教えていただきたいです。

他のやり方としてsqlalchemyで.to_sqlすれば簡単にアップすことができるのですが、今回はforで回したかったので見送りました。python3は辞書の順番も保持してくれるので今回省略したのですが、本当はちゃんとforで回して順番通りにリストに格納しないといけませんね。