def test_normal(self, con, tmpdir):
dump_path = str(tmpdir.join("dump.db"))
con.dump(dump_path)
con_dump = SimpleSQLite(dump_path, "r")
assert con.fetch_num_records(TEST_TABLE_NAME) == con_dump.fetch_num_records(TEST_TABLE_NAME)
assert con.select_as_tabledata(TEST_TABLE_NAME) == con_dump.select_as_tabledata(
TEST_TABLE_NAME
)
def test_normal(self, con, tmpdir):
dump_path = str(tmpdir.join("dump.db"))
con.dump(dump_path)
con_dump = SimpleSQLite(dump_path, "r")
assert con.fetch_num_records(
TEST_TABLE_NAME) == con_dump.fetch_num_records(TEST_TABLE_NAME)
assert con.select_as_tabledata(
TEST_TABLE_NAME) == con_dump.select_as_tabledata(TEST_TABLE_NAME)
class MysqlDataHandle(MysqlData):
def __init__(self, g, event_queue, data_handle):
self.g = g
self.event_queue = event_queue
self.data_handle = data_handle
self.dbname = 'bitmex'
self.create_table(self.dbname)
self.data = None
self.con = SimpleSQLite(self.dbname, 'a')
self.ex = cxt.bitmex({
'apiKey': self.g.apiKey,
'secret': self.g.apiSecret,
'timeout': 60000,
})
def checkData(self, symbol):
if self.OneHourDBIsZeroCount(symbol) == True:
self.insertOneHourData(symbol)
elif self.CheckLastOneHourData(symbol) == False:
self.insertOneHourData(symbol)
def on_1mbar_event(self, event):
__bar = event.dict_
# _now_time = datetime.strptime(__bar.timestamp,'%Y-%m-%dT%H:%M:%S.%fZ')
_sql = [
__bar.symbol, __bar.bar_type, __bar.open, __bar.high, __bar.low,
__bar.close, __bar.volume, __bar.trades, __bar.buy, __bar.sell,
__bar.timestamp
]
conn = sqlite3.connect(self.dbname)
cursor = conn.cursor()
cursor.execute(insert_bar_1m_sql, (_sql))
conn.commit()
cursor.close()
conn.close()
def on_1hbar_event(self, event):
__bar = event.dict_
_sql = [
__bar.symbol, __bar.bar_type, __bar.open, __bar.high, __bar.low,
__bar.close, __bar.volume, __bar.trades, __bar.buy, __bar.sell,
__bar.timestamp
]
conn = sqlite3.connect(self.dbname)
cursor = conn.cursor()
cursor.execute(insert_bar_1h_much_sql, (_sql))
conn.commit()
cursor.close()
conn.close()
def retResampleOhlcv(self, ohlcv):
data = np.array(ohlcv)
start_index = datetime.datetime.fromtimestamp(int(
str(data[0][0])[:10]))
end_index = datetime.datetime.fromtimestamp(int(str(data[-1][0])[:10]))
dateIndex = pd.date_range(start=start_index, end=end_index, freq='T')
df = pd.DataFrame(
data[:, 1:],
index=dateIndex,
columns=['Open', 'High', 'Low', 'Close', 'Volume', 'Buy', 'Sell'])
ohlc_dict = {
'Open': 'first',
'High': 'max',
'Low': 'min',
'Close': 'last',
'Volume': 'sum',
'Buy': 'sum',
'Sell': 'sum',
}
df1H = df.resample('60T', closed='left', label='left').agg(ohlc_dict)
return df1H
def OneHourDBIsZeroCount(self, symbol):
conn = sqlite3.connect(self.dbname)
cursor = conn.cursor()
cursor.execute("SELECT * FROM bitmex_bar_1h WHERE symbol = ?",
(symbol, ))
bar_1h_count = cursor.fetchall()
if len(bar_1h_count) == 0:
return True
else:
return False
def InsertNewOrder(self, order):
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
_insert_sql = '''
INSERT INTO bitmex_orderhistory
(symbol,orderid,ordertype,orderprice,side,amount,status,date)
VALUES (?,?,?,?,?,?,?,?)
'''
cursor.execute(_insert_sql, order)
conn.commit()
cursor.close()
conn.close()
def InsertManyNewOrder(self, orderlist):
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
_insert_sql = '''
INSERT INTO bitmex_orderhistory
(symbol,orderid,ordertype,orderprice,side,amount,status,date)
VALUES (?,?,?,?,?,?,?,?)
'''
cursor.executemany(_insert_sql, orderlist)
conn.commit()
cursor.close()
conn.close()
def CheckNewOrderInThreeMinute(self, symbol):
d = datetime.datetime.now().replace(
second=0, microsecond=0) + datetime.timedelta(minutes=-3)
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
cursor.execute(
"SELECT * FROM bitmex_orderhistory WHERE symbol = ? and date >= ? and ordertype= 'opening' and status = 'new' ",
(symbol, d))
_new_order = cursor.fetchall()
cursor.execute(
"SELECT * FROM bitmex_orderhistory WHERE symbol = ? and date < ? and ordertype= 'opening' and status = 'new' ",
(symbol, d))
_old_order = cursor.fetchall()
if len(_old_order) > 0:
for __old_order in _old_order:
try:
self.ex.cancel_order(__old_order[2])
except Exception as e:
print(e)
time.sleep(1)
cursor.execute(
'DELETE FROM bitmex_orderhistory WHERE orderid = ?',
(__old_order[2], ))
time.sleep(0.5)
conn.commit()
cursor.close()
conn.close()
if len(_new_order) > 0:
return True
else:
return False
def DeleteBuyOrder(self, symbol):
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
cursor.execute(
"SELECT * FROM bitmex_orderhistory WHERE symbol = ? and ordertype= 'grid' and side = 'Buy' and status = 'new' ",
(symbol, ))
_orders = cursor.fetchall()
if len(_orders) > 0:
for _order in _orders:
try:
self.ex.cancel_order(_order[2])
except Exception as e:
print(e)
time.sleep(1)
cursor.execute(
'DELETE FROM bitmex_orderhistory WHERE orderid = ?',
(_order[2], ))
time.sleep(0.5)
conn.commit()
cursor.close()
conn.close()
def UpdateOrderStatus(self, order):
ord_id = order.orderid
status = order.status
symbol = order.symbol
if status == 'filled':
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
cursor.execute(
"UPDATE bitmex_orderhistory SET status = 'filled' WHERE orderid = ? and symbol = ? ",
(ord_id, symbol))
conn.commit()
cursor.close()
conn.close()
def getOHLCDataFromDB(self, timeframe=None):
timeframe = '1h'
conn = sqlite3.connect(self.dbname)
cursor = conn.cursor()
cursor.execute(
"SELECT open,high,low,close FROM bitmex_bar_1h ORDER BY id DESC LIMIT 200"
)
bar_1h_data = cursor.fetchall()
ohlcv = np.array(bar_1h_data)[::-1]
return ohlcv
def CheckLastOneHourData(self, symbol):
d = datetime.datetime.now().replace(
second=0, microsecond=0) + datetime.timedelta(hours=-1)
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
cursor.execute(
"SELECT * FROM bitmex_bar_1h WHERE date >= ? and symbol = ? ",
(d, symbol))
__count = cursor.fetchall()
if len(__count) == 0:
cursor.execute("DELETE FROM bitmex_bar_1h WHERE symbol = ?",
(symbol, ))
conn.commit()
cursor.close()
conn.close()
return False
else:
conn.commit()
cursor.close()
conn.close()
return True
def insertOneHourData(self, symbol):
_sql = []
ohlcv = self.onehour_200_data(symbol)
for _ohlcv in ohlcv:
_date = datetime.datetime.fromtimestamp(int(str(_ohlcv[0])[:10]))
__list = [
symbol, '1h', _ohlcv[1], _ohlcv[2], _ohlcv[3], _ohlcv[4],
_ohlcv[5], _date
]
_sql.append(__list)
conn = sqlite3.connect(self.dbname)
cursor = conn.cursor()
cursor.executemany(insert_bar_1h_little_sql, _sql)
conn.commit()
cursor.close()
conn.close()
def onehour_200_data(self, symbol=None):
if symbol == 'XBTUSD':
symbol = 'BTC/USD'
elif symbol == 'ETHUSD':
symbol = 'ETH/USD'
ohlcv1h = None
limit = 201
since = self.ex.milliseconds() - limit * 60 * 60 * 1000
params = {'partial': False}
while True:
try:
ohlcv1h = self.ex.fetch_ohlcv(symbol, '1h', since, limit + 1,
params)
break
except Exception as e:
print(e)
time.sleep(1)
if ohlcv1h is not None:
return ohlcv1h
def CheckDB_by_Symbol_in_timeframe(self,
symbol=None,
condition=None,
timedelta=None):
"""
Args:
condiction:'opening','grid'
symbol:'XBTUSD','ETHUSD'
timedelta:3600 minutes
Return:
if founded conditicon db,return True,else return False.
Example:
https://github.com/thombashi/SimpleSQLite
pip install SimpleSQLite
modify sqlquery.py in sites-packages/
con = SimpleSQLite('bitmex','a')
table_name = 'bitmex_bar_1h'
where_list = And([Where("symbol", "XBTUSD"), Where("id", 1, "<=")])
result = con.select(select="symbol,close",table_name=table_name,where=where_list)
for s in result.fetchall():
print(s)
con.fetch_num_records(table_name,where_list)
con.update(table_name, set_query="symbol = 'XBTUSD',close=3300", where=where_list)
"""
d = datetime.datetime.now().replace(
second=0, microsecond=0) + datetime.timedelta(minutes=-timedelta)
where_list = And([
Where("symbol", symbol, '='),
Where("ordertype", condition, "="),
Where("date", d, ">")
])
table_name = 'bitmex_orderhistory'
ordercount_in_one_day = self.con.fetch_num_records(
table_name, where_list)
where_list = And([
Where("symbol", symbol, '='),
Where("ordertype", condition, "="),
Where("date", d, "<=")
])
orderids_before_one_day = self.con.select(select="orderid",
table_name=table_name,
where=where_list)
for orderid in orderids_before_one_day.fetchall():
while True:
try:
self.ex.cancel_order(orderid[0])
self.con.delete(table_name,
where=Where("orderid", orderid[0], "="))
break
except Exception as e:
print(e)
time.sleep(1)
return ordercount_in_one_day
def on_fill_event(self, a):
symbol = a.dict_.symbol
orderid = a.dict_.orderid
conn = sqlite3.connect('bitmex')
cursor = conn.cursor()
cursor.execute(
"UPDATE bitmex_orderhistory SET status = 'filled' WHERE orderid = ? and symbol = ? ",
(orderid, symbol))
conn.commit()
cursor.close()
conn.close()
def create_table(self, dbname):
conn = sqlite3.connect(dbname)
try:
create_bitmex_bar_1m_cmd = '''
CREATE TABLE IF NOT EXISTS bitmex_bar_1m
(id INTEGER,
symbol text,
bar_type real,
open real,
high real,
low real,
close real,
volume real,
trades real,
buy real,
sell real,
date INTEGER,
PRIMARY KEY (id));
'''
create_bitmex_bar_1h_cmd = '''
CREATE TABLE IF NOT EXISTS bitmex_bar_1h
(id INTEGER,
symbol text,
bar_type real,
open real,
high real,
low real,
close real,
volume real,
trades real,
buy real,
sell real,
date INTEGER,
PRIMARY KEY (id));
'''
create_bitmex_orderhistory_cmd = '''
CREATE TABLE IF NOT EXISTS bitmex_orderhistory
(id INTEGER,
symbol text,
orderid text,
ordertype text,
orderprice real,
side text,
amount real,
status text,
date INTEGER,
PRIMARY KEY (id));
'''
conn.execute(create_bitmex_bar_1h_cmd)
conn.execute(create_bitmex_bar_1m_cmd)
conn.execute(create_bitmex_orderhistory_cmd)
except:
print("Create table failed")
conn.commit()
conn.close()
