Python
AWS

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でデータ欠損がある場合に埋めます。

stock.png

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)

stock0.png

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)

stock1.png

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


詰まったこと

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

for column_name, item in df.AAPL.iteritems():

cur.execute(str(column_name), item)
con.commit()

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

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

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