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 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_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_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 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 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 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 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 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 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_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_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_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 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 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 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')
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 on_message(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:
# 将行情数据（ticker）保存在数据库中
if 'channel' in d and d['channel'].find('_ticker') != -1:
global count
count += 1
# print('count >>>', count)
channel = d['channel']
channel = channel.split('_')
base = channel[3]
quote = channel[4]
symbol = base + '_' + quote
t = d['data']
sql = 'SELECT _id FROM xcm_okex_ticker WHERE symbol=%s'
cursor.execute(sql, (symbol))
rows = cursor.fetchall()
if len(rows) == 0:
sql = '''
INSERT INTO xcm_okex_ticker (
symbol,base_currency,quote_currency,
buy,high,last,low,sell,timestamp,vol)
VALUES (
%s,%s,%s,
%s,%s,%s,%s,%s,%s,%s)'''
param = (
symbol, base, quote,
t['buy'], t['high'], t['last'], t['low'], t['sell'], t['timestamp'], t['vol'])
cursor.execute(sql, param)
# print('insert >>>', symbol)
else:
sql = '''
UPDATE xcm_okex_ticker SET
buy=%s,high=%s,last=%s,low=%s,sell=%s,timestamp=%s,vol=%s
WHERE symbol=%s'''
param = (
t['buy'], t['high'], t['last'], t['low'], t['sell'], t['timestamp'], t['vol'],
symbol)
cursor.execute(sql, param)
# print('update >>>', symbol)
# print(sql)
db.commit()
except:
util.printExcept()
finally:
cursor.close()
db.close()
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
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 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')
import fcoin
from chengutil import mysqlutil, util
api = fcoin.authorize('key', 'secret')
print(api.server_time())
symbol = api.symbols()
symbol = symbol['data']
print('symbols len >>>', len(symbol))
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)
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_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')
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)
