def init(): global count, done, doneToken, pendingPeriods, periods mysqlutil.log('zb', str(datetime.datetime.now()), 'init') count = 0 done = [] doneToken = { '1min': 0, '3min': 0, '5min': 0, '15min': 0, '30min': 0, '1hour': 0, '2hour': 0, '4hour': 0, '6hour': 0, '12hour': 0, '1day': 0, '3day': 0, '1week': 0 } pendingPeriods = { '1min', '3min', '5min', '15min', '30min', '1hour', '2hour', '4hour', '6hour', '12hour', '1day', '3day', '1week' } periods = [ '1min', '3min', '5min', '15min', '30min', '1hour', '2hour', '4hour', '6hour', '12hour', '1day', '3day', '1week' ]
def get_okex_kline_history_by_peroid(): init() db = mysqlutil.init() cursor = db.cursor() try: global total if total == 0: sql = 'SELECT count(_id) FROM xcm_okex_kline' cursor.execute(sql) rows = cursor.fetchall() if len(rows) != 0: total = rows[0][0] mysqlutil.log('okex', '\ntotal >>> {:,}'.format(total)) except: util.printExcept() finally: cursor.close() db.close() for p in periods: mysqlutil.log('okex', 'start period thread >>>', p) _thread.start_new_thread(get_okex_kline_history, (p, ))
def request_rest(url, logTable): try: mysqlutil.log(logTable, url) socket.setdefaulttimeout(20) headers = { "Content-type": "application/x-www-form-urlencoded", 'User-Agent': "Mozilla/5.0 (Windows NT 6.1; rv:32.0) Gecko/20100101 Firefox/32.0", } req = request.Request(url, None, headers) response = request.urlopen(req) mysqlutil.log(logTable, 'response code >>>', response.code) content = response.read().decode('utf-8') content = json.loads(content) if 'message' in content: mysqlutil.log(logTable, '\033[0;30;41m', 'content >>>', content, '\033[0m') return False else: mysqlutil.log(logTable, 'content >>>', content) mysqlutil.log(logTable, 'data len >>>', len(content)) return content except: util.printExcept() sleep(30) return False
def init(): global count, done, doneToken, pendingPeriods, periods mysqlutil.log('binance', str(datetime.datetime.now()), 'init') count = 0 done = [] doneToken = { '1m': 0, '3m': 0, '5m': 0, '15m': 0, '30m': 0, '1h': 0, '2h': 0, '4h': 0, '6h': 0, '8h': 0, '12h': 0, '1d': 0, '3d': 0, '1w': 0, '1M': 0 } pendingPeriods = { '1m', '3m', '5m', '15m', '30m', '1h', '2h', '4h', '6h', '8h', '12h', '1d', '3d', '1w', '1M' } periods = [ '1m', '3m', '5m', '15m', '30m', '1h', '2h', '4h', '6h', '8h', '12h', '1d', '3d', '1w', '1M' ]
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_kline_timer(interval, period, initTimestamp): global timer # 检查 websocket 是否已经关闭 if not ws.sock: mysqlutil.log(TABLE, '\n### websocket was closed. ###\n') timer[period].cancel() return # get_kline(period, initTimestamp) thread.start_new_thread(get_kline, (period, initTimestamp, interval)) # interval + 60 是给定时器添加 1 分钟的缓冲时间,避免相同周期的进程同时运行。 timer[period] = threading.Timer(interval + 60, get_kline_timer, [ interval, period, initTimestamp]) timer[period].start()
def insert_huobipro_kline(klineStr): mysqlutil.log(TABLE, klineStr) klineObj = json.loads(klineStr) if 'tick' not in klineObj: return False klineObj = convert_kline_json(klineStr) tick = klineObj['tick'] # 初始化数据库连接 db = mysqlutil.init() cursor = db.cursor() # 定义 _id nowTime = time.time() nowTime *= 10000000 try: sql = """ INSERT INTO `xcm_huobipro_kline` (_id,symbol,base,quote,period,ts, ts,amount,count,open,close, low,high,vol) VALUES (%s,%s,%s,%s, %s,%s,%s,%s,%s, %s,%s,%s) """ cursor.execute(sql, ( nowTime, tick['symbol'], tick['base'], tick['quote'], tick['period'], klineObj['ts'], tick['id'], tick['amount'], tick['count'], tick['open'], tick['close'], tick['low'], tick['high'], tick['vol'])) db.commit() finally: pass # 关闭数据库连接 cursor.close() db.close() return True
def request_okex_kline_rest(symbol, period, since='', size=2000): try: url = 'https://www.okex.com/api/v1/kline.do?symbol=%s&type=%s&since=%s&size=%d' % ( symbol, period, since, size) mysqlutil.log('okex', url) socket.setdefaulttimeout(20) headers = { "Content-type": "application/x-www-form-urlencoded", 'User-Agent': "Mozilla/5.0 (Windows NT 6.1; rv:32.0) Gecko/20100101 Firefox/32.0", } req = request.Request(url, None, headers) response = request.urlopen(req) content = response.read().decode('utf-8') return json.loads(content) except: util.printExcept() return False
def on_open(ws): mysqlutil.log(TABLE, '\n### Connected ###\n') global SYMBOLS SYMBOLS = CommonApi.get_symbols(TABLE, 'dict') mysqlutil.log(TABLE, SYMBOLS) # nowTime = int(time.time() * 1000000) # def run(*args): # ws.send( # '{"sub": "market.btcusdt.kline.1min","id": "kline_' + str(nowTime) + '"}') ts = util.getTimeStamp(initDateStr) # 根据周期字典启动定时器 for key in periods: period = periods[key] if period == 0: continue get_kline_timer(period, key, ts)
def get_total(tableName): db = mysqlutil.init() cursor = db.cursor() try: global total if total == 0: sql = 'SELECT count(_id) FROM %s'%(tableName) cursor.execute(sql) rows = cursor.fetchall() if len(rows) != 0: total = rows[0][0] mysqlutil.log('binance', '\ntotal >>> {:,}'.format(total)) except: util.printExcept() finally: cursor.close() db.close()
def get_kline_total(): db = mysqlutil.init() cursor = db.cursor() total = -1 try: cursor.execute('SELECT COUNT(_id) FROM xcm_huobipro_kline') kdata = cursor.fetchall() if len(kdata) != 0: total = kdata[0][0] except: mysqlutil.log(TABLE, '\033[0;30;41m get_kline_total except >>> \033[0m', sys.exc_info()[0]) if total == -1: mysqlutil.log(TABLE, 'get_kline_total error') total = 0 cursor.close() db.close() return total
def init(): global count, done, doneToken, pendingPeriods, periods mysqlutil.log('gate', str(datetime.datetime.now()), 'init') count = 0 done = [] doneToken = { '1m': 0, '5m': 0, '10m': 0, '15m': 0, '20m': 0, '30m': 0, '1h': 0, '2h': 0, '3h': 0, '4h': 0, '6h': 0, '8h': 0, '1d': 0, '2d': 0, '3d': 0, '4d': 0, '5d': 0, '6d': 0, '7d': 0 } pendingPeriods = { '1m', '5m', '10m', '15m', '20m', '30m', '1h', '2h', '3h', '4h', '6h', '8h', '1d', '2d', '3d', '4d', '5d', '6d', '7d' } periods = [ '1m', '5m', '10m', '15m', '20m', '30m', '1h', '2h', '3h', '4h', '6h', '8h', '1d', '2d', '3d', '4d', '5d', '6d', '7d' ]
def save_kline(period): global exCount, errCount startTimestamp = time.time() db = mysqlutil.init() cursor = db.cursor() try: symbols = chitbtc.get_symbols() symbolCount = 0 for s in symbols: symbol = s[0] base = s[1] quote = s[2] # 打印当前处理的交易对和处理进度 symbolCount += 1 mysqlutil.logWithList(table=TABLE, list=['\033[0;30;42m', startDate, period, 'symbol >>>', symbol, symbolCount, '/', len(symbols), '\033[0m'], show=True) kdata = chitbtc.get_candle_info(symbol, period) if kdata == False: errCount += 1 time.sleep(10) continue # 访问太过频繁,超过了每 1 秒钟 100 次的限制,先暂停休眠 1 秒钟。 if 'error' in kdata and kdata['error']['code'] == 429: time.sleep(1) if 'error' in kdata: # 打印请求返回的错误信息 mysqlutil.log(TABLE, startDate, period, symbol, 'get_candle_info error >>>', kdata) errCount += 1 continue save_kline_data(symbol, base, quote, period, kdata) mysqlutil.log(TABLE, period, 'complete datetime >>>', datetime.datetime.now()) mysqlutil.log(TABLE, period, ' time elapsed >>>', util.timeElapsed(startTimestamp)) except: exCount += 1 util.printExcept(target='get-' + TABLE + '-kline > save_kline') finally: cursor.close() db.close()
def request_kline_rest(symbol, period, since): try: periodSec = get_period_interval(period) rangeHour = get_hour_span(since) url = 'https://data.gateio.io/api2/1/candlestick2/%s?group_sec=%s&range_hour=%s' % ( symbol, periodSec, rangeHour) mysqlutil.log('gate', url) socket.setdefaulttimeout(20) headers = { "Content-type": "application/x-www-form-urlencoded", 'User-Agent': "Mozilla/5.0 (Windows NT 6.1; rv:32.0) Gecko/20100101 Firefox/32.0", } req = request.Request(url, None, headers) response = request.urlopen(req) mysqlutil.log('gate', 'response code >>>', response.code) content = response.read().decode('utf-8') mysqlutil.log('gate', 'content >>>', content) content = json.loads(content) mysqlutil.log('gate', 'data len >>>', len(content)) if 'code' in content: mysqlutil.log('gate', 'error url >>>', url) # 返回请求太频繁的错误 if content['code'] == 40: time.sleep(60) return content data = content['data'] if len(data) > 0: mysqlutil.log('gate', 'data >>>', '[' + str(data[0]) + ', ...]') mysqlutil.log('gate', 'data start >>>', util.getLocaleDateStrBy13(int(data[0][0]))) mysqlutil.log( 'gate', 'data end >>>', util.getLocaleDateStrBy13(int(data[len(data) - 1][0]))) return content except: util.printExcept() return False
def request_kline_rest(symbol, period, startTime=0, endTime=0, size=1000): try: url = 'https://api.binance.com/api/v1/klines?symbol=%s&interval=%s&limit=%d' if startTime == 0 or endTime == 0: url = url % (symbol, period, size) else: url = (url + '&startTime=%d&endTime=%d') % (symbol, period, size, startTime, endTime) # 测试接口是否正常 # url = 'https://api.binance.com/api/v1/time' # url = 'http://ip.cn' mysqlutil.log('binance', url) socket.setdefaulttimeout(20) headers = { "Content-type": "application/x-www-form-urlencoded", 'User-Agent': "Mozilla/5.0 (Windows NT 6.1; rv:32.0) Gecko/20100101 Firefox/32.0", } req = request.Request(url, None, headers) response = request.urlopen(req) mysqlutil.log('binance', 'response code >>>', response.code) content = response.read().decode('utf-8') content = json.loads(content) mysqlutil.log('binance', 'data len >>>', len(content)) if len(content) > 0: mysqlutil.log('binance', 'data >>>') mysqlutil.log('binance', ' ', '[' + str(content[0]) + ', ...]') mysqlutil.log('binance', 'data start >>>', util.getLocaleDateStrBy13(content[0][0])) mysqlutil.log( 'binance', 'data end >>>', util.getLocaleDateStrBy13(content[len(content) - 1][0])) return content except: util.printExcept() time.sleep(61) return False
def get_okex_kline_history(period): global runCount, count, total, done, doneToken, pendingPeriods, periods startDate = datetime.datetime.now() db = mysqlutil.init() cursor = db.cursor() try: symbols = CommonApi.get_symbols(TABLE, format='dict') symbolCount = 0 for sk in symbols: symbol = symbols[sk] base = symbols[sk]['base'] quote = symbols[sk]['quote'] symbolCount += 1 mysqlutil.log('okex', startDate, period, 'symbol >>>', sk, symbolCount, '/', len(symbols)) # 获取最近的一条数据的时间戳 sql = ''' SELECT ts FROM xcm_okex_kline WHERE symbol=%s AND period=%s ORDER BY ts DESC LIMIT 0,1 ''' cursor.execute(sql, (sk, period)) rows = cursor.fetchall() lastTimestamp = 0 if len(rows) == 0: since = '' else: lastTimestamp = rows[0][0] # mysqlutil.log('okex', startDate, period, 'last timestamp >>>', str(rows[0][0])) mysqlutil.log('okex', startDate, period, 'last datetime >>>', util.getLocaleDateStrBy13(rows[0][0])) since = str(rows[0][0] + get_period_interval(period)) mysqlutil.log('okex', startDate, period, 'period >>>', period) if since != '': mysqlutil.log('okex', startDate, period, 'since datetime >>>', util.getLocaleDateStrBy13(int(since))) kdata = request_okex_kline_rest(sk, period, since) if kdata is False: continue elif 'error_code' in kdata: mysqlutil.log('okex', startDate, period, '\033[0;30;41m error_code >>>', kdata['error_code'], '\033[0m\n') continue else: mysqlutil.log('okex', startDate, period, 'kdata len >>>', len(kdata)) mysqlutil.log('okex', startDate, period, 'kdata >>>', kdata) mysqlutil.log('okex', startDate, period, 'kdata start datetime >>>', util.getLocaleDateStrBy13(kdata[0][0])) newTimestamps = [] for k in kdata: newTimestamp = k[0] # mysqlutil.log('okex', 'newTimestamp >>>', newTimestamp) # TODO: 之所以重复,似乎是同一个时间间隔,有新的交易发生。需要考虑将此重复时间的数据更新到数据库中。 if lastTimestamp == newTimestamp or newTimestamp in newTimestamps: mysqlutil.log('okex', startDate, period, '\033[0;30;47m duplicated timestamp >>>', k[0], '\033[0m') mysqlutil.log('okex', startDate, period, '\033[0;30;47m duplicated timestamp >>>', util.getLocaleDateStrBy13(k[0]), '\033[0m') continue newTimestamps.append(newTimestamp) nowTime = time.time() nowTime *= 10000000 sql = ''' INSERT INTO xcm_okex_kline ( _id, symbol,base,quote,period, ts,open,high,low,close,vol) VALUES ( %s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s)''' param = (nowTime, sk, base, quote, period, k[0], k[1], k[2], k[3], k[4], k[5]) cursor.execute(sql, param) count += 1 total += 1 db.commit() mysqlutil.log('okex', startDate, period, 'begin date >>>', beginDate) mysqlutil.log('okex', startDate, period, 'start date >>>', startDate) mysqlutil.log('okex', startDate, period, 'current date >>>', datetime.datetime.now()) mysqlutil.log('okex', startDate, period, 'insert done >>> {:,}'.format(count)) mysqlutil.log('okex', startDate, period, 'period done >>>', done) mysqlutil.log('okex', startDate, period, 'period doneToken >>>', doneToken) mysqlutil.log('okex', startDate, period, 'period pending >>>', pendingPeriods) mysqlutil.log('okex', startDate, period, 'total >>> {:,}'.format(total)) mysqlutil.log('okex', 'runCount >>>', runCount, '\n') done.append(period) doneToken[period] = 1 pendingPeriods.discard(period) except: util.printExcept() finally: cursor.close() db.close() mysqlutil.log('okex', startDate, period, 'start date >>>', startDate) mysqlutil.log('okex', startDate, period, 'end date >>>', datetime.datetime.now()) mysqlutil.log('okex', startDate, period, 'period done >>>', done) mysqlutil.log('okex', startDate, period, 'period doneToken >>>', doneToken) mysqlutil.log('okex', startDate, period, 'period pending >>>', pendingPeriods) mysqlutil.log('okex', startDate, period, 'total >>> {:,}'.format(total)) if len(done) == len(periods): runCount += 1 mysqlutil.log('okex', 'runCount >>>', runCount, '\n')
def get_ticker_history(): db = mysqlutil.init() cursor = db.cursor() try: sql = 'SELECT symbol, base, quote FROM xcm_bigone_symbol' cursor.execute(sql) symbols = cursor.fetchall() symbolCount = 0 for r in symbols: symbol = r[0] base = r[1] quote = r[2] symbolCount += 1 ticker = request_ticker_rest(symbol) timestamp = request_ping_rest() mysqlutil.log('bigone', symbol, 'symbol precentage >>>', symbolCount, '/', len(symbols)) mysqlutil.log('bigone', symbol, ' timestamp >>>', util.getLocaleDateStrDefault(timestamp / 1000000000)) nowTime = time.time() * 10000000 sql = ''' INSERT INTO xcm_bigone_ticker_history ( _id, symbol, base, quote, ts, open, high, low, close, volume, daily_change, daily_change_perc ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )''' param = (nowTime, symbol, base, quote, timestamp, ticker['open'], ticker['high'], ticker['low'], ticker['close'], ticker['volume'], ticker['daily_change'], ticker['daily_change_perc']) cursor.execute(sql, param) db.commit() global total total += 1 mysqlutil.log('bigone', '\033[0;30;43m', symbol, ' begin date >>>', beginDate, '\033[0m') mysqlutil.log('bigone', '\033[0;30;43m', symbol, 'current date >>>', str(datetime.datetime.now()), ' \033[0m') mysqlutil.log('bigone', '\033[0;30;42m', symbol, ' total >>> {:,}'.format(total), ' \033[0m') except: util.printExcept() finally: cursor.close() db.close()
def get_kline_history(period): global runCount, count, total, done, doneToken, pendingPeriods startDate = str(datetime.datetime.now()) db = mysqlutil.init() cursor = db.cursor() try: sql = 'SELECT symbol, base_asset, quote_asset FROM xcm_binance_symbol' cursor.execute(sql) symbols = cursor.fetchall() symbolCount = 0 nowTime13 = int(time.time() * 1000) for r in symbols: symbol = r[0] base = r[1] quote = r[2] symbolCount += 1 mysqlutil.log('binance', startDate, period, 'symbol >>>', symbol, symbolCount, '/', len(symbols)) # 获取最近的一条数据的时间戳 sql = ''' SELECT open_time FROM xcm_binance_kline_history WHERE symbol=%s AND period=%s ORDER BY open_time DESC LIMIT 0,1 ''' cursor.execute(sql, (symbol, period)) rows = cursor.fetchall() lastTimestamp = 0 if len(rows) == 0: since = 1 else: lastTimestamp = rows[0][0] # mysqlutil.log('binance', startDate, period, 'last timestamp >>>', str(rows[0][0])) mysqlutil.log('binance', startDate, period, 'last datetime >>>', util.getLocaleDateStrBy13(rows[0][0])) since = rows[0][0] + get_period_interval(period) * 1000 mysqlutil.log('binance', startDate, period, 'period >>>', period) if since != '': mysqlutil.log('binance', startDate, period, 'since datetime >>>', util.getLocaleDateStrBy13(int(since))) kdata = request_kline_rest(symbol, period, since, nowTime13) if kdata is False: continue elif 'error_code' in kdata: mysqlutil.log('binance', startDate, period, '\033[0;30;41m error_code >>>', kdata['error_code'], '\033[0m\n') continue else: mysqlutil.log('binance', startDate, period, 'kdata len >>>', len(kdata)) if len(kdata): mysqlutil.log('binance', startDate, period, 'kdata >>>') mysqlutil.log('binance', ' ', '[' + str(kdata[0]) + ', ...]') mysqlutil.log('binance', startDate, period, 'kdata start datetime >>>', util.getLocaleDateStrBy13(kdata[0][0])) newTimestamps = [] for k in kdata: newTimestamp = k[0] # mysqlutil.log('binance', 'newTimestamp >>>', newTimestamp) if lastTimestamp == newTimestamp or newTimestamp in newTimestamps: mysqlutil.log('binance', startDate, period, '\033[0;30;47m duplicated timestamp >>>', k[0], '\033[0m') mysqlutil.log('binance', startDate, period, '\033[0;30;47m duplicated timestamp >>>', util.getLocaleDateStrBy13(k[0]), '\033[0m') continue newTimestamps.append(newTimestamp) nowTime = time.time() nowTime *= 10000000 sql = ''' INSERT INTO xcm_binance_kline_history ( _id, symbol,base_asset,quote_asset,period, open_time,open,high,low,close,volume,close_time, quote_asset_volume,num_of_trades,buy_base_asset,buy_quote_asset,f_ignore) VALUES ( %s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,%s, %s,%s,%s,%s,%s)''' param = (nowTime, symbol, base, quote, period, k[0], k[1], k[2], k[3], k[4], k[5], k[6], k[7], k[8], k[9], k[10], k[11]) # print('binance', 'sql >>>', sql%param) cursor.execute(sql, param) count += 1 total += 1 db.commit() mysqlutil.log('binance', '\033[0;30;43m', startDate, period, 'begin date >>>', beginDate, '\033[0m') mysqlutil.log('binance', '\033[0;30;43m', startDate, period, 'start date >>>', startDate, '\033[0m') mysqlutil.log('binance', '\033[0;30;43m', startDate, period, 'current date >>>', str(datetime.datetime.now()), '\033[0m') mysqlutil.log('binance', startDate, period, 'insert done >>> {:,}'.format(count)) mysqlutil.log('binance', startDate, period, 'period done >>>', str(done)) mysqlutil.log('binance', startDate, period, 'period doneToken >>>', str(doneToken)) mysqlutil.log('binance', startDate, period, 'period pending >>>', str(pendingPeriods)) mysqlutil.log('binance', '\033[0;30;42m', startDate, period, 'total >>> {:,}'.format(total), '\033[0m') mysqlutil.log('binance', 'runCount >>>', runCount, '\n') done.append(period) doneToken[period] = 1 pendingPeriods.discard(period) except: util.printExcept() finally: cursor.close() db.close() mysqlutil.log('binance', '\033[0;30;43m', startDate, period, 'begin date >>>', beginDate, '\033[0m') mysqlutil.log('binance', '\033[0;30;43m', startDate, period, 'start date >>>', startDate, '\033[0m') mysqlutil.log('binance', '\033[0;30;43m', startDate, period, 'end date >>>', str(datetime.datetime.now()), '\033[0m') mysqlutil.log('binance', startDate, period, 'period done >>>', done) mysqlutil.log('binance', startDate, period, 'period doneToken >>>', str(doneToken)) mysqlutil.log('binance', startDate, period, 'period pending >>>', str(pendingPeriods)) mysqlutil.log('binance', '\033[0;30;42m', startDate, period, 'total >>> {:,}'.format(total), ' \033[0m') if len(done) == len(periods): runCount += 1 mysqlutil.log('binance', 'runCount >>>', runCount, '\n')
def save_ticker(): startTimestamp = time.time() symbols = cfcoin.get_symbols() count = 0 symbolCount = 0 for s in symbols: symbol = s[0] base = s[1] quote = s[2] # 打印当前处理的交易对和处理进度 symbolCount += 1 # mysqlutil.log('fcoin', '\033[0;30;42m', startDate, 'symbol >>>', symbol, symbolCount, '/', len(symbols), # '\033[0m') util.dprint('\033[0;30;42m', startDate, 'symbol >>>', symbol, symbolCount, '/', len(symbols), '\033[0m') try: ticker = api.market.get_ticker(symbol) except: util.printExcept(target='get-fcoin-ticker > get_ticker') time.sleep(10) continue # 访问太过频繁,超过了每 10 秒钟 100 次的限制,先暂停休眠 10 秒钟。 if ticker['status'] == 429: time.sleep(10) if ticker['status'] != 0: # 打印请求返回的错误信息 mysqlutil.log('fcoin', startDate, symbol, 'get_ticker error >>>', ticker) continue ticker = ticker['data']['ticker'] save_ticker_record(symbol, base, quote, ticker) count += 1 if count % 10 == 0: util.sleep(1, False) print() mysqlutil.log('fcoin', 'complete datetime >>>', datetime.datetime.now()) mysqlutil.log('fcoin', ' time elapsed >>>', util.timeElapsed(startTimestamp)) mysqlutil.log('fcoin', ' exCount >>>', exCount, 'runCount >>>', runCount) mysqlutil.log('fcoin', ' startDate >>>', startDate) mysqlutil.log('fcoin', ' total elapsed >>>', util.timeElapsed(startDate.timestamp()))
from chengutil import mysqlutil mysqlutil.log('okex', 'test log 1', 'test log 2', 'test log 3')
def get_kline_history(period): global runCount, count, total, done, doneToken, pendingPeriods startDate = str(datetime.datetime.now()) db = mysqlutil.init() cursor = db.cursor() try: sql = 'SELECT symbol, base, quote FROM xcm_gate_symbol' cursor.execute(sql) symbols = cursor.fetchall() symbolCount = 0 nowTime13 = int(time.time() * 1000) for r in symbols: symbol = r[0] base = r[1] quote = r[2] symbolCount += 1 mysqlutil.log('gate', startDate, period, 'symbol >>>', symbol, symbolCount, '/', len(symbols)) # 获取最近的一条数据的时间戳 sql = ''' SELECT ts FROM xcm_gate_kline_history WHERE symbol=%s AND period=%s ORDER BY ts DESC LIMIT 0,1 ''' cursor.execute(sql, (symbol, period)) rows = cursor.fetchall() lastTimestamp = 0 if len(rows) == 0: since = util.getTimeStampReturn13('2017-12-01 00:00:00') else: lastTimestamp = rows[0][0] # mysqlutil.log('gate', startDate, period, 'last timestamp >>>', str(rows[0][0])) mysqlutil.log('gate', startDate, period, 'last datetime >>>', util.getLocaleDateStrBy13(rows[0][0])) since = rows[0][0] + get_period_interval(period) mysqlutil.log('gate', startDate, period, 'period >>>', period) if since != '': mysqlutil.log('gate', startDate, period, 'since datetime >>>', util.getLocaleDateStrBy13(int(since))) kdata = request_kline_rest(symbol, period, since) if kdata is False: continue elif 'code' in kdata: mysqlutil.log('gate', startDate, period, '\033[0;30;41m code >>>', kdata['code'], kdata['message'], '\033[0m\n') continue elif 'result' in kdata and kdata['result']: kdata = kdata['data'] mysqlutil.log('gate', startDate, period, 'kdata len >>>', len(kdata)) if len(kdata): mysqlutil.log('gate', startDate, period, 'kdata >>>') mysqlutil.log('gate', ' ', '[' + str(kdata[0]) + ', ...]') mysqlutil.log('gate', startDate, period, 'kdata start datetime >>>', util.getLocaleDateStrBy13(int(kdata[0][0]))) newTimestamps = [] for k in kdata: newTimestamp = k[0] # mysqlutil.log('gate', 'newTimestamp >>>', newTimestamp) if lastTimestamp == newTimestamp or newTimestamp in newTimestamps: mysqlutil.log('gate', startDate, period, '\033[0;30;47m duplicated timestamp >>>', k[0], '\033[0m') mysqlutil.log('gate', startDate, period, '\033[0;30;47m duplicated timestamp >>>', util.getLocaleDateStrBy13(k[0]), '\033[0m') continue newTimestamps.append(newTimestamp) nowTime = time.time() nowTime *= 10000000 sql = ''' INSERT INTO xcm_gate_kline_history ( _id, symbol, base, quote, period, ts, volume, close, high, low, open) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''' param = (nowTime, symbol, base, quote, period, k[0], k[1], k[2], k[3], k[4], k[5]) try: # print('gate', 'sql >>>', sql%param) cursor.execute(sql, param) count += 1 total += 1 except pymysql.err.IntegrityError: # util.printExcept() mysqlutil.log('gate', startDate, period, '\033[0;30;41m pymysql.err.IntegrityError', '\033[0m\n') mysqlutil.log('gate', startDate, period, sql % param) db.commit() mysqlutil.log('gate', '\033[0;30;43m', startDate, period, 'begin date >>>', beginDate, '\033[0m') mysqlutil.log('gate', '\033[0;30;43m', startDate, period, 'start date >>>', startDate, '\033[0m') mysqlutil.log('gate', '\033[0;30;43m', startDate, period, 'current date >>>', str(datetime.datetime.now()), '\033[0m') mysqlutil.log('gate', startDate, period, 'insert done >>> {:,}'.format(count)) mysqlutil.log('gate', startDate, period, 'period done >>>', str(done)) mysqlutil.log('gate', startDate, period, 'period doneToken >>>', str(doneToken)) mysqlutil.log('gate', startDate, period, 'period pending >>>', str(pendingPeriods)) mysqlutil.log('gate', '\033[0;30;42m', startDate, period, 'total >>> {:,}'.format(total), '\033[0m') mysqlutil.log('gate', 'runCount >>>', runCount, '\n') done.append(period) doneToken[period] = 1 pendingPeriods.discard(period) except: util.printExcept() finally: cursor.close() db.close() mysqlutil.log('gate', '\033[0;30;43m', startDate, period, 'begin date >>>', beginDate, '\033[0m') mysqlutil.log('gate', '\033[0;30;43m', startDate, period, 'start date >>>', startDate, '\033[0m') mysqlutil.log('gate', '\033[0;30;43m', startDate, period, 'end date >>>', str(datetime.datetime.now()), '\033[0m') mysqlutil.log('gate', startDate, period, 'period done >>>', done) mysqlutil.log('gate', startDate, period, 'period doneToken >>>', str(doneToken)) mysqlutil.log('gate', startDate, period, 'period pending >>>', str(pendingPeriods)) mysqlutil.log('gate', '\033[0;30;42m', startDate, period, 'total >>> {:,}'.format(total), ' \033[0m') if len(done) == len(periods): runCount += 1 mysqlutil.log('gate', 'runCount >>>', runCount, '\n')
'3d': 0, '1w': 0, '1M': 0 } pendingPeriods = { '1m', '3m', '5m', '15m', '30m', '1h', '2h', '4h', '6h', '8h', '12h', '1d', '3d', '1w', '1M' } periods = [ '1m', '3m', '5m', '15m', '30m', '1h', '2h', '4h', '6h', '8h', '12h', '1d', '3d', '1w', '1M' ] if __name__ == '__main__': global beginDate beginDate = str(datetime.datetime.now()) currentRunCount = runCount get_kline_history_by_peroid() while True: if currentRunCount == runCount: time.sleep(60) else: mysqlutil.log('binance', str(datetime.datetime.now()), 'over') currentRunCount = runCount get_kline_history_by_peroid() # break
def get_ticker_history(): db = mysqlutil.init() cursor = db.cursor() try: sql = 'SELECT pair FROM xcm_bitfinex_symbol' cursor.execute(sql) symbols = cursor.fetchall() global symbolCount, runTime symbolCount = 0 runTime = 0 for r in symbols: symbol = r[0] mysqlutil.log('bitfinex', symbol, ' runTime >>>', runTime) # if runTime == 5: # runTime = 0 # sleep(10) ticker = request_ticker_rest(symbol) if ticker == False: ticker = request_ticker_rest(symbol) # runTime += 1 sleep(3) symbolCount += 1 mysqlutil.log('bitfinex', symbol, 'symbol precentage >>>', symbolCount, '/', len(symbols)) mysqlutil.log('bitfinex', symbol, 'ticker timestamp >>>', ticker['timestamp']) mysqlutil.log('bitfinex', symbol, 'ticker timestamp >>>', util.getLocaleDateStrDefault(int(float(ticker['timestamp'])))) nowTime = time.time() * 10000000 sql = ''' INSERT INTO xcm_bitfinex_ticker_history ( _id, pair, mid, bid, ask, last_price, low, high, volume, ts ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )''' param = (nowTime, symbol, ticker['mid'], ticker['bid'], ticker['ask'], ticker['last_price'], ticker['low'], ticker['high'], ticker['volume'], ticker['timestamp']) cursor.execute(sql, param) db.commit() global total total += 1 mysqlutil.log('bitfinex', '\033[0;30;43m', symbol, ' begin date >>>', beginDate, '\033[0m') mysqlutil.log('bitfinex', '\033[0;30;43m', symbol, 'current date >>>', str(datetime.datetime.now()), ' \033[0m') mysqlutil.log('bitfinex', '\033[0;30;42m', symbol, ' total >>> {:,}'.format(total), '\033[0m') except: util.printExcept() finally: cursor.close() db.close()
'6d': 0, '7d': 0 } pendingPeriods = { '1m', '5m', '10m', '15m', '20m', '30m', '1h', '2h', '3h', '4h', '6h', '8h', '1d', '2d', '3d', '4d', '5d', '6d', '7d' } periods = [ '1m', '5m', '10m', '15m', '20m', '30m', '1h', '2h', '3h', '4h', '6h', '8h', '1d', '2d', '3d', '4d', '5d', '6d', '7d' ] if __name__ == '__main__': global beginDate beginDate = str(datetime.datetime.now()) currentRunCount = runCount get_kline_history_by_peroid() while True: if currentRunCount == runCount: time.sleep(60 * 60) else: mysqlutil.log('gate', str(datetime.datetime.now()), 'over') currentRunCount = runCount get_kline_history_by_peroid() # request_kline_rest('btc_usdt', '1m', util.getTimeStamp('2018-01-01 00:00:00'))
def get_kline(): for p in PERIODS: save_kline(p) mysqlutil.log(TABLE, 'get_kline', 'time elapsed >>>', util.timeElapsed(startDate.timestamp()))
def request_kline_rest(symbol, period, since=0, size=1000): try: url = 'http://api.zb.cn/data/v1/kline?market=%s&type=%s&since=%s&size=%s' % ( symbol, period, since, size) mysqlutil.log('zb', url) socket.setdefaulttimeout(20) headers = { "Content-type": "application/x-www-form-urlencoded", 'User-Agent': "Mozilla/5.0 (Windows NT 6.1; rv:32.0) Gecko/20100101 Firefox/32.0", } req = request.Request(url, None, headers) response = request.urlopen(req) mysqlutil.log('zb', 'response code >>>', response.code) content = response.read().decode('utf-8') # mysqlutil.log('zb', 'content >>>', content) content = json.loads(content) data = content['data'] mysqlutil.log('zb', 'data symbol >>>', content['symbol']) mysqlutil.log('zb', 'data moneyType >>>', content['moneyType']) mysqlutil.log('zb', 'data len >>>', len(data)) if len(data) > 0: mysqlutil.log('zb', 'data >>>') mysqlutil.log('zb', ' ', '[' + str(data[0]) + ', ...]') mysqlutil.log('zb', 'data start >>>', util.getLocaleDateStrBy13(data[0][0])) mysqlutil.log('zb', 'data end >>>', util.getLocaleDateStrBy13(data[len(data) - 1][0])) return content except: util.printExcept() time.sleep(61) return False
def save_ticker(): global exCount, dupCount, timeErrCount startTimestamp = time.time() db = mysqlutil.init() cursor = db.cursor() try: nowTime = time.time() * 10000000 symbols = chitbtc.get_symbols('dict') tickers = chitbtc.get_ticker() # 访问太过频繁,超过了每 1 秒钟 100 次的限制,先暂停休眠 1 秒钟。 if 'error' in tickers and tickers['error']['code'] == 429: time.sleep(1) if 'error' in tickers: # 打印请求返回的错误信息 mysqlutil.log(TABLE, startDate, 'save_ticker error >>>', tickers) exCount += 1 sql = ''' INSERT INTO xcm_hitbtc_ticker ( _id, symbol, base, quote, ask, bid, open, high, low, last, volume, volumeQuote, ts) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ''' tickerCount = 0 dupCurCount = 0 for t in tickers: symbol = t['symbol'] base = symbols[symbol]['base'] quote = symbols[symbol]['quote'] # 转换时间为时间戳 ts = util.getTimeStampReturn13(t['timestamp'], "%Y-%m-%dT%H:%M:%S.%fZ") if ts == False: timeErrCount += 1 util.printExcept(target='get-' + TABLE + '-kline > get_last_timestamp', msg=str(t)) continue regex = r'.+?\.([0-9]{3})Z' matchObj = re.search(regex, t['timestamp'], re.I) ms = matchObj.group(1) ts += int(ms) primary = symbol + '-' + str(ts) + '-' + base + '-' + quote + '-' if primary in primaries: dupCount += 1 dupCurCount += 1 continue primaries.append(primary) param = (nowTime, symbol, base, quote, t['ask'], t['bid'], t['open'], t['high'], t['low'], t['last'], t['volume'], t['volumeQuote'], ts) try: cursor.execute(sql, param) db.commit() except pymysql.err.IntegrityError: # ex_type, ex_val, ex_stack = sys.exc_info() # print('EX_VAL: ' + str(ex_val)) dupCount += 1 dupCurCount += 1 exCount += 1 continue tickerCount += 1 util.dprint(startDate, symbol, 'tickerCount:', tickerCount, '/ len:', len(tickers), '/ dupCurCount:', dupCurCount, '/ dupCount:', dupCount) util.dprint(startDate, datetime.datetime.now(), 'completed', '\nnewCount:', tickerCount, ' duplicateCurrentCount:', dupCurCount, ' exCount:', exCount, ' timeErrCount:', timeErrCount, ' runCount:', runCount, ' timeSpan:', util.timeElapsed(startTimestamp), ' totalSpan:', util.timeElapsed(startDate.timestamp())) print() return True except: exCount += 1 util.printExcept(target='get-' + TABLE + '-kline > save_ticker') finally: cursor.close() db.close() return False
def get_kline_history(period): global runCount, count, total, done, doneToken, pendingPeriods startDate = str(datetime.datetime.now()) db = mysqlutil.init() cursor = db.cursor() try: sql = 'SELECT symbol, base, quote FROM xcm_zb_symbol' cursor.execute(sql) symbols = cursor.fetchall() symbolCount = 0 nowTime13 = int(time.time() * 1000) # 初始化线程标签 threadLock = _thread.allocate_lock() threadLock.acquire() runThreadLock[period]['lock'] = threadLock threadTime = int(time.time() * 1000) runThreadTime.append(threadTime) runThreadLock[period]['time'] = threadTime while wait_all_thread_running() == False: time.sleep(5) print('ready to run >>>', period) for r in symbols: symbol = r[0] base = r[1] quote = r[2] symbolCount += 1 mysqlutil.log('zb', startDate, period, 'symbol >>>', symbol, symbolCount, '/', len(symbols)) # 获取最近的一条数据的时间戳 sql = ''' SELECT ts FROM xcm_zb_kline_history WHERE symbol=%s AND period=%s ORDER BY ts DESC LIMIT 0,1 ''' cursor.execute(sql, (symbol, period)) rows = cursor.fetchall() lastTimestamp = 0 if len(rows) == 0: since = util.getTimeStampReturn13('2017-12-01 00:00:00') else: lastTimestamp = rows[0][0] # mysqlutil.log('zb', startDate, period, 'last timestamp >>>', str(rows[0][0])) mysqlutil.log('zb', startDate, period, 'last datetime >>>', util.getLocaleDateStrBy13(rows[0][0])) since = rows[0][0] + get_period_interval_return_min( period) * 1000 mysqlutil.log('zb', startDate, period, 'period >>>', period) mysqlutil.log('zb', startDate, period, 'since datetime >>>', util.getLocaleDateStrBy13(int(since))) while True: print('thread period lock >>>', period, runThreadLock[period]['lock'].locked()) if (runThreadLock[period]['lock'].locked() == True) and (lock_and_run(period, threadTime)): kdata = request_kline_rest(symbol, period, since) print('\033[1;31;42m thread release >>>', period, '\033[0m') runThreadLock[period]['lock'].release() # 更新线程时间 runThreadTime.remove(threadTime) threadTime = int(time.time() * 1000) runThreadTime.append(threadTime) runThreadLock[period]['time'] = threadTime print('runThreadTime >>>', runThreadTime) break else: # 给线程重新加锁 if runThreadLock[period]['lock'].locked() == False: threadLock = _thread.allocate_lock() threadLock.acquire() runThreadLock[period]['lock'] = threadLock time.sleep(10) if kdata is False: continue elif 'code' in kdata: mysqlutil.log('zb', startDate, period, '\033[0;30;41m error code >>>', kdata['code'], 'message >>>', kdata['message'], '\033[0m\n') continue elif 'error' in kdata: mysqlutil.log('zb', startDate, period, '\033[0;30;41m error >>>', kdata['error'], '\033[0m\n') continue elif 'result' in kdata: mysqlutil.log('zb', startDate, period, '\033[0;30;41m result >>>', kdata['result'], 'message >>>', kdata['message'], '\033[0m\n') continue elif 'data' in kdata: kdata = kdata['data'] mysqlutil.log('zb', startDate, period, 'kdata len >>>', len(kdata)) if len(kdata) > 0: mysqlutil.log('zb', startDate, period, 'kdata >>>') mysqlutil.log('zb', ' ', '[' + str(kdata[0]) + ', ...]') mysqlutil.log('zb', startDate, period, 'kdata start datetime >>>', util.getLocaleDateStrBy13(kdata[0][0])) else: mysqlutil.log('zb', startDate, period, 'kdata >>>', kdata) newTimestamps = [] for k in kdata: newTimestamp = k[0] # mysqlutil.log('zb', 'newTimestamp >>>', newTimestamp) if lastTimestamp == newTimestamp or newTimestamp in newTimestamps: mysqlutil.log('zb', startDate, period, '\033[0;30;47m duplicated timestamp >>>', k[0], '\033[0m') mysqlutil.log('zb', startDate, period, '\033[0;30;47m duplicated timestamp >>>', util.getLocaleDateStrBy13(k[0]), '\033[0m') continue newTimestamps.append(newTimestamp) nowTime = time.time() nowTime *= 10000000 sql = ''' INSERT INTO xcm_zb_kline_history ( _id, symbol, base, quote, period, ts, open, high, low, close, amount) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''' param = (nowTime, symbol, base, quote, period, k[0], k[1], k[2], k[3], k[4], k[5]) # print('sql >>>', sql%param) cursor.execute(sql, param) count += 1 total += 1 db.commit() mysqlutil.log('zb', '\033[0;30;43m', startDate, period, 'begin date >>>', beginDate, '\033[0m') mysqlutil.log('zb', '\033[0;30;43m', startDate, period, 'start date >>>', startDate, '\033[0m') mysqlutil.log('zb', '\033[0;30;43m', startDate, period, 'current date >>>', str(datetime.datetime.now()), '\033[0m') mysqlutil.log('zb', startDate, period, 'insert done >>> {:,}'.format(count)) mysqlutil.log('zb', startDate, period, 'period done >>>', str(done)) mysqlutil.log('zb', startDate, period, 'period doneToken >>>', str(doneToken)) mysqlutil.log('zb', startDate, period, 'period pending >>>', str(pendingPeriods)) mysqlutil.log('zb', '\033[0;30;42m', startDate, period, 'total >>> {:,}'.format(total), '\033[0m') mysqlutil.log('zb', 'runCount >>>', runCount, '\n') done.append(period) doneToken[period] = 1 pendingPeriods.discard(period) except: util.printExcept() finally: cursor.close() db.close() mysqlutil.log('zb', '\033[0;30;43m', startDate, period, 'begin date >>>', beginDate, '\033[0m') mysqlutil.log('zb', '\033[0;30;43m', startDate, period, 'start date >>>', startDate, '\033[0m') mysqlutil.log('zb', '\033[0;30;43m', startDate, period, 'end date >>>', str(datetime.datetime.now()), '\033[0m') mysqlutil.log('zb', startDate, period, 'period done >>>', done) mysqlutil.log('zb', startDate, period, 'period doneToken >>>', str(doneToken)) mysqlutil.log('zb', startDate, period, 'period pending >>>', str(pendingPeriods)) mysqlutil.log('zb', '\033[0;30;42m', startDate, period, 'total >>> {:,}'.format(total), ' \033[0m') if len(done) == len(periods): runCount += 1 mysqlutil.log('zb', 'runCount >>>', runCount, '\n')
def save_ticker(): global exCount, dupCount, timeErrCount startTimestamp = time.time() db = mysqlutil.init() cursor = db.cursor() try: nowTime = time.time() * 10000000 symbols = CommonApi.get_symbols(TABLE, 'dict') tickers = CommonApi.get_ticker('https://api.huobi.pro/market/tickers') if tickers['status'] == 'error': # 打印请求返回的错误信息 mysqlutil.log(TABLE, startDate, 'save_ticker error >>>', tickers) exCount += 1 sql = 'INSERT INTO xcm_' + TABLE + '_ticker (' sql += ''' _id, symbol, base, quote, open, high, low, close, amount, vol, count, ts) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ''' tickerCount = 0 dupCurCount = 0 ts = tickers['ts'] tickers = tickers['data'] for t in tickers: symbol = t['symbol'] # 去除非正常的交易对 huobi10/hb10 if symbol == 'huobi10' or symbol == 'hb10': continue base = symbols[symbol]['base'] quote = symbols[symbol]['quote'] primary = symbol + '-' + str(ts) + '-' + base + '-' + quote + '-' if primary in primaries: dupCount += 1 dupCurCount += 1 continue primaries.append(primary) param = (nowTime, symbol, base, quote, t['open'], t['high'], t['low'], t['close'], t['amount'], t['vol'], t['count'], ts) try: cursor.execute(sql, param) db.commit() except pymysql.err.IntegrityError: dupCount += 1 dupCurCount += 1 exCount += 1 continue tickerCount += 1 util.dprint(startDate, symbol, 'tickerCount:', tickerCount, '/ len:', len(tickers), '/ dupCurCount:', dupCurCount, '/ dupCount:', dupCount) util.dprint(startDate, datetime.datetime.now(), 'completed', '\nnewCount:', tickerCount, ' duplicateCurrentCount:', dupCurCount, ' exCount:', exCount, ' timeErrCount:', timeErrCount, ' runCount:', runCount, ' timeSpan:', util.timeElapsed(startTimestamp), ' totalSpan:', util.timeElapsed(startDate.timestamp())) print() return True except: exCount += 1 util.printExcept(target='get-' + TABLE + '-kline > save_ticker') finally: cursor.close() db.close() return False