def db_write_data(self, data_list, table_name): # 将data_list中数据写入数据库
try:
self._db_reconnect()
if data_list is None:
return True
with self.db_connect.cursor() as cursor:
for i in data_list:
if type(i) == list:
sql1 = "REPLACE INTO " + str(table_name) + \
" (time, OpenPrice, HighPrice, LowPrice, ClosePrice, Volume) VALUES (\"" \
+ str(i[0]) + "\"," + str(i[1]) + "," + str(i[2]) \
+ "," + str(i[3]) + "," + str(i[4]) + "," + str(i[5]) + ")"
elif type(i) == dict:
sql1 = "REPLACE INTO " + str(table_name) + \
" (time, OpenPrice, HighPrice, LowPrice, ClosePrice, Volume) VALUES (\"" \
+ str(i['time']) + "\"," + str(i['OpenPrice']) + "," + str(i['HighPrice']) \
+ "," + str(i['LowPrice']) + "," + str(i['ClosePrice']) + "," + str(i['Volume']) + ")"
else:
print("类Dbmgr-函数db_write_data中无法识别需要写入的数据，请检查数据输入！！！")
pushMessage.fun_push_message(
"类Dbmgr-函数db_write_data中无法识别需要写入的数据，请检查数据输入！！！")
return False
cursor.execute(sql1)
self.db_connect.commit()
# self.db_connect.close()
except Exception:
self.db_connect.rollback()
print("类Dbmgr-函数db_write_data中数据写入失败,请及时检查异常！！")
pushMessage.fun_push_message(
"类Dbmgr-函数db_write_data中数据写入失败,请及时检查异常！！")
return False
def db_write_column(self, index_list, data_list, table_name,
column_name): # 将data_list中数据写入数据库
try:
self._db_reconnect()
if data_list is None:
return True
with self.db_connect.cursor() as cursor:
for i in range(len(data_list)):
sql2 = "UPDATE " + str(table_name) + " SET " + str(column_name) + "=" + str(data_list[i]) + \
" WHERE " + "time=\"" + str(index_list[i][0]) + "\""
# print(sql2)
cursor.execute(sql2)
self.db_connect.commit()
except Exception:
self.db_connect.rollback()
print("类Dbmgr-函数db_write_column中数据写入失败,请及时检查异常！！")
pushMessage.fun_push_message(
"类Dbmgr-函数db_write_column中数据写入失败,请及时检查异常！！")
return False
def _db_connect(self):
try:
self.db_connect = pymysql.connect(host=self.host,
port=self.port,
user=self.user,
password=self.password,
db=self.db,
charset=self.charset,
cursorclass=self.cursorclass)
return True
except pymysql.err.OperationalError:
print("类Dbmgr-函数db_connect中数据库连接超时！！！")
pushMessage.fun_push_message("类Dbmgr-函数db_connect中数据库连接超时！！！")
return False
except pymysql.err.InterfaceError:
print("类Dbmgr-函数db_connect中异常原因导致数据库连接失败！请及时检查连接！！！")
pushMessage.fun_push_message(
"类Dbmgr-函数db_connect中异常原因导致数据库连接失败！请及时检查连接！！！")
return False
def db_read_data(self, table_name, key, m,
n): # 读取数据第m-n行的"column_name"列数据，若为多列，则用','隔开
try:
self._db_reconnect()
# print("read-0")
with self.db_connect.cursor() as cursor:
sql3 = "SELECT " + str(key) + " FROM " + str(
table_name) + " limit " + str(m) + "," + str(n)
# print(sql3)
cursor.execute(sql3)
self.db_connect.commit()
result_data = cursor.fetchall()
# print("1db_read_data" + ("*" * 80))
# print(result_data)
# print(type(result_data))
# print("2db_read_data" + ("*" * 80))
df = pd.DataFrame(list(result_data))
return df # 返回dataFrame类型的结果
except Exception:
self.db_connect.rollback()
print("类Dbmgr-函数db_read_data中数据读取失败,请及时检查异常！！")
pushMessage.fun_push_message(
"类Dbmgr-函数db_read_data中数据读取失败,请及时检查异常！！")
return False
def fun_get_data(database_obj,
instrument_id="BTC-USDT",
granularity="60",
table_name="realtime_data_1min"):
# 获取需查询的时间段长度值
delta_time = int(int(granularity) / 60)
data_list = []
n = 0 # 数据请求失败的次数，失败后，下一次请求的数据时间段应相应延长n倍
headers = UserAgent().random
# 进入循环查询过程
while True:
# 计算查询时间段起始值
now_time = datetime.datetime.utcnow()
start = (now_time + datetime.timedelta(minutes=-delta_time *
(n + 1))).replace(
microsecond=0).isoformat()
end = now_time.replace(microsecond=0).isoformat()
# 拼接请求链接
url_real_data = "https://www.okex.me/api/spot/v3/instruments/" + str(instrument_id) + "/candles?granularity=" \
+ str(granularity) + "&start=" + str(start) + ".000Z&end=" + str(end) + ".000Z"
# print(url_real_data)
try:
s = requests.session()
s.keep_alive = False
s.adapters.DEFAULT_RETRIES = 100
r = s.get(url_real_data,
headers={"User-Agent": headers},
timeout=12) # 获得请求数据
except requests.exceptions.ConnectionError as e:
print('error', e.args)
pushMessage.fun_push_message("the error is " + str(e.args))
n += 1
else:
n = 0
temp = r.json() # 将请求的数据解码后存入列表
temp.reverse()
data_list.extend(temp) # 将请求的数据解码后存入列表
if len(data_list) == 0: # 对获取的数据为空列表的处理方式为直接结束函数，重新查询
continue
print(data_list, "\tdelta_time = ",
delta_time) # 打印获取的数据及当前未存入数据库的数据（用于调试）
# 将获取到的列表数据存入数据库[[time, open, high, low, close, volume],[],...]
for i in data_list:
# 修改
sql = "SELECT MACDsignal_DEA,EMA12,EMA26 FROM " + str(
table_name) + " order by time DESC limit 1"
history_data = database_obj.db_select_sql(
sql) # 返回dataFrame类型数据
EMA12 = 2 / 13 * float(i[4]) + 11 / 13 * float(
history_data.at[0, 1]) # 实时计算MACD
EMA26 = 2 / 27 * float(i[4]) + 25 / 27 * float(
history_data.at[0, 2])
MACD_DIFF = EMA12 - EMA26
MACDsignal_DEA = 2 / 10 * MACD_DIFF + 8 / 10 * float(
history_data.at[0, 0])
MACDhist_DIFF_DEA = MACD_DIFF - MACDsignal_DEA
sql = "REPLACE INTO " + str(table_name) \
+ " (time, OpenPrice, HighPrice, LowPrice, ClosePrice, Volume, " \
"MACD_DIFF, MACDsignal_DEA, MACDhist_DIFF_DEA, EMA12, EMA26) VALUES (\"" \
+ str(i[0]) + "\"," \
+ str(i[1]) + "," \
+ str(i[2]) + "," \
+ str(i[3]) + "," \
+ str(i[4]) + "," \
+ str(i[5]) + "," \
+ str(MACD_DIFF) + "," \
+ str(MACDsignal_DEA) + "," \
+ str(MACDhist_DIFF_DEA) + "," \
+ str(EMA12) + "," \
+ str(EMA26) + ")" # 编写sql语句
# print(sql)
database_obj.db_exe_sql(sql) # 执行sql语句
data_list = []
# 修改
# sql = "REPLACE INTO " + str(table_name) + \
# " (time, OpenPrice, HighPrice, LowPrice, ClosePrice, Volume) VALUES (\"" \
# + str(i[0]) + "\"," + str(i[1]) + "," + str(i[2]) \
# + "," + str(i[3]) + "," + str(i[4]) + "," + str(i[5]) + ")" # 编写sql语句
# database_obj.db_exe_sql(sql) # 执行sql语句
time.sleep(int(granularity))