Ejemplo n.º 1
0
def main():
    conf = ConfigParser.SafeConfigParser()
    conf.read("forex.ini")

    db = ForexDb(host=conf.get("DB",
                                  "host"),
                    user=conf.get("DB",
                                  "user"),
                    pas=conf.get("DB",
                                 "pass"),
                    database=conf.get("DB",
                                      "database"))

    infiledir = conf.get("FILE","path")

    files = get_files(infiledir)

    for file in files:
        print file
        yyyymmdd = file[0:8]
        yyyymm = file[0:6]

        forex_data = pd.read_csv(infiledir + file,chunksize=10000)

        """テーブルの作成"""
        db.create_db(yyyymm)
        db.Commit()

        """レコードの削除"""
        db.DeleteForex(yyyymmdd)
        db.Commit()

        """Insert"""
        for df in forex_data:
            [db.InsertForex(row["updatetime"],row["ccy_pair"],row["Bid"],row["Mid"],row["Ask"],row["Datetime"],yyyymm) for index,row in df.iterrows()]
            db.Commit()

        # """Load Data"""
        # db.AppendForex(infiledir + file,yyyymm)
        # db.Commit()

    """DB接続のクローズ"""
    db.close()

    return 0
Ejemplo n.º 2
0
def main():

    url = "https://query.yahooapis.com/v1/public/yql?q=select%20Date%2CTime%2Cid%2CBid%2CAsk%2CRate%20from%20yahoo.finance.xchange%20where%20pair%20in(%22USDJPY%22%2C%22EURJPY%22%2C%22EURUSD%22)&format=json&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback="

    _retlist = []
    append = _retlist.append
    cnt = 0
    is_test = 1

    startday = datetime.datetime.strftime(datetime.datetime.now(),"%Y%m%d")
    endday = datetime.datetime.strftime(datetime.datetime.now(),"%Y%m%d")

    print startday
    outputfullpath = "" + startday + "_rate.csv"

    youbi = datetime.datetime.now().isoweekday()
    starthour = 0
    endhour = 30
    if youbi == 1:
        """月曜の場合は開始時間を遅らす"""
        starthour = 5
    if youbi == 6:
        """土曜の場合は修了時間を早める"""
        endhour = 7

    """開始待ち"""
    while True:
        """開始時刻よりも後になったらbreak"""
        if datetime.datetime.now().hour >= starthour:
            break

    while True:
        if startday != endday:
            break
        if cnt > 5 and is_test == 1:
            break
        if datetime.datetime.now().hour >= endhour:
            break

        try:
            """YQLを用いて現時点での為替レートを取得する。"""
            response = urllib2.urlopen(url)
            response_json = response.read()

            """keyとなる現時点の時刻"""
            nowtime = datetime.datetime.now()

            response_dict = json.loads(response_json)

            """取得結果はlist of dictになっている"""
            ccy_list_of_dict = response_dict["query"]["results"]["rate"]

            """現在時刻を付け足す"""
            map(lambda x:x.__setitem__(u"updatetime",nowtime),ccy_list_of_dict)

            """リストに追加していく"""
            [append(dict) for dict in ccy_list_of_dict]

            cnt += 1
            endday = datetime.datetime.strftime(datetime.datetime.now(),"%Y%m%d")


        except Exception as e:
            print '=== エラー内容 ==='
            print 'type:' + str(type(e))
            print 'args:' + str(e.args)
            print 'message:' + e.message
            print 'e自身:' + str(e)
            """エラーが出たら1分待つ"""
            time.sleep(60)

    """リストをデータフレームにする"""
    df = pd.DataFrame(_retlist)
    df.rename(columns={"id":"ccy_pair","Rate":"Mid"},inplace=True)
    df["Datetime"] = df["Date"] + " " + df["Time"]
    df["Datetime"] = map(lambda x:datetime.datetime.strptime(x,"%m/%d/%Y %I:%M%p").strftime("%Y-%m-%d %H:%M:%S"),df["Datetime"])
    df["updatetime"] = map(lambda x:x.strftime("%Y-%m-%d %H:%M:%S"),df["updatetime"])
    df = df[["updatetime","ccy_pair","Bid","Mid","Ask","Datetime"]]

    """csvに吐き出す"""
    df.to_csv(outputfullpath,index=None)

    print df

    """DBへのInsert"""
    print "Insert into Data Base"
    conf = ConfigParser.SafeConfigParser()
    conf.read("forex.ini")

    db = ForexDb(host=conf.get("DB",
                                  "host"),
                    user=conf.get("DB",
                                  "user"),
                    pas=conf.get("DB",
                                 "pass"),
                    database=conf.get("DB",
                                      "database"))

    """テーブルの作成"""
    print "Create Database"
    db.create_db(startday[0:6])
    db.Commit()
    
    """レコードの削除"""
    print "Deleate Record"
    db.DeleteForex(startday)
    db.Commit()

    """Insert"""
    print "Convert to List of Dict"
    list_of_dict = [row.to_dict() for index,row in df.iterrows()]
    print "Insert to DB"
    db.InsertForexMulti(list_of_dict,startday[0:6])
    db.Commit()

    """DB接続のクローズ"""
    db.close()