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