示例#1
0
def get_last_timestamp(symbol, period):
    db = mysqlutil.init()
    cursor = db.cursor()

    try:

        sql = '''
            SELECT ts FROM xcm_%s_kline 
            WHERE symbol='%s' AND period='%s'
            ORDER BY ts DESC LIMIT 0,1
        ''' % (TABLE, symbol, period)
        cursor.execute(sql)
        rows = cursor.fetchall()
        if len(rows) == 0:
            since = util.getTimeStamp('2018-01-01 00:00:00')
        else:
            lastTimestamp = rows[0][0]
            # 打印最近一条数据的时间
            # mysqlutil.log(TABLE, startDate, period, 'last timestamp >>>', lastTimestamp)
            mysqlutil.log(TABLE, period, 'last datetime >>>',
                          util.getLocaleDateStrBy10(lastTimestamp))
            since = rows[0][0]

        return since
    except:
        util.printExcept(target='get-' + TABLE + '-kline > get_last_timestamp')
    finally:
        cursor.close()
        db.close()

    return False
示例#2
0
def insert_huobipro_kline_history(klineStr):
    mysqlutil.log(TABLE, klineStr)
    klineObj = json.loads(klineStr)

    if 'data' not in klineObj:
        return False

    klineObj = json.loads(klineStr)
    data = klineObj['data']

    # 初始化数据库连接
    db = mysqlutil.init()
    cursor = db.cursor()

    sql = '%s'
    param = ('not set')
    tick = 'not set'

    try:

        for tick in data:
            tick = convert_kline_json_by_ticker(tick, klineObj['rep'])
            # mysqlutil.log(TABLE,tick)
            # 定义 _id
            nowTime = time.time()
            nowTime *= 10000000

            sql = '''INSERT INTO `xcm_huobipro_kline` 
              (_id, symbol, base, quote, period, ts, amount, `count`, `open`, `close`, low, high, vol) 
              VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
            param = (
                nowTime, tick['symbol'], tick['base'], tick['quote'], tick['period'],
                tick['id'], tick['amount'], tick['count'], tick['open'], tick['close'],
                tick['low'], tick['high'], tick['vol'])
            # print(param)
            cursor.execute(sql, param)

        db.commit()

        return len(data)
    except:
        ex = sys.exc_info()
        mysqlutil.log(TABLE, '\033[0;30;41m get_kline except >>> \033[0m \nEX_NAME:', ex[0], '\nEX_VAL:', ex[1], '\nEX_TRACK:\n', ex[2])
        mysqlutil.log(TABLE, '\033[0;30;43m get_kline except sql symbol >>> \033[0m', tick['symbol'])
        mysqlutil.log(TABLE, '\033[0;30;43m get_kline except sql period >>> \033[0m', tick['period'])
        mysqlutil.log(TABLE, '\033[0;30;43m get_kline except sql ts >>> \033[0m', util.getLocaleDateStrBy10(tick['id']))
        # mysqlutil.log(TABLE,'get_kline except sql >>>', sql%param)
        util.printExcept()

    # 关闭数据库连接
    cursor.close()
    db.close()

    return True
示例#3
0
def get_sina_rate_usdcny():
    url = 'http://hq.sinajs.cn/rn=1531609137914list=fx_susdcny'
    response = request.urlopen(url)
    content = response.read().decode('gbk')
    # print(content)

    matchObj = re.search(
        r',([^,]*?),美元兑人民币即期汇率', content, re.I)
    usdcny = matchObj.group(1)
    print('美元兑人民币即期汇率 >>>', usdcny)
    print(util.getLocaleDateStrBy10(time.time()))

    return usdcny
示例#4
0
def get_kline_by_symbol(period, initTimestamp, symbol):
    if not ws.sock:
        mysqlutil.log(TABLE, '\n### websocket was closed. ###\n')
        timer[period].cancel()
        return

    db = mysqlutil.init()
    cursor = db.cursor()

    try:
        # 获取数据表中最后一条记录,获取它的时间戳,作为请求的开始时间戳。
        sql = """
        SELECT ts 
        FROM xcm_huobipro_kline 
        WHERE period=%s AND symbol=%s
        ORDER BY ts DESC LIMIT 1"""
        # mysqlutil.log(TABLE, sql)
        cursor.execute(sql, (period, symbol))
        kdata = cursor.fetchall()
        if len(kdata) != 0:
            mysqlutil.log(TABLE, 'get_kline_by_symbol kdata >>>', kdata)
            fromTimeStamp = kdata[0][0] + 1
        else:
            fromTimeStamp = initTimestamp

        nowTime = int(time.time() * 1000000)
        req = '{"req": "market.%s.kline.%s","id": "kline_%d","from":%d}' % (
            symbol, period, nowTime, fromTimeStamp)
        mysqlutil.log(TABLE, 'get_kline_by_symbol req >>>', req)
        mysqlutil.log(TABLE, '\033[0;30;43m get_kline_by_symbol req symbol >>> \033[0m', symbol)
        mysqlutil.log(TABLE, '\033[0;30;43m get_kline_by_symbol req period >>> \033[0m', period)
        mysqlutil.log(TABLE, '\033[0;30;43m get_kline_by_symbol req fromTimeStamp >>> \033[0m',
                      util.getLocaleDateStrBy10(fromTimeStamp))
        ws.send(req)
    except:
        mysqlutil.log(TABLE, '\033[0;30;41m get_kline_by_symbol except >>> \033[0m')
        filename = os.path.basename(sys.argv[0]).split(".")[0]
        util.printExcept(filename)
    finally:
        cursor.close()
        db.close()
示例#5
0
def get_kline(period, initTimestamp, sleep):
    if not ws.sock:
        mysqlutil.log(TABLE, '\n### websocket was closed. ###\n')
        timer[period].cancel()
        return

    # 防止相同周期的进程重复运行
    if threadRunning[period]:
        mysqlutil.log(TABLE, 'threadRunning', period, 'was running.')
        return

    # 延迟执行
    time.sleep(sleep)
    threadRunning[period] = True
    global runTime
    runTime[period] += 1

    db = mysqlutil.init()
    cursor = db.cursor()

    try:

        # 获取交易对清单,循环请求K线数据。
        cursor.execute("SELECT symbol FROM xcm_huobipro_symbol ORDER BY _id ASC")
        rows = cursor.fetchall()

        nowTime = int(time.time() * 1000000)

        global getKlineCount
        mysqlutil.log(TABLE, 'get_kline rows len >>>', len(rows))

        count = 0
        for r in rows:

            # 分批获取数据。火币Pro交易所的 WebSocket 似乎不能同时请求太多,单次请求的上限似乎为 27 个。
            count += 1
            if getKlineCount[period] >= count:
                continue

            symbol = r[0]
            # mysqlutil.log(TABLE, 'symbol >>>', symbol)

            # 获取数据表中最后一条记录,获取它的时间戳,作为请求的开始时间戳。
            sql = """
            SELECT ts 
            FROM xcm_huobipro_kline 
            WHERE period=%s AND symbol=%s
            ORDER BY ts DESC LIMIT 1"""
            # mysqlutil.log(TABLE, sql)
            cursor.execute(sql, (period, symbol))
            kdata = cursor.fetchall()
            if len(kdata) != 0:
                # mysqlutil.log(TABLE, 'get_kline', period, 'kdata >>>', kdata)
                fromTimeStamp = kdata[0][0] + 1
            else:
                fromTimeStamp = initTimestamp

            req = '{"req": "market.%s.kline.%s","id": "kline_%d","from":%d}' % (
                symbol, period, nowTime, fromTimeStamp)
            mysqlutil.log(TABLE, '\033[0;30;44m get_kline req >>> \033[0m', req)
            mysqlutil.log(TABLE, '\033[0;30;43m get_kline req symbol >>> \033[0m', symbol)
            mysqlutil.log(TABLE, '\033[0;30;43m get_kline req period >>> \033[0m', period)
            mysqlutil.log(TABLE, '\033[0;30;43m get_kline req fromTimeStamp >>> \033[0m',
                          util.getLocaleDateStrBy10(fromTimeStamp))

            if not ws.sock:
                mysqlutil.log(TABLE, '\n### websocket was closed. ###\n')
                timer[period].cancel()
                return
            ws.send(req)
            mysqlutil.log(TABLE, 'get_kline count', period + ' >>>', count)  # 执行进度。已经将数据库表中的交易对清单执行到第几个。

            if count % 20 == 0:
                getKlineCount[period] = count
                break

            time.sleep(1)

        # 如果一组交易对已经处理完,重置获取计数。
        if count == len(rows):
            getKlineCount[period] = 0
            fullTime[period] += 1

        mysqlutil.log(TABLE, '\033[0;30;42m getKlineCount', period + ' >>> \033[0m', getKlineCount[period])

    except:
        mysqlutil.log(TABLE, '\033[0;30;41m get_kline except >>> \033[0m')
        filename = os.path.basename(sys.argv[0]).split(".")[0]
        util.printExcept(filename)
    finally:
        cursor.close()
        db.close()

    # 恢复进程未运行的标签
    threadRunning[period] = False

    mysqlutil.log(TABLE, '==========> runTime  ===> ', runTime)
    mysqlutil.log(TABLE, '==========> fullTime ===> ', fullTime)