#!/usr/bin/python
# -*- coding: UTF-8 -*-

from sqlalchemy import create_engine
import tushare as ts
import pandas as pd
import time as threadTime
from datetime import time,timedelta,datetime


# df = ts.get_tick_data('600377', date='2017-05-17')
# df.head(10)
# print(df)
# ts.get_hist_data('600848',start='2015-01-05',end='2015-01-09')
# 格力 000651  康美药业 600518  宁沪高速 600377  中国建筑 601668  
# 50ETF 510050  农业银行 601288  招商银行 600036  中国银行 601988  宝钢包装 601968
# 上证指数  000001

# 初始化mysql引擎
engine = create_engine('mysql://root:@127.0.0.1/stock?charset=utf8')


# # 获取上市日期
# df = ts.get_stock_basics()
# print(df)
# #存入数据库
# df.to_sql('ipo_date',engine,if_exists='append')
stockCodeList = ['000651', '600518', '600377', '601668', '601288', '600036', '601988', '601968', '002300','600732', '000998', '002191', '002250', '002465', '002518']
# stockCodeList = ['002300']

for stock_code in stockCodeList:
    # # stock_code='000651'
    # sqlStr="select t.`code`,t.timeToMarket from ipo_date t where t.code=%s" % (stock_code)
    # print sqlStr
    # df=pd.io.sql.read_sql_query(sqlStr,engine,index_col='code')
    # ipoDate = df.ix[stock_code]['timeToMarket'] #上市日期YYYYMMDD 
    # ipoStr=str(ipoDate) 
    # 增量更新指定日期  而不适用上市日期
    ipoStr='20170909'
    ipoDT= datetime.strptime(ipoStr, '%Y%m%d')

    oneYear = timedelta(days=365)
    oneDay=timedelta(days=1)
    dStartDate=ipoDT
    startDate=dStartDate.strftime('%Y-%m-%d')
    dEndDate=dStartDate+oneYear
    endDate=dEndDate.strftime('%Y-%m-%d')
    dNow=datetime.now()

    while dNow>dEndDate:
        print "start to get %s : %s -- %s " % (stock_code,startDate,endDate)
        df=ts.get_h_data(stock_code,start=startDate,end=endDate)
        #存入数据库
        df.to_sql(stock_code,engine,if_exists='append',index_label='date')
        print "\n get %s  date:%s - %s successful!" % (stock_code,startDate,endDate)
        threadTime.sleep(5)
        dStartDate=dEndDate+oneDay
        startDate=dStartDate.strftime('%Y-%m-%d')
        dEndDate=dStartDate+oneYear
        endDate=dEndDate.strftime('%Y-%m-%d')

    endDate=dNow.strftime('%Y-%m-%d')
    print "start to get %s : %s -- %s " % (stock_code,startDate,endDate)
    df=ts.get_h_data(stock_code,start=startDate,end=endDate)
    #存入数据库
    df.to_sql(stock_code,engine,if_exists='append',index_label='date')
    print "\n get %s  date:%s - %s successful!" % (stock_code,startDate,endDate)
    threadTime.sleep(5)
    print "%s day info all done!" % (stock_code)