使用python读取黄金行情数据

黄金行情数据"/>

使用python读取黄金行情数据

使用 pandas_datareader.data  包，从FRED 官网获取黄金行情数据（）。

pandas_datareader.data  包是获取yahoo,google等财经信息，参见网址 。

1、获取黄金行情源码 getGold.py

import numpy as np
import pandas as pd
import pandas_datareader.data as web
import math
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
import pymysql
import datetime

#连接数据库
def connect():
    conn = pymysql.Connect(host='127.0.0.1',port=3306,user='root',passwd='123456',db='finance_db',charset='utf8')
    return conn

#关闭数据库连接
def close(conn):
    conn.close()

#查询数据库
def select(sql):
    conn = connect()
    cursor = conn.cursor()
    cursor.execute(sql)
    #print("cursor.excute:",cursor.rowcount) 
    #rs = cursor.fetchone()
    rs = cursor.fetchall()
    close(conn)
    return rs

#查询最近一天
def getLastTransDate():
    statement = 'select max(date) from t_gold '
    rs = select(statement)
    if rs[0][0] is None:
        startDate = '1960-01-01'
    else:
        startDate = rs[0][0].strftime('%Y-%m-%d')
    return startDate

def main():
    startDate = getLastTransDate()
    df = web.DataReader(name='GOLDAMGBD228NLBM', data_source='fred',start = startDate)
    print (df.info())
    #df.to_csv(r'./test.csv',encoding='gbk')
    #df.plot()
    #plt.show()

    connect = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/finance_db?charset=utf8')

    df.to_sql('t_gold',con=connect,if_exists='append',index=True)

if __name__ == '__main__':
    main()

'''
CREATE TABLE `t_gold` (
  `DATE` datetime DEFAULT NULL,
  `GOLDAMGBD228NLBM` double DEFAULT NULL,
  KEY `ix_t_gold_DATE` (`DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
'''

2、显示黄金价格趋势图，goldTrends.py

使用pandas 读取mysql数据库，使用matplotlib.pyplot 画价格曲线

import numpy as np
import pandas as pd
import pandas_datareader.data as web
import math
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
import pymysql
import datetime

#绘制图形
def drawTrends(startDate):
    connect = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/finance_db?charset=utf8')
    sql = 'select * from t_gold where date>=\''+startDate+'\''
    #print(sql)

    df = pd.read_sql_query(sql, connect)
    #print(df.info())
    df2 = df[['DATE', 'GOLDAMGBD228NLBM']]
    df2['DATE'] = pd.to_datetime(df2['DATE'])
    df2 = df2[['DATE', 'GOLDAMGBD228NLBM']]
    df2 = df2.sort_values(['DATE'])
    df2 = df2.set_index('DATE')
    ax = df2.plot(kind='line', title="Gold Price Change")
    ax.set_xlabel("Period", fontsize=12)
    ax.set_ylabel("Price", fontsize=12)

    plt.show()

def main():
    startDate = input('请输入起始日期【yyyy-mm-dd】：')
    drawTrends(startDate)

if __name__ == '__main__':
    main()

'''
CREATE TABLE `t_gold` (
  `DATE` datetime DEFAULT NULL,
  `GOLDAMGBD228NLBM` double DEFAULT NULL,
  KEY `ix_t_gold_DATE` (`DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
'''