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 run(*args): # 批量订阅所有的交易对K线数据 subscribe = [] db = mysqlutil.init() cursor = db.cursor() try: sql = 'SELECT symbol FROM xcm_okex_symbol' cursor.execute(sql) rows = cursor.fetchall() for r in rows: subscribe.append({ 'event': 'addChannel', 'channel': 'ok_sub_spot_' + r[0] + '_kline_' + self.myPeriod }) except: util.printExcept() finally: cursor.close() db.close() print('subscribe >>>', json.dumps(subscribe)) print('send subscribe') ws.send(json.dumps(subscribe))
def _get_recommend_currency(): db = mysqlutil.init() cursor = db.cursor() currencies = [] try: sql = ''' SELECT * FROM `xcm_vxn_currency_info` WHERE currency in ('btc','eth','xrp','bch','ltc','xlm') ''' cursor.execute(sql, ()) rows = cursor.fetchall() for r in rows: currencies.append(_convert_currency(r)) except: util.printExcept() finally: db.close() cursor.close() return currencies
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 get_scope_percent(): db = mysqlutil.init() cursor = db.cursor() currencies = [] try: sql = ''' SELECT scope, count(scope) FROM (SELECT ROUND(price_scope, 0) scope FROM `xcm_vxn_currency_info`) AS t GROUP BY scope ''' print(sql) cursor.execute(sql, ()) rows = cursor.fetchall() for r in rows: currencies.append({'scope': r[0], 'count': r[1]}) except: util.printExcept() finally: db.close() cursor.close() return currencies
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 save_kline_record(symbol, base, quote, period, krecord, table, insertSql, klineFields): db = mysqlutil.init() cursor = db.cursor() try: nowTime = time.time() * 10000000 sql = insertSql param = (nowTime, symbol, base, quote, period) for f in klineFields: if type([f]) == list and len(f) == 2: param += (util.getTimeStamp(krecord[f[0]], f[1]), ) else: param += (krecord[f], ) cursor.execute(sql, param) db.commit() return True except: util.printExcept(target='get-' + table + '-kline > save_krecord') return False finally: cursor.close() db.close()
def main(): db = mysqlutil.init() cursor = db.cursor() try: usdcny = get_sina_rate_usdcny() sql = '''INSERT INTO `xcm_exchange_rate` (base_currency, quote_currency, rate, create_time) VALUES (%s, %s, %s, %s)''' now = int(time.time()) cursor.execute(sql,('usd', 'cny', usdcny,now)) rowcount = cursor.rowcount if rowcount == 0: print('\033[0;30;41m insert exchange rate result >>> \033[0m', rowcount) else: print('\033[0;30;42m insert exchange rate result >>> \033[0m', rowcount) db.commit() except: print('\033[0;30;41m main except >>> \033[0m') filename = os.path.basename(sys.argv[0]).split(".")[0] util.printExcept(filename) finally: cursor.close() db.close()
def logToDB(tableName, param): db = init() cursor = db.cursor() logContent = '' try: for s in param: s = str(s) logContent += s + ' ' if logContent != '': nowTime = time.time() * 10000000 sql = ''' INSERT INTO xcm_log_%s (ts,content) VALUES ('%s','%s')''' % ( tableName, nowTime, logContent.replace('\'', '\\\'')) cursor.execute(sql, ()) db.commit() return logContent except: util.printExcept() finally: cursor.close() db.close() return False
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()))
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 on_message(self, ws, message): # print('date >>>', datetime.datetime.now()) # print(message) data = json.loads(message) if 'event' not in data: pass db = mysqlutil.init() cursor = db.cursor() # print('data len >>>', len(data)) for d in data: try: # 将K线数据(kline)保存在数据库中 if 'channel' in d and d['channel'].find('_kline_') != -1: self.count += 1 print('count >>>', self.myPeriod, self.count) channel = d['channel'] channel = channel.split('_') base = channel[3] quote = channel[4] symbol = base + '_' + quote period = channel[6] nowTime = time.time() nowTime *= 10000000 klines = d['data'] for k in klines: sql = ''' INSERT INTO xcm_okex_kline ( _id, symbol,base_currency,quote_currency,period, timestamp,open,high,low,close,vol) 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]) cursor.execute(sql, param) print('insert >>>', symbol) # print(sql) db.commit() except: util.printExcept() finally: cursor.close() db.close()
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 get_symbols(): db = mysqlutil.init() cursor = db.cursor() try: sql = 'SELECT symbol, base, quote FROM xcm_fcoin_symbol' cursor.execute(sql) symbols = cursor.fetchall() return symbols except: util.printExcept() finally: cursor.close() db.close() return False
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 request_zb_symbol(): try: url = 'http://api.zb.cn/data/v1/markets' print(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') # print(content) symbol = json.loads(content) print('data len >>>', len(symbol)) return symbol except: util.printExcept() return False
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 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 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_currency_list(orderField, orderBy='DESC', searchKey='', pageIndex=1, pageSize=10): db = mysqlutil.init() cursor = db.cursor() currencies = [] # 将页码转换为查询范围 pageIndex = int(pageIndex) pageSize = int(pageSize) pageStart = (pageIndex - 1) * pageSize try: sql = 'SELECT * FROM `xcm_vxn_currency_info`' if searchKey != '': searchKey = "'%" + searchKey + "%'" sql += 'WHERE currency LIKE %s OR fullname LIKE %s OR cnname LIKE %s' % ( searchKey, searchKey, searchKey) sql += 'ORDER BY %s %s LIMIT %s,%s' % (orderField, orderBy, pageStart, pageSize) print(sql) cursor.execute(sql) rows = cursor.fetchall() for r in rows: currencies.append(_convert_currency(r)) except: util.printExcept() finally: db.close() cursor.close() return currencies
def import_okex_symbol(csvFile): db = mysqlutil.init() cursor = db.cursor() try: count = 0 with open(csvFile,'r',encoding='UTF-8') as f: symbol = csv.reader(f) header = next(symbol) for s in symbol: # print(s) sql = ''' SELECT COUNT(_id) FROM `xcm_okex_symbol` WHERE symbol=%s''' cursor.execute(sql, (s[1])) rows = cursor.fetchall() # print(rows[0][0]) if rows[0][0] > 0: continue sql = ''' INSERT INTO `xcm_okex_symbol` (product_id,symbol,base_min_size,base_increment,quote_increment,base,quote) VALUES (%s,%s,%s,%s,%s,%s,%s)''' c = s[1].split('_') cursor.execute(sql, (s[0], s[1], s[2], s[3], s[4], c[0], c[1])) db.commit() count += 1 print('insert >>>', count) print('\n### successed ###\n') except: util.printExcept() finally: cursor.close() db.close()
def get_ticker(): try: url = 'https://api.hitbtc.com/api/2/public/ticker' # print(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') # print(content) content = json.loads(content) # print('data len >>>', len(content)) return content except: util.printExcept(target='hitbtc > get_ticker') return False
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 import_huobipro_symbol(): symbol = get_huobipro_symbol() symbol = symbol['data'] print('symbol len >>>', len(symbol)) try: count = 0 for s in symbol: sql = ''' SELECT COUNT(_id) FROM `xcm_huobipro_symbol` WHERE symbol=%s''' cursor.execute(sql, (s['symbol'])) rows = cursor.fetchall() if rows[0][0] > 0: continue sql = ''' INSERT INTO `xcm_huobipro_symbol` (base,quote,price_precision, amount_precision,symbol,symbol_partition) VALUES (%s,%s,%s,%s,%s,%s)''' cursor.execute( sql, (s['base-currency'], s['quote-currency'], s['price-precision'], s['amount-precision'], s['symbol'], s['symbol-partition'])) db.commit() count += 1 print('insert >>>', count) print('### Successed ###') except: util.printExcept( target='import-huobipro-symbol > import_huobipro_symbol') finally: cursor.close() db.close()
def get_symbols(format='tuple'): db = mysqlutil.init() cursor = db.cursor() try: sql = 'SELECT symbol, base, quote FROM xcm_%s_symbol' % (TABLE) cursor.execute(sql) symbols = cursor.fetchall() if format == 'dict': dict = {} for s in symbols: dict[s[0]] = {'base': s[1], 'quote': s[2]} symbols = dict return symbols except: util.printExcept() finally: cursor.close() db.close() return False
def save_ticker_record(symbol, base, quote, t): db = mysqlutil.init() cursor = db.cursor() try: nowTime = time.time() * 10000000 ts = api.server_time() if ts['status'] != 0: return False ts = ts['data'] sql = ''' INSERT INTO xcm_fcoin_ticker ( _id, symbol, base, quote, ts, last, last_volume, max_bid, max_bid_volume, min_ask, min_ask_volume, 24h_ago_last, high, low, 24h_base_volume, 24h_quote_volume) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''' param = (nowTime, symbol, base, quote, ts, t[0], t[1], t[2], t[3], t[4], t[5], t[6], t[7], t[8], t[9], t[10]) cursor.execute(sql, param) db.commit() return True except: global exCount exCount += 1 util.printExcept(target='get-fcoin-kline > save_ticker') finally: cursor.close() db.close() return False
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
db = mysqlutil.init() cursor = db.cursor() try: for s in symbol: sql = ''' SELECT COUNT(_id) FROM `xcm_fcoin_symbol` WHERE symbol=%s''' cursor.execute(sql, (s['name'])) rows = cursor.fetchall() if rows[0][0] > 0: continue print('symbol >>>', s['name']) sql = ''' INSERT INTO `xcm_fcoin_symbol` (symbol, base, quote, price_decimal, amount_decimal) VALUES (%s, %s, %s, %s, %s)''' param = (s['name'], s['base_currency'], s['quote_currency'], s['price_decimal'], s['amount_decimal']) print(param) cursor.execute(sql, param) db.commit() print('\n### successed ###\n') except: util.printExcept() finally: cursor.close() db.close()
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()
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')