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
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
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
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()
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)