def test_normal_file( self, tmpdir, json_text, filename, table_name, expected_table_name, expected_attr_names, expected_data_matrix, ): p_db = tmpdir.join("tmp.db") p_json = tmpdir.join(filename) with open(str(p_json), "w") as f: f.write(json_text) con = SimpleSQLite(str(p_db), "w") con.create_table_from_json(str(p_json), table_name) assert con.fetch_table_names() == [expected_table_name] assert expected_attr_names == con.fetch_attr_names(expected_table_name) result = con.select(select="*", table_name=expected_table_name) result_matrix = result.fetchall() assert len(result_matrix) == 3 assert result_matrix == expected_data_matrix
def test_normal_file( self, tmpdir, json_text, filename, table_name, expected_table_name, expected_attr_names, expected_data_matrix, ): p_db = tmpdir.join("tmp.db") p_json = tmpdir.join(filename) with open(str(p_json), "w") as f: f.write(json_text) con = SimpleSQLite(str(p_db), "w") try: con.create_table_from_json(str(p_json), table_name) except ImportError: pytest.skip("requires pytablereader") assert con.fetch_table_names() == [expected_table_name] assert expected_attr_names == con.fetch_attr_names(expected_table_name) result = con.select(select="*", table_name=expected_table_name) result_matrix = result.fetchall() assert len(result_matrix) == 3 assert result_matrix == expected_data_matrix
def main(): file_path = "sample_data_multi.json" # create sample data file --- with open(file_path, "w") as f: f.write("""{ "table_a" : [ {"attr_b": 4, "attr_c": "a", "attr_a": 1}, {"attr_b": 2.1, "attr_c": "bb", "attr_a": 2}, {"attr_b": 120.9, "attr_c": "ccc", "attr_a": 3} ], "table_b" : [ {"a": 1, "b": 4}, {"a": 2 }, {"a": 3, "b": 120.9} ] }""") # create table --- con = SimpleSQLite("sample.sqlite", "w") con.create_table_from_json(file_path) # output --- for table_name in con.fetch_table_names(): print("table: " + table_name) print(con.fetch_attr_names(table_name)) result = con.select(select="*", table_name=table_name) for record in result.fetchall(): print(record) print()
class SimpleSqliteCilent(object): def __init__(self): self.con = SimpleSQLite("pandas_df.sqlite") def createTableWithDf(self, dataframe, tableName): self.con.create_table_from_dataframe(dataframe, tableName) def dropTable(self, tableName): self.con.drop_table(tableName) def createTableWithList(self, tableName, columns): self.con.create_table(tableName, columns) def insertOneRow(self, tableName, columns): self.con.insert(tableName, columns) """ 返回的是tuple形式的结果 """ def query(self, tableName, whereDict, operation="=", extra=None): print len(whereDict.keys()) print "---------------" if len(whereDict.keys()) != 1: raise NotSupportParamError() return self.con.select(select="*", table_name=tableName,\ where=SqlQuery.make_where(key=whereDict.keys()[0], value=whereDict[whereDict.keys()[0]],operation=operation),extra=extra).fetchall() """ 根据where条件更新,目前仅支持1个条件 """ def update(self, tableName, set_queryDict, whereDict): print len(whereDict.keys()) print "---------------" if len(whereDict.keys()) != 1: raise NotSupportParamError() set_queryStr = "" for setKey, setValue in set_queryDict.items(): set_queryStr.join(setKey).join("=").join(setValue) return self.con.update(tableName,set_query ="", \ where=SqlQuery.make_where(key=whereDict.keys()[0], \ value=whereDict[whereDict.keys()[0]])).fetchall() """ 插入字典值形式的记录 """ def insertMany(self, tableName, inert_dictList): self.con.insert_many(tableName, inert_dictList) ##pass def __del__(self): if self.con is not None: self.con.close()
def main(): table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") data_matrix = [[1, "aaa"], [2, "bbb"]] con.create_table_from_data_matrix(table_name, ["key", "value"], data_matrix) print("---- before update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record) print() con.update(table_name, set_query="value = 'ccc'", where=Where(key="key", value=1)) print("---- after update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record)
def test_normal(self, tmpdir, value, expected): p_db = tmpdir.join("tmp.db") con = SimpleSQLite(str(p_db), "w") con.create_table_from_tabledata(value) assert con.fetch_table_names() == [value.table_name] assert con.fetch_attr_names(value.table_name) == value.headers result = con.select(select="*", table_name=value.table_name) result_matrix = result.fetchall() assert result_matrix == expected actual = con.select_as_tabledata(columns=value.headers, table_name=value.table_name) assert actual.equals(value)
def test_normal_multi_file_same_table_same_structure(self): db_path = "test.sqlite" runner = CliRunner() with runner.isolated_filesystem(): file_list = [ valid_json_multi_file_2_1(), valid_json_multi_file_2_2() ] result = runner.invoke(cmd, ["-o", db_path, "file"] + file_list) print_traceback(result) assert result.exit_code == ExitCode.SUCCESS con = SimpleSQLite(db_path, "r") expected_table_list = ["multij2", SourceInfo.get_table_name()] actual_table_list = con.fetch_table_names() print_test_result(expected=expected_table_list, actual=actual_table_list) assert set(actual_table_list) == set(expected_table_list) expected_data_table = { "multij2": [ (1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc"), (1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc"), ] } for table in con.fetch_table_names(): if table == SourceInfo.get_table_name(): continue expected_data = expected_data_table.get(table) actual_data = con.select("*", table_name=table).fetchall() message = "table={}, expected={}, actual={}".format( table, expected_data, actual_data) print("--- table: {} ---".format(table)) print_test_result(expected=expected_data, actual=actual_data) assert expected_data == actual_data, message
def simpledb(): return_array = [] temp_dict = {} print("In the simpledb function") table_name = "sample_table" con = SimpleSQLite("simple.sqlite", "w") print("after con") # create table ----- data_matrix = [[1, 1.1, "aaa", 1, 1], [2, 2.2, "bbb", 2.2, 2.2], [3, 3.3, "ccc", 3, "ccc"]] con.create_table_from_data_matrix( table_name, ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], data_matrix, ) con.insert(table_name, record={ "attr_a": 4, "attr_b": 4.4, "attr_c": "ddd", "attr_d": 4.44, "attr_e": "hoge", }) # display data type for each column in the table ----- print(con.schema_extractor.fetch_table_schema(table_name).dumps()) # display values in the table ----- print("records:") key_array = [ "attr_a", "attr_b", "attr_c", "attr_d", "attr_e", ] result = con.select(select="*", table_name=table_name) print(type(result)) for record in result.fetchall(): print(record) temp_dict = dict(zip(key_array, record)) return_array.append(temp_dict) temp_dict = [] print("Last line of the simpledb function:") return json.dumps(return_array)
def test_normal_text(self, tmpdir, csv_text, table_name, attr_name_list, expected_table_name, expected_attr_name_list, expected_data_matrix): p_db = tmpdir.join("tmp.db") con = SimpleSQLite(str(p_db), "w") con.create_table_from_csv(csv_text, table_name, attr_name_list) assert con.get_table_name_list() == [expected_table_name] assert expected_attr_name_list == con.get_attr_name_list( expected_table_name) result = con.select(select="*", table_name=expected_table_name) result_matrix = result.fetchall() assert len(result_matrix) == 3 assert result_matrix == expected_data_matrix
def test_normal(self, tmpdir, attr_names, data_matrix, index_attrs, expected_attr): p = tmpdir.join("tmp.db") con = SimpleSQLite(str(p), "w") table_name = TEST_TABLE_NAME con.create_table_from_data_matrix( table_name, attr_names, data_matrix, primary_key=None, index_attrs=index_attrs ) # check data --- result = con.select(select=AttrList(attr_names), table_name=table_name) result_matrix = result.fetchall() assert len(result_matrix) == 3 print_test_result(expected=expected_attr, actual=con.fetch_attr_type(table_name)) assert con.fetch_attr_type(table_name) == expected_attr
def to_table_data(self): from simplesqlite import SimpleSQLite from simplesqlite.sqlquery import SqlQuery con = SimpleSQLite(self._source_data, "r") for table in con.get_table_name_list(): self.__table_name = table attr_name_list = con.get_attr_name_list(table) data_matrix = con.select( select=",".join(SqlQuery.to_attr_str_list(attr_name_list)), table_name=table) yield TableData( table, attr_name_list, data_matrix)
def main(): with open("sample_data.csv", "w") as f: f.write("\n".join([ '"attr_a","attr_b","attr_c"', '1,4,"a"', '2,2.1,"bb"', '3,120.9,"ccc"' ])) # create table --- con = SimpleSQLite("sample.sqlite", "w") con.create_table_from_csv("sample_data.csv") # output --- table_name = "sample_data" print(con.fetch_attr_names(table_name)) result = con.select(select="*", table_name=table_name) for record in result.fetchall(): print(record)
def to_table_data(self): from simplesqlite import SimpleSQLite from simplesqlite.query import AttrList con = SimpleSQLite(self._source_data, "r") for table in con.get_table_name_list(): self.__table_name = table attr_name_list = con.get_attr_name_list(table) data_matrix = con.select(select=AttrList(attr_name_list), table_name=table).fetchall() yield TableData(table, attr_name_list, data_matrix, dp_extractor=self._loader.dp_extractor)
def test_normal_multi_file_same_table_same_structure(self): db_path = "test.sqlite" runner = CliRunner() with runner.isolated_filesystem(): files = [valid_json_multi_file_2_1(), valid_json_multi_file_2_2()] result = runner.invoke(cmd, ["-o", db_path, "file"] + files) print_traceback(result) assert result.exit_code == ExitCode.SUCCESS con = SimpleSQLite(db_path, "r") expected_tables = ["multij2", SourceInfo.get_table_name()] actual_tables = con.fetch_table_names() print_test_result(expected=expected_tables, actual=actual_tables) assert set(actual_tables) == set(expected_tables) expected_data_table = { "multij2": [ (1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc"), (1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc"), ] } for table in con.fetch_table_names(): if table == SourceInfo.get_table_name(): continue expected_data = expected_data_table.get(table) actual_data = con.select("*", table_name=table).fetchall() message = "table={}, expected={}, actual={}".format( table, expected_data, actual_data ) print("--- table: {} ---".format(table)) print_test_result(expected=expected_data, actual=actual_data) assert expected_data == actual_data, message
def main(): table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") # create table ----- data_matrix = [[1, 1.1, "aaa", 1, 1], [2, 2.2, "bbb", 2.2, 2.2], [3, 3.3, "ccc", 3, "ccc"]] con.create_table_from_data_matrix( table_name, ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], data_matrix) # display data type for each column in the table ----- print(con.schema_extractor.fetch_table_schema(table_name).dumps()) # display values in the table ----- print("records:") result = con.select(select="*", table_name=table_name) for record in result.fetchall(): print(record)
def test_normal_file(self, tmpdir, json_text, filename, table_name, expected_table_name, expected_attr_name_list, expected_data_matrix): p_db = tmpdir.join("tmp.db") p_json = tmpdir.join(filename) with open(str(p_json), "w") as f: f.write(json_text) con = SimpleSQLite(str(p_db), "w") con.create_table_from_json(str(p_json), table_name) assert con.get_table_name_list() == [expected_table_name] assert expected_attr_name_list == con.get_attr_name_list( expected_table_name) result = con.select(select="*", table_name=expected_table_name) result_matrix = result.fetchall() assert len(result_matrix) == 3 assert result_matrix == expected_data_matrix
def test_normal(self, tmpdir, attr_name_list, data_matrix, index_attr_list, expected_attr): p = tmpdir.join("tmp.db") con = SimpleSQLite(str(p), "w") table_name = TEST_TABLE_NAME con.create_table_from_data_matrix(table_name, attr_name_list, data_matrix, index_attr_list) con.commit() # check data --- result = con.select(select=",".join( SqlQuery.to_attr_str_list(attr_name_list)), table_name=table_name) result_matrix = result.fetchall() assert len(result_matrix) == 3 print("expected: {}".format(expected_attr)) print("actual: {}".format(con.get_attr_type(table_name))) assert con.get_attr_type(table_name) == expected_attr
def main(): table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") con.create_table_from_data_matrix( table_name, ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], [[1, 1.1, "aaa", 1, 1]] ) # insert namedtuple SampleTuple = namedtuple("SampleTuple", "attr_a attr_b attr_c attr_d attr_e") con.insert(table_name, record=[7, 7.7, "fff", 7.77, "bar"]) con.insert_many( table_name, records=[(8, 8.8, "ggg", 8.88, "foobar"), SampleTuple(9, 9.9, "ggg", 9.99, "hogehoge")], ) # print result = con.select(select="*", table_name=table_name) for record in result.fetchall(): print(record)
def to_table_data(self): from simplesqlite import SimpleSQLite from simplesqlite.query import AttrList con = SimpleSQLite(self._source_data, "r") for table in con.fetch_table_names(): self.__table_name = table attr_names = con.fetch_attr_names(table) data_matrix = con.select(select=AttrList(attr_names), table_name=table).fetchall() yield TableData( table, attr_names, data_matrix, dp_extractor=self._loader.dp_extractor, type_hints=self._extract_type_hints(attr_names), )
def test_normal(self, tmpdir, attr_names, data_matrix, index_attrs, expected_attr): p = tmpdir.join("tmp.db") con = SimpleSQLite(str(p), "w") table_name = TEST_TABLE_NAME con.create_table_from_data_matrix(table_name, attr_names, data_matrix, primary_key=None, index_attrs=index_attrs) # check data --- result = con.select(select=AttrList(attr_names), table_name=table_name) result_matrix = result.fetchall() assert len(result_matrix) == 3 print_test_result(expected=expected_attr, actual=con.fetch_attr_type(table_name)) assert con.fetch_attr_type(table_name) == expected_attr
def main(): table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") con.create_table_from_data_matrix( table_name, ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], [[1, 1.1, "aaa", 1, 1]] ) con.insert( table_name, record={"attr_a": 4, "attr_b": 4.4, "attr_c": "ddd", "attr_d": 4.44, "attr_e": "hoge"}, ) con.insert_many( table_name, records=[ {"attr_a": 5, "attr_b": 5.5, "attr_c": "eee", "attr_d": 5.55, "attr_e": "foo"}, {"attr_a": 6, "attr_c": "fff"}, ], ) result = con.select(select="*", table_name=table_name) for record in result.fetchall(): print(record)
def test_normal_text( self, tmpdir, csv_text, table_name, attr_names, expected_table_name, expected_attr_names, expected_data_matrix, ): p_db = tmpdir.join("tmp.db") con = SimpleSQLite(str(p_db), "w") con.create_table_from_csv(csv_text, table_name, attr_names) assert con.fetch_table_names() == [expected_table_name] assert expected_attr_names == con.fetch_attr_names(expected_table_name) result = con.select(select="*", table_name=expected_table_name) result_matrix = result.fetchall() assert len(result_matrix) == 3 assert result_matrix == expected_data_matrix
#!/usr/bin/env python # encoding: utf-8 from __future__ import print_function from simplesqlite import SimpleSQLite from simplesqlite.query import Where table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") data_matrix = [[1, "aaa"], [2, "bbb"]] con.create_table_from_data_matrix(table_name, ["key", "value"], data_matrix) print("---- before update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record) print() con.update(table_name, set_query="value = 'ccc'", where=Where(key="key", value=1)) print("---- after update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record)
#!/usr/bin/env python # encoding: utf-8 import json from simplesqlite import SimpleSQLite import six table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") # create table ----- data_matrix = [ [1, 1.1, "aaa", 1, 1], [2, 2.2, "bbb", 2.2, 2.2], [3, 3.3, "ccc", 3, "ccc"], ] con.create_table_with_data( table_name, attribute_name_list=["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], data_matrix=data_matrix) # display values in the table ----- six.print_(con.get_attribute_name_list(table_name)) result = con.select(select="*", table_name=table_name) for record in result.fetchall(): six.print_(record) # display data type for each column in the table ----- six.print_(json.dumps(con.get_attr_type(table_name), indent=4))
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()
# create sample data file --- with open(file_path, "w") as f: f.write( """{ "table_a" : [ {"attr_b": 4, "attr_c": "a", "attr_a": 1}, {"attr_b": 2.1, "attr_c": "bb", "attr_a": 2}, {"attr_b": 120.9, "attr_c": "ccc", "attr_a": 3} ], "table_b" : [ {"a": 1, "b": 4}, {"a": 2 }, {"a": 3, "b": 120.9} ] }""" ) # create table --- con = SimpleSQLite("sample.sqlite", "w") con.create_table_from_json(file_path) # output --- for table_name in con.fetch_table_names(): print("table: " + table_name) print(con.fetch_attr_names(table_name)) result = con.select(select="*", table_name=table_name) for record in result.fetchall(): print(record) print()
def test_normal_append(self): db_path = "test.sqlite" runner = CliRunner() with runner.isolated_filesystem(): files = [valid_json_multi_file_2_1()] table_name = "multij2" expected_tables = [table_name, SourceInfo.get_table_name()] # first execution without --append option (new) --- result = runner.invoke(cmd, ["-o", db_path, "file"] + files) print_traceback(result) assert result.exit_code == ExitCode.SUCCESS con = SimpleSQLite(db_path, "r") actual_tables = con.fetch_table_names() print_test_result(expected=expected_tables, actual=actual_tables) assert set(actual_tables) == set(expected_tables) actual_data = con.select("*", table_name=table_name).fetchall() expected_data = [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")] print_test_result(expected=expected_data, actual=actual_data) assert expected_data == actual_data # second execution with --append option --- result = runner.invoke(cmd, ["-o", db_path, "--append", "file"] + files) print_traceback(result) assert result.exit_code == ExitCode.SUCCESS con = SimpleSQLite(db_path, "r") actual_tables = con.fetch_table_names() print_test_result(expected=expected_tables, actual=actual_tables) assert set(actual_tables) == set(expected_tables) actual_data = con.select("*", table_name=table_name).fetchall() expected_data = [ (1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc"), (1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc"), ] print_test_result(expected=expected_data, actual=actual_data) assert expected_data == actual_data # third execution without --append option (overwrite) --- result = runner.invoke(cmd, ["-o", db_path, "file"] + files) print_traceback(result) assert result.exit_code == ExitCode.SUCCESS con = SimpleSQLite(db_path, "r") actual_tables = con.fetch_table_names() print_test_result(expected=expected_tables, actual=actual_tables) assert set(actual_tables) == set(expected_tables) actual_data = con.select("*", table_name=table_name).fetchall() expected_data = [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")] print_test_result(expected=expected_data, actual=actual_data) assert expected_data == actual_data
def test_normal_multi_file_different_table(self): db_path = "test.sqlite" runner = CliRunner() with runner.isolated_filesystem(): files = [ valid_json_single_file(), valid_json_single_b_file(), valid_json_multi_file_1(), valid_json_kv_file(), valid_csv_file_1_1(), valid_csv_file_2_1(), invalid_csv_file(), valid_tsv_file(), invalid_tsv_file(), valid_excel_file(), invalid_excel_file_1(), invalid_excel_file_2(), valid_html_file(), invalid_html_file(), valid_ltsv_file(), invalid_ltsv_file(), valid_markdown_file(), not_supported_format_file(), ] result = runner.invoke(cmd, ["-o", db_path, "file"] + files) assert result.exit_code == ExitCode.SUCCESS con = SimpleSQLite(db_path, "r") expected_tables = [ "2018Asset", "singlejson", "multij1", "multij2", "valid_kv", "csv_a", "rename_insert", "excel_sheet_a", "excel_sheet_c", "excel_sheet_d", "valid_ltsv_a", "testtitle_tablename", "testtitle_html2", "tsv_a", "valid_mdtable_markdown1", "root", SourceInfo.get_table_name(), ] actual_tables = con.fetch_table_names() print_test_result(expected=expected_tables, actual=actual_tables) assert set(actual_tables) == set(expected_tables) expected_data_table = { "2018Asset": [(1, "1billion", "item_entertainment_18_212")], "singlejson": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")], "multij1": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")], "multij2": [(1, 4.0), (2, None), (3, 120.9)], "valid_kv": [("json_b", "hoge"), ("json_c", "bar")], "csv_a": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")], "rename_insert": [ (1, 55, "D Sam", 31, "Raven"), (2, 36, "J Ifdgg", 30, "Raven"), (3, 91, "K Wedfb", 28, "Raven"), ], "excel_sheet_a": [(1.0, 1.1, "a"), (2.0, 2.2, "bb"), (3.0, 3.3, "cc")], "excel_sheet_c": [(1, 1.1, "a"), (2, "", "bb"), (3, 3.3, "")], "excel_sheet_d": [(1, 1.1, "a"), (2, "", "bb"), (3, 3.3, "")], "testtitle_tablename": [(1, 123.1, "a"), (2, 2.2, "bb"), (3, 3.3, "ccc")], "valid_ltsv_a": [ (1, 123.1, '"ltsv0"', 1.0, '"1"'), (2, 2.2, '"ltsv1"', 2.2, '"2.2"'), (3, 3.3, '"ltsv2"', 3.0, '"cccc"'), ], "testtitle_html2": [(1, 123.1), (2, 2.2), (3, 3.3)], "tsv_a": [(1, 4.0, "tsv0"), (2, 2.1, "tsv1"), (3, 120.9, "tsv2")], "valid_mdtable_markdown1": [(1, 123.1, "a"), (2, 2.2, "bb"), (3, 3.3, "ccc")], } for table in con.fetch_table_names(): if table in (SourceInfo.get_table_name(), "root"): continue result = con.select("*", table_name=table) expected_data = expected_data_table.get(table) actual_data = result.fetchall() message = "table={}, expected={}, actual={}".format( table, expected_data, actual_data) print("--- table: {} ---".format(table)) print_test_result(expected=expected_data, actual=actual_data) assert sorted(expected_data) == sorted(actual_data), message
def test_normal_multi_file_different_table(self): db_path = "test.sqlite" runner = CliRunner() with runner.isolated_filesystem(): files = [ valid_json_single_file(), invalid_json_single_file(), valid_json_multi_file_1(), valid_json_kv_file(), valid_csv_file_1_1(), valid_csv_file_2_1(), invalid_csv_file(), valid_tsv_file(), invalid_tsv_file(), valid_excel_file(), invalid_excel_file_1(), invalid_excel_file_2(), valid_html_file(), invalid_html_file(), valid_ltsv_file(), invalid_ltsv_file(), valid_markdown_file(), not_supported_format_file(), ] result = runner.invoke(cmd, ["-o", db_path, "file"] + files) assert result.exit_code == ExitCode.SUCCESS con = SimpleSQLite(db_path, "r") expected_tables = [ "singlejson", "multij1", "multij2", "valid_kv", "csv_a", "rename_insert", "excel_sheet_a", "excel_sheet_c", "excel_sheet_d", "valid_ltsv_a", "testtitle_tablename", "testtitle_html2", "tsv_a", "valid_mdtable_markdown1", SourceInfo.get_table_name(), ] actual_tables = con.fetch_table_names() print_test_result(expected=expected_tables, actual=actual_tables) assert set(actual_tables) == set(expected_tables) expected_data_table = { "singlejson": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")], "multij1": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")], "multij2": [(1, 4.0), (2, None), (3, 120.9)], "valid_kv": [("json_b", "hoge"), ("json_c", "bar")], "csv_a": [(1, 4.0, "a"), (2, 2.1, "bb"), (3, 120.9, "ccc")], "rename_insert": [ (1, 55, "D Sam", 31, "Raven"), (2, 36, "J Ifdgg", 30, "Raven"), (3, 91, "K Wedfb", 28, "Raven"), ], "excel_sheet_a": [(1.0, 1.1, "a"), (2.0, 2.2, "bb"), (3.0, 3.3, "cc")], "excel_sheet_c": [(1, 1.1, "a"), (2, "", "bb"), (3, 3.3, "")], "excel_sheet_d": [(1, 1.1, "a"), (2, "", "bb"), (3, 3.3, "")], "testtitle_tablename": [(1, 123.1, "a"), (2, 2.2, "bb"), (3, 3.3, "ccc")], "valid_ltsv_a": [ (1, 123.1, u'"ltsv0"', 1.0, u'"1"'), (2, 2.2, u'"ltsv1"', 2.2, u'"2.2"'), (3, 3.3, u'"ltsv2"', 3.0, u'"cccc"'), ], "testtitle_html2": [(1, 123.1), (2, 2.2), (3, 3.3)], "tsv_a": [(1, 4.0, "tsv0"), (2, 2.1, "tsv1"), (3, 120.9, "tsv2")], "valid_mdtable_markdown1": [(1, 123.1, "a"), (2, 2.2, "bb"), (3, 3.3, "ccc")], } for table in con.fetch_table_names(): if table == SourceInfo.get_table_name(): continue result = con.select("*", table_name=table) expected_data = expected_data_table.get(table) actual_data = result.fetchall() message = "table={}, expected={}, actual={}".format( table, expected_data, actual_data ) print("--- table: {} ---".format(table)) print_test_result(expected=expected_data, actual=actual_data) assert sorted(expected_data) == sorted(actual_data), message