def ExecNoneQueryMulti(self, sqls=list()):
"""
执行非查询语句，执行事务提交方式
调用示例：
cur = self.__GetConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()
"""
# 事务处理
conn, cur = self.__GetConnect()
currrent_sql = ''
try:
for sql in sqls:
currrent_sql = sql
cur.execute(sql)
conn.commit()
print('事务处理成功', sqls.__len__())
except Exception as e:
info = "sql事务执行失败：" + repr(e) + currrent_sql
conn.rollback()
log_exp.error('非预见性异常' + info)
raise (OSError, '自定义error')
finally:
conn.close()
async def weixin_reply(request):
if request.method == 'GET':
res = request_para(request)
sign_ture = res.get('signature')
time_stamp = res.get('timestamp')
nonce_ = res.get('nonce')
echo_str = res.get('echostr')
try:
check_signature(token='4725471112', signature=sign_ture, timestamp=time_stamp, nonce=nonce_)
return_str = echo_str
except InvalidSignatureException:
log_exp.error('InvalidSignatureException')
return_str = 'InvalidSignatureException'
return response.text(body=return_str)
elif request.method == 'POST':
xml = request.body
msg = parse_message(xml)
if msg.type == 'text' and msg.source == 'opfB6w88fRxMh6DJirlzW8biOFNw': # 固定1148270327这个用户
res_msg = web_call_main(msg.content.strip())
reply = TextReply(content=res_msg, message=msg)
xml = reply.render()
return response.text(body=xml, status=200)
else:
return response.text(body='NaN')
else:
return response.text(body='NaN')
def get_candle_data(exchange, symbol, time_interval='1m', is_contract=False):
"""
# 直接获取k线数据, 私有方法
"""
para = dict()
if is_contract and exchange.name == 'Okex':
para = {'contract_type': 'quarter'}
# 抓取k线
loop_count = 0
while True:
try:
loop_count += 1
content = None
if exchange.name == 'OKEX':
# 抓取数据 如果是获取合约数据则加上params={'contract_type': 'quarter'}
content = exchange.fetch_ohlcv(
symbol, timeframe=time_interval, since=0,
params=para) # 这里有个since=0的问题，是okex特有的，在其他交易所得用limit
elif exchange.name == 'Bitfinex v2':
# bitfinex V2拿数据 v1处理资产
content = exchange.fetch_ohlcv(symbol,
timeframe=time_interval,
limit=1000,
since=None)
else:
log_exp.error('错误的交易所，exchage.name %s', exchange.name)
exit(110)
# 整理数据
df = pd.DataFrame(content, dtype=float)
df.rename(columns={
0: 'MTS',
1: 'open',
2: 'high',
3: 'low',
4: 'close',
5: 'volume'
},
inplace=True)
df['candle_begin_time'] = pd.to_datetime(df['MTS'], unit='ms')
df['candle_begin_time_GMT8'] = df['candle_begin_time'] + timedelta(
hours=8)
df = df[[
'candle_begin_time_GMT8', 'open', 'high', 'low', 'close',
'volume'
]]
return df
except Exception as e:
if loop_count >= 5:
break
log_exp.error('下载数据报错,10秒后重试%s', e)
time.sleep(20)
def __get_position(exchange):
"""
查询持仓信息
:param exchange:
:return:
"""
for i in range(5):
try:
position = exchange.private_post_auth_r_positions()
return position
except Exception as e:
log_exp.info('下单查询持仓报错%s', e)
time.sleep(10)
log_exp.error('下单查询多次报错')
def ExecNonQuery(self, sql):
"""
执行非查询语句
调用示例：
cur = self.__GetConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()
"""
conn, cur = self.__GetConnect()
try:
cur.execute(sql)
conn.commit()
except Exception as e:
log_exp.error("sql执行失败" + repr(e) + sql)
finally:
conn.close()
def __GetConnect(self):
"""
得到连接信息
返回: conn.cursor()
"""
if not self.db:
log_exp.error("没有设置数据库信息")
raise (NameError, "没有设置数据库信息")
# 连接到SQLite数据库
# 数据库文件是test.db
# 如果文件不存在，会自动在当前目录创建:
conn = sqlite3.connect(self.db)
cur = conn.cursor()
if not cur:
log_exp.error("连接数据库失败")
raise (NameError, "连接数据库失败")
else:
return conn, cur
def ExecQuery(self, sql):
"""
执行查询语句
返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段
调用示例：
ms = MSSQL(host="localhost",user="******",pwd="123456",db="PythonWeiboStatistics")
resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser")
for (id,NickName) in resList:
print str(id),NickName
"""
conn, cur = self.__GetConnect()
resList = None
try:
cur.execute(sql)
resList = cur.fetchall()
except Exception as e:
log_exp.error("sql执行失败" + repr(e) + sql)
finally:
# 查询完毕后必须关闭连接
conn.close()
return resList
def __sell_find(self, flag, index, df_copy, df_sell, state):
"""
对当前index帧数据执行判定； 【1.无信号、持仓未知，看是否要平仓；2. 有信号、有持仓，看是否要平仓】时，会被执行此函数
:param index: 传值
:param df_copy: 引用，直接改变原来dataframe；复制表，辅助循环和查询
:param df_sell: 引用，直接改变原来dataframe；原始表，修改数据
:param state: 传值
:return: state
"""
if state[1] == -1: # 有持空仓
if flag == 0: # 当前无信号
# ===找出中线平仓信号
if (df_copy.loc[index, 'bbPb'] >
(0.5 + self.th2)) and (df_copy.loc[index - 1, 'bbPb'] <=
(0.5 + self.th2)):
df_sell.loc[index, 'signal_sell'] = -10
state = (0, 0, -10) # 平仓掉了之前的状态
elif flag == -1 or flag == 2: # 依然是做空，则继续
log_exp.debug('持空仓信号为%d,当前信号为%d, 继续做空！', state[1], flag)
elif flag == -2:
log_exp.error('持有空仓；由下而上快穿越上线，反向做空信号。该信号不存在，因为中线附近已平仓，请检查！')
elif state[1] == 1: # 持多仓
if flag == 0:
# ===找出做多中线平仓信号
if (df_copy.loc[index, 'bbPb'] <
(0.5 - self.th2)) and (df_copy.loc[index - 1, 'bbPb'] >=
(0.5 - self.th2)):
df_sell.loc[index, 'signal_sell'] = 10
state = (0, 0, 10) # 平仓掉了之前的状态
elif flag == 1 or flag == -2: # 依然是做多，则继续 【-2本质还是穿越上线】
log_exp.debug('持多仓信号为%d,当前信号为%d, 继续做多！', state[1], flag)
elif flag == 2:
log_exp.error('持有多仓；由上而下快穿越下线，反向做多信号。该信号不存在，因为中线附近已平仓，请检查！')
elif state[1] == 2: # 持仓状态为2
if flag == 0 or flag == -1 or flag == 2:
# ===止损：跌幅达到一定量就平
if ((df_copy.loc[index, 'close'] -
df_copy.loc[state[0], 'close']) /
df_copy.loc[state[0], 'close']) < -self.th4:
df_sell.loc[index, 'signal_sell'] = 20
state = (0, 0, 20) # 平仓掉了之前的状态
else: # 智能止盈，由上往下穿越中线并达到波动量则平仓
if (df_copy.loc[index, 'bbPb'] >=
0.5) and (df_copy.loc[index - 1, 'bbPb'] < 0.5):
df_sell.loc[index, 'signal_sell'] = 20
state = (0, 0, 20) # 平仓掉了之前的状态
elif flag == -2 or flag == 1: # 【穿越下线的两种形态】
log_exp.debug('持多仓信号为%d,当前信号为%d, 继续做多！', state[1], flag)
elif state[1] == -2: # 迁移状态为-2
if flag == 0 or flag == 1 or flag == -2:
# ===止损：涨幅达到一定量就平
if ((df_copy.loc[index, 'close'] -
df_copy.loc[state[0], 'close']) /
df_copy.loc[state[0], 'close']) > self.th4:
df_sell.loc[index, 'signal_sell'] = -20
state = (0, 0, -20) # 平仓掉了之前的状态
else: # 智能止盈，由下往上穿越中线并达到波动量则平仓
if (df_copy.loc[index, 'bbPb'] <=
0.5) and (df_copy.loc[index - 1, 'bbPb'] > 0.5):
df_sell.loc[index, 'signal_sell'] = -20
state = (0, 0, -20) # 平仓掉了之前的状态
elif flag == 2 or flag == -1: # 【穿越下线的两种形态】
log_exp.debug('持空仓信号为%d,当前信号为%d, 继续做空！', state[1], flag)
pass
return state
def run_instance(self):
#外部是大循环，dowork()会一直运行
self.next_time = utils.next_run_time(
self.interval_time) # 得到下次运行的时间,提前2s
self.k_data = None #每次都需要初始化为nan
#数据获取方式优化
if self.next_time < datetime.now():
#下个运行时刻不知什么原因错过了
log_exp.error('当前时间(%s)超过了下次运行时间(%s)，请检查。程序立刻执行数据更新',
datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
self.next_time.strftime("%Y-%m-%d %H:%M:%S"))
self.k_data = k_lines.update_kline(self.exchange, self.symbol,
self.interval_time,
self.next_time)
else:
if ((self.next_time - datetime.now()) < timedelta(seconds=60)):
#差60s就到达下一个运行时间，直接检测执行
log_exp.debug('马上到点，启动高频检测数据[间隔50s]')
self.k_data = k_lines.update_kline(self.exchange,
self.symbol,
self.interval_time,
self.next_time,
detect_time=50)
else:
#选择sleep方式，而非不断循环执行
sleep(((self.next_time - datetime.now()) * 0.7).seconds)
# self.k_data = utils.file_obj_convert()#调试代码
# self.k_data = self.k_data[0:960]#调试代码
if self.k_data is not None:
self.k_data = self.k_data[
self.k_data['candle_begin_time_GMT8'] < pd.to_datetime(
self.next_time)] # 去除target_time周期的数据
#logger.debug('k_data tail(3): \n %s', self.k_data.tail(3))
#拿到时刻数据，进行信号分析
df_signal = self.trading_signal(self.k_data, self.strtegy_para)
log_exp.debug('df_signal tail(3): \n %s', df_signal.tail(3))
signal = df_signal.iloc[-1]['signal'] # 最新数据的那个信号
signal_before = df_signal.iloc[-1]['pos'] # 前一刻的持仓
log_exp.info('当前最新数据时间 %s', self.next_time)
log_exp.info('当前交易信号为:%s', signal)
log_exp.info('前一刻理论持仓为:%s', signal_before)
#调试代码
# signal = 1
# signal_before = 0
if not pd.isna(signal):
# self.operation_para =>【order_para=[multi, percent, my_exchange.bitfinex_instance()]】
# 根据1日线决定倍率
k_data_1day = k_lines.get_candle_data(self.exchange,
self.symbol, '1d')
diff_percent = 0 # 当前与均值的差异
if k_data_1day is None:
ratio = 1
else:
# 计算30日均线
k_data_1day['median'] = k_data_1day['close'].rolling(
30, min_periods=1).mean() # n日的均值
close_cur = df_signal.iloc[-1]['close'] # 最新数据的那个信号
mean_cur = k_data_1day.iloc[-1]['median']
mean_max = max(
list(
map(lambda x: abs(x),
k_data_1day['median'].values.tolist())))
diff_percent = (close_cur - mean_cur) / mean_cur # 正负都有可能
max_diff_percent = (mean_max - mean_cur) / mean_cur # 为正数
# 分成5个等级：1，1.5，2，2.5，3
mean_percent = max_diff_percent / 5
diff_percent_abs = abs(diff_percent)
if 0 <= diff_percent_abs < mean_percent:
ratio = 1
elif mean_percent <= diff_percent_abs < mean_percent * 2:
ratio = 1.5
elif mean_percent * 2 <= diff_percent_abs < mean_percent * 3:
ratio = 2
elif mean_percent * 3 <= diff_percent_abs < mean_percent * 4:
ratio = 2.5
elif mean_percent * 4 <= diff_percent_abs:
ratio = 3
else:
ratio = 1
operation_para_modify = copy.copy(self.operation_para) # 操作副本
# 0 表示自动。 均值上方且做多信号； 均值下方且做空信号；
if self.operation_para[0] == 0 and (
(diff_percent > 0 and signal == 1) or
(diff_percent < 0 and signal == -1)):
operation_para_modify[0] = ratio
else:
operation_para_modify[0] = 1
log_exp.info('%d倍持仓！', ratio)
# 具体参数，需要结合对应函数给相应参数
self.trading_operation(self.exchange, self.symbol, signal,
signal_before, operation_para_modify)
log_exp.debug('----------group line-------------')
def auto_trade_leverage(exchange_v2,
symbol,
signal,
signal_before,
para=list()):
leverage = para[0] # 杠杆倍数，最高3.3倍
position_pct = para[1] # 持仓比重
exchange_v1 = para[2] #操作资产的exchange
base_coin = symbol.split('/')[-1]
trade_coin = symbol.split('/')[0]
# 对symbol进行命名统一
if base_coin == 'USDT':
symbol_temp = 't' + symbol.split('/')[0] + symbol.split('/')[1][:-1]
if base_coin == 'BTC':
symbol_temp = 't' + symbol.split('/')[0] + symbol.split('/')[1]
# 获取仓位信息，需要有仓位才能获取，不然是空白，这里获取的是Margin账户的信息
position = __get_position(exchange_v2)
log_exp.debug('持仓情况%s', position)
# 用来存储已持仓的币种列表
position_list = []
position_all_amount = []
if len(position) == 0:
log_exp.info('当前无持仓')
if len(position) != 0:
for p in position:
position_symbol = p[0][1:-3] #'tBTCUSD'截取一段
position_list.append(position_symbol)
position_amount = p[2]
position_all_amount.append(position_amount)
log_exp.info('所有持仓币种：%s', position_list)
#显示第一位的币种情况
log_exp.info('首个持仓币种 %s', position_list[0])
log_exp.info('首个持仓数量 %s', position_all_amount[0])
# 生成平单的注释，邮件中提醒损益情况
note = ''
profit_loss = 0.0
profit_loss_percent = 0.0
if trade_coin in position_list:
_index = position_list.index(trade_coin)
if len(position[_index]) > 7:
profit_loss = position[_index][6]
profit_loss_percent = position[_index][7]
note = str(round(profit_loss, 2)) + '，' + str(
round(profit_loss_percent, 2))
log_exp.info('交易注释生成：%s', note)
#操作下单动作
loop_count = 0
while loop_count < 5:
loop_count += 1
try:
# 查账户余额【默认USDT用于交易】
if base_coin == 'USDT':
balance_total = float(
exchange_v1.fetch_balance({'type':
'trading'})['free']['USDT'])
elif base_coin == 'BTC':
balance_total = float(
exchange_v1.fetch_balance({'type':
'trading'})['free']['BTC'])
else:
balance_total = 0.0
# =====空仓情况下：下多单
if signal == 1 and signal_before == 0 and trade_coin not in position_list: # in语法 相当于Java里的contains
# 获取最新的买入价格
price = exchange_v1.fetch_ticker(symbol)['ask'] # 获取卖一价格
# 计算买入数量,按总资产的position_pct来计算仓位
buy_amount = (balance_total * position_pct * leverage / price)
# 下单
place_order_bitfinex(exchange_v1,
order_type='limit',
buy_or_sell='buy',
symbol=symbol,
price=price * 1.01,
amount=buy_amount,
record={
'multiple': leverage,
'profit': profit_loss,
'profit_percent': profit_loss_percent,
'signal': '多单',
'account': balance_total
})
log_exp.info('已下多单')
time.sleep(5)
# =====空仓情况下：下空单
if signal == -1 and signal_before == 0 and trade_coin not in position_list:
# 获取最新的卖出价格
price = exchange_v1.fetch_ticker(symbol)['bid'] # 获取买一价格
# 计算买入数量,按总资产的position_pct来计算仓位
sell_amount = (balance_total * position_pct * leverage / price)
# 下单
place_order_bitfinex(exchange_v1,
order_type='limit',
buy_or_sell='sell',
symbol=symbol,
price=price * 0.99,
amount=sell_amount,
record={
'multiple': leverage,
'profit': profit_loss,
'profit_percent': profit_loss_percent,
'signal': '空单',
'account': balance_total
})
log_exp.info('已下空单')
time.sleep(5)
# =====持仓情况下：平空单
if signal == 0 and signal_before == -1 and trade_coin in position_list:
# 获取最新的买入价格
price = exchange_v1.fetch_ticker(symbol)['ask'] # 获取卖一价格
# 查询持仓数量
position_new = __get_position(exchange_v2)
position_new = pd.DataFrame(position_new)
buy_amount = (abs(
position_new[position_new[0] == symbol_temp].iloc[0][2]))
# 下单
place_order_bitfinex(exchange_v1,
order_type='limit',
buy_or_sell='buy',
symbol=symbol,
price=price * 1.01,
amount=buy_amount,
comment=note,
record={
'multiple': 0.0,
'profit': profit_loss,
'profit_percent': profit_loss_percent,
'signal': '平空单',
'account': balance_total
})
log_exp.info('已平空单')
time.sleep(5)
# =====持仓情况下：平多单
if signal == 0 and signal_before == 1 and trade_coin in position_list:
# 获取最新的卖出价格
price = exchange_v1.fetch_ticker(symbol)['bid'] # 获取买一价格
# 查询持仓数量
position_new = __get_position(exchange_v2)
position_new = pd.DataFrame(position_new)
sell_amount = (
position_new[position_new[0] == symbol_temp].iloc[0][2])
# 下单
place_order_bitfinex(exchange_v1,
order_type='limit',
buy_or_sell='sell',
symbol=symbol,
price=price * 0.99,
amount=sell_amount,
comment=note,
record={
'multiple': 0.0,
'profit': profit_loss,
'profit_percent': profit_loss_percent,
'signal': '平多单',
'account': balance_total
})
log_exp.info('已平多单')
# =====持仓情况下：空仓转多仓 【暂时废弃】
if signal == 1 and signal_before == -1 and trade_coin in position_list:
# 先买入平仓
price = exchange_v1.fetch_ticker(symbol)['ask'] # 获取卖一价格
position_new = __get_position(exchange_v2)
position_new = pd.DataFrame(position_new)
buy_amount = (abs(
position_new[position_new[0] == symbol_temp].iloc[0][2]))
place_order_bitfinex(exchange_v1,
order_type='limit',
buy_or_sell='buy',
symbol=symbol,
price=price * 1.03,
amount=buy_amount,
comment=note)
log_exp.info('已平空单，稍后下多单')
# 然后下多单
price = exchange_v1.fetch_ticker(symbol)['ask'] # 获取卖一价格
if base_coin == 'USDT':
balance_total = float(
exchange_v1.fetch_balance({'type':
'trading'})['free']['USDT'])
if base_coin == 'BTC':
balance_total = float(
exchange_v1.fetch_balance({'type':
'trading'})['free']['BTC'])
buy_amount = (balance_total * position_pct * leverage / price)
place_order_bitfinex(exchange_v1,
order_type='limit',
buy_or_sell='buy',
symbol=symbol,
price=price * 1.02,
amount=buy_amount)
log_exp.info('已下多单，完成空转多')
time.sleep(3)
# =====持仓情况下：多仓转空仓 【暂时废弃】
if signal == -1 and signal_before == 1 and trade_coin in position_list:
# 先卖出平仓
price = exchange_v1.fetch_ticker(symbol)['bid'] # 获取买一价格
# 查询持仓数量
position_new = __get_position(exchange_v1)
position_new = pd.DataFrame(position_new)
sell_amount = (
position_new[position_new[0] == symbol_temp].iloc[0][2])
place_order_bitfinex(exchange_v1,
order_type='limit',
buy_or_sell='sell',
symbol=symbol,
price=price * 0.97,
amount=sell_amount,
comment=note)
log_exp.info('已平多单，稍后下空单')
# 然后下空单
if base_coin == 'USDT':
balance_total = float(
exchange_v1.fetch_balance({'type':
'trading'})['free']['USDT'])
if base_coin == 'BTC':
balance_total = float(
exchange_v1.fetch_balance({'type':
'trading'})['free']['BTC'])
price = exchange_v1.fetch_ticker(symbol)['bid'] # 获取买一价格
sell_amount = (balance_total * position_pct * leverage / price)
place_order_bitfinex(exchange_v1,
order_type='limit',
buy_or_sell='sell',
symbol=symbol,
price=price * 0.98,
amount=sell_amount)
log_exp.info('已下空单，完成多转空')
time.sleep(3)
break
except Exception as e:
log_exp.error('下单函数报错，10秒后重试,%s', e)
time.sleep(10)
def place_order_bitfinex(
exchange,
order_type,
buy_or_sell,
symbol,
price,
amount,
comment='下单',
record={
'multiple': '0',
'profit': '0',
'profit_percent': '0',
'signal': '无',
'account': '0'
}):
"""
下单
:param exchange: 交易所
:param order_type: limit, market
:param buy_or_sell: buy, sell
:param symbol: 买卖品种
:param price: 当market订单的时候，price无效
:param amount: float 买卖量
:return:
"""
log_exp.info(
'下单 order_type： %s, buy_or_sell： %s, symbol： %s, price： %s, amount： %s',
order_type, buy_or_sell, symbol, price, str(amount))
content_txt = '执行时间：' + datetime.now().strftime(
"%Y-%m-%d %H:%M:%S"
) + '\n持仓情况(损益，损益百分比)：' + comment + '\n 执行参数（价格，数量）：' + str(round(
price, 4)) + '，' + str(round(amount, 3))
log_exp.info('邮件正文：%s', content_txt)
threading.Thread(target=send_mail,
args=(buy_or_sell + ' ' + symbol, content_txt)).start()
# 记录数据到sqlite
record_type = str(symbol) # ETH
trade_signal = record['signal'] #'做空/做多/平仓'
trade_multiple = str(record['multiple']) # 倍数
trade_amount = str(round(amount, 2)) # 量
trade_profit = str(round(record['profit'], 2)) # 损益
trade_profit_percent = str(round(record['profit_percent'], 2)) # 损益比
account = str(round(record['account'])) # 账户余额
sql = "INSERT INTO history_cache(record_type, trade_signal, trade_multiple, trade_amount, trade_profit, trade_profit_percent, account) VALUES('"\
+record_type+"', '"+trade_signal+"', '"+trade_multiple+"', '"+trade_amount+"', '"+trade_profit+"', '"+trade_profit_percent+"', '"+account+"')"
__sqlite.ExecNonQuery(sql)
order_info = None
amount = str(amount)
for i in range(5):
try:
# 限价单
if order_type == 'limit':
# 买
if buy_or_sell == 'buy':
order_info = exchange.create_limit_buy_order(
symbol, amount, price, {'type': 'limit'}) # 买单
# 卖
elif buy_or_sell == 'sell':
order_info = exchange.create_limit_sell_order(
symbol, amount, price, {'type': 'limit'}) # 卖单
# 市价单
elif order_type == 'market':
# 买
if buy_or_sell == 'buy':
order_info = exchange.create_market_buy_order(
symbol, amount, {'type': 'market'}) # 买单
# 卖
elif buy_or_sell == 'sell':
order_info = exchange.create_market_sell_order(
symbol, amount, {'type': 'market'}) # 卖单
else:
pass
log_exp.info('下单信息：%s', order_info)
return order_info
except Exception as e:
log_exp.error('下单报错，1s后重试%s', e)
time.sleep(1)
log_exp.error('下单报错次数过多，程序终止')
threading.Thread(
target=send_mail,
args=('下单报错次数过多，程序终止', 'failed order, time:' +
datetime.now().strftime("%Y-%m-%d %H:%M:%S"))).start()
exit()
