def delete_duplicate_records(self, table: MyTable) -> int:
db_df_duplicate_id = DatabaseDataFrame(self, table.query_duplicate_id)
if db_df_duplicate_id.df.shape[0] == 0:
return 0
id_oid_keep_dict = {}
row_id_delete_list = []
db_df_id_oid = DatabaseDataFrame(self, table.query_id_oid)
key_columns = table.key_column_name_list
if len(key_columns) == 1:
duplicate_id_list = list(db_df_duplicate_id.df[key_columns])
else:
duplicate_id_list = []
for index, row in db_df_duplicate_id.df.iterrows():
record_id = '_'.join(
[str(row[column]) for column in key_columns])
duplicate_id_list.append(record_id)
for index, row in db_df_id_oid.df.iterrows():
if len(key_columns) == 1:
record_id = row[DC.ID]
else:
record_id = '_'.join(
[str(row[column]) for column in key_columns])
if record_id in duplicate_id_list:
row_id = row['rowid']
if record_id in id_oid_keep_dict:
row_id_delete_list.append(str(row_id))
else:
id_oid_keep_dict[record_id] = row_id
row_id_delete_concatenated = ','.join(row_id_delete_list)
query = 'DELETE FROM {} WHERE oid in ({});'.format(
table.name, row_id_delete_concatenated)
return self.delete_records(query)
def __init__(self, db: SalesmanDatabase, master_id='', and_clause=''):
clauses = []
if master_id != '':
clauses.append("{}='{}'".format(SLDC.MASTER_ID, master_id))
if and_clause != '':
clauses.append(and_clause)
where_clause = '' if len(clauses) == 0 else 'WHERE {}'.format(
' AND '.join(clauses))
self.statement = "SELECT * from {}{}".format(SMTBL.SALE, where_clause)
DatabaseDataFrame.__init__(self, db, self.statement)
def get_missing_trade_strategies_for_pattern_id(
self, pattern_id: str, check_against_strategy_dict: dict,
mean: int, sma_number: int) -> dict:
query = "SELECT ID, Pattern_id, Buy_Trigger, Trade_Strategy, Trade_Mean_Aggregation " \
"FROM trade where pattern_id = '{}'".format(pattern_id)
db_df = DatabaseDataFrame(self, query)
return_dict = {}
strategy_dict_exist = {}
for index, row in db_df.df.iterrows():
buy_trigger = row[DC.BUY_TRIGGER]
trade_strategy = row[DC.TRADE_STRATEGY]
mean_aggregation = row[DC.TRADE_MEAN_AGGREGATION]
is_combination_existing = False
if trade_strategy == TSTR.SMA and mean_aggregation == sma_number:
is_combination_existing = True
elif trade_strategy != TSTR.SMA and mean_aggregation == mean:
is_combination_existing = True
if is_combination_existing:
if buy_trigger not in strategy_dict_exist:
strategy_dict_exist[buy_trigger] = [trade_strategy]
else:
strategy_dict_exist[buy_trigger].append(trade_strategy)
for buy_trigger, trade_strategy_list in check_against_strategy_dict.items(
):
if buy_trigger not in strategy_dict_exist:
return_dict[buy_trigger] = trade_strategy_list
else:
exist_list = strategy_dict_exist[buy_trigger]
return_dict[buy_trigger] = [
strategy for strategy in trade_strategy_list
if strategy not in exist_list
]
return return_dict
def delete_existing_wave(self, wave_data_dict: dict):
query = self._wave_table.get_query_select_for_unique_record_by_dict(
wave_data_dict)
db_df = DatabaseDataFrame(self, query)
if db_df.df.shape[0] > 0:
rowid = db_df.df['rowid'][0]
self.delete_records("DELETE from {} WHERE rowid = {}".format(
STBL.WAVE, rowid))
def get_trade_records_for_asset_statistics_as_dataframe(
self) -> pd.DataFrame:
query = self._trade_table.get_query_select_for_records(
"Trade_Result_ID != 0 and Trade_Process = 'Online'")
query = self._trade_table.get_query_select_for_records(
"Trade_Result_ID != 0")
db_df = DatabaseDataFrame(self, query)
return db_df.df
def select_data_by_data_dict(self,
data_dict: dict,
target_columns=None,
sort_columns=None,
index_col='') -> pd.DataFrame:
query = self._table.get_query_select_by_data_dict(
data_dict, target_columns, sort_columns)
db_df = DatabaseDataFrame(self._stock_db, query)
return self.__get_dataframe_with_index_column__(db_df.df, index_col)
def __get_company_dict__(self,
symbol_input: str = '',
like_input: str = ''):
company_dict = {}
query = self._company_table.get_select_query(symbol_input, like_input)
db_df = DatabaseDataFrame(self, query)
for index, rows in db_df.df.iterrows():
company_dict[rows.Symbol] = rows
return company_dict
def update_trade_type_for_pattern(self, pattern_id: str, trade_type: str):
where_clause = "Pattern_ID = '{}'".format(pattern_id)
query = self._trade_table.get_query_select_for_records(where_clause)
db_df = DatabaseDataFrame(self, query)
trade_type_not = TRT.NOT_SHORT if trade_type == TRT.SHORT else TRT.NOT_LONG
trade_type_new = trade_type_not if db_df.df.shape[
0] == 0 else trade_type
self.update_table_column(STBL.PATTERN, DC.TRADE_TYPE, trade_type_new,
"ID = '{}'".format(pattern_id))
def is_symbol_loaded(self,
symbol: str,
and_clause='',
period=PRD.DAILY,
aggregation=1):
query = "SELECT * from {} WHERE Symbol = '{}' and Period = '{}' and Aggregation = {}".format(
STBL.STOCKS, symbol, period, aggregation)
query += '' if and_clause == '' else ' and {}'.format(and_clause)
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def is_trade_already_available_for_pattern_id(self, pattern_id,
buy_trigger: str,
strategy: str,
mean: int) -> bool:
where_clause = "Pattern_ID = '{}' and Trade_Mean_Aggregation = {} " \
"and Buy_Trigger = '{}' and Trade_Strategy = '{}'".format(
pattern_id, mean, buy_trigger, strategy
)
query = self._trade_table.get_query_select_for_records(where_clause)
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def get_pattern_differences_to_saved_version(self,
pattern_dict: dict) -> dict:
query = self._pattern_table.get_query_select_for_unique_record_by_id(
pattern_dict[DC.ID])
db_df = DatabaseDataFrame(self, query)
df_first = db_df.df.iloc[0]
return {
key: [str(df_first[key]),
str(pattern_dict[key])]
for key, values in pattern_dict.items()
if str(df_first[key]) != str(pattern_dict[key])
}
def __init__(self,
db: StockDatabase,
symbol='',
and_clause='',
period=PRD.DAILY,
aggregation=1):
self.symbol = symbol
self.statement = "SELECT * from {} WHERE Symbol = '{}' and Period = '{}' and Aggregation = {}".format(
STBL.STOCKS, symbol, period, aggregation)
if and_clause != '':
self.statement += ' and ' + and_clause
DatabaseDataFrame.__init__(self, db, self.statement)
if self.df.shape[0] == 0:
self.df_data = None
else:
self.df.set_index(CN.TIMESTAMP, drop=False, inplace=True)
self.column_data = [CN.CLOSE]
self.df_data = self.df[[
CN.OPEN, CN.HIGH, CN.LOW, CN.CLOSE, CN.VOL, CN.TIMESTAMP,
CN.BIG_MOVE, CN.DIRECTION
]]
def __get_last_loaded_time_stamp_dic__(self,
symbol_input: str = '',
like_input: str = '',
period=PRD.DAILY):
last_loaded_time_stamp_dic = {}
query = self._stocks_table.get_distinct_symbol_query(
symbol_input, like_input)
db_df = DatabaseDataFrame(self, query)
loaded_symbol_list = [
rows.Symbol for index, rows in db_df.df.iterrows()
]
query = 'SELECT Symbol, MAX(Timestamp) as Max_ts FROM {} WHERE Period = "{}" GROUP BY Symbol'.format(
STBL.STOCKS, period)
db_df = DatabaseDataFrame(self, query)
symbol_timestamp_dict_list = db_df.df.to_dict('records')
symbol_timestamp_dict = {}
for dict_entries in symbol_timestamp_dict_list:
symbol_timestamp_dict[
dict_entries['Symbol']] = dict_entries['Max_ts']
for symbol in loaded_symbol_list:
last_loaded_time_stamp_dic[symbol] = symbol_timestamp_dict.get(
symbol, 0)
return last_loaded_time_stamp_dic
def is_saved_pattern_version_without_ticks_previous_period(
self, pattern_id: str) -> bool:
query = self._pattern_table.get_query_select_for_unique_record_by_id(
pattern_id)
db_df = DatabaseDataFrame(self, query)
df_first = db_df.df.iloc[0]
ticks_previous_period_columns = [
DC.TICKS_PREVIOUS_PERIOD_HALF_TOP_OUT_TILL_PATTERN,
DC.TICKS_PREVIOUS_PERIOD_FULL_TOP_OUT_TILL_PATTERN,
DC.TICKS_PREVIOUS_PERIOD_HALF_BOTTOM_OUT_TILL_PATTERN,
DC.TICKS_PREVIOUS_PERIOD_FULL_BOTTOM_OUT_TILL_PATTERN
]
for ticks_columns in ticks_previous_period_columns:
if df_first[ticks_columns] != 0:
return False
return True
def get_wave_records_for_recommender_as_dataframe(
self, limit: int) -> pd.DataFrame:
query = self._wave_table.get_query_for_recommender_records(limit)
db_df = DatabaseDataFrame(self, query)
return db_df.df
def select_data_by_query(self, query: str, index_col='') -> pd.DataFrame:
# print('query={}'.format(query))
db_df = DatabaseDataFrame(self._db, query)
self._counter_processed += db_df.df.shape[0]
return self.__get_dataframe_with_index_column__(db_df.df, index_col)
def __init__(self, db: SalesmanDatabase, offer_id_master: str):
self.offer_id_master = offer_id_master
self.statement = "SELECT * from {} WHERE {} = '{}'".format(SMVW.V_SALE, SLDC.MASTER_ID, offer_id_master)
DatabaseDataFrame.__init__(self, db, self.statement)
if self.df.shape[0] == 0:
self.df_data = None
def get_pattern_records_for_replay_as_dataframe(self) -> pd.DataFrame:
query = self._pattern_table.get_query_select_for_records(
"Period = 'DAILY'")
db_df = DatabaseDataFrame(self, query)
return db_df.df[db_df.df[DC.PATTERN_TYPE].isin(
FT.get_long_trade_able_types())]
def is_stock_data_already_available(self, symbol: str, time_stamp: int,
period: str, aggregation: int) -> bool:
query = self._stocks_table.get_query_for_unique_record(
symbol, time_stamp, period, aggregation)
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def get_trade_records_for_replay_as_dataframe(self) -> pd.DataFrame:
query = self._trade_table.get_query_select_for_records(
"Trade_Result_ID != 0 AND Period = 'DAILY'")
db_df = DatabaseDataFrame(self, query)
return db_df.df
def get_pattern_id_for_trade_id(self, trade_id: str) -> str:
query = "SELECT id, pattern_id FROM trade WHERE id = '{}'".format(
trade_id)
db_df = DatabaseDataFrame(self, query)
return db_df.df.iloc[0][DC.PATTERN_ID]
def is_wave_already_available(self, wave_data_dict: dict) -> bool:
query = self._wave_table.get_query_select_for_unique_record_by_dict(
wave_data_dict)
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def get_asset_records_for_statistics_as_dataframe(self) -> pd.DataFrame:
query = self._asset_table.get_query_select_for_records()
db_df = DatabaseDataFrame(self, query)
return db_df.df
def get_trade_records_for_trading_optimizer_dataframe(
self) -> pd.DataFrame:
query = self._trade_table.get_query_for_trading_optimizer()
db_df = DatabaseDataFrame(self, query)
return db_df.df
def get_trade_records_for_statistics_as_dataframe(self) -> pd.DataFrame:
query = self._trade_table.get_query_select_for_records(
"Trade_Result_ID != 0")
db_df = DatabaseDataFrame(self, query)
# print('query={}, found={}'.format(query, db_df.df.shape[0]))
return db_df.df
def get_trade_records_as_dataframe(self, where_clause='') -> pd.DataFrame:
query = self._trade_table.get_query_select_for_records(where_clause)
db_df = DatabaseDataFrame(self, query)
return db_df.df
def is_trade_already_available(self, trade_id: str) -> bool:
query = self._trade_table.get_query_select_for_unique_record_by_id(
trade_id)
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def is_any_asset_already_available_for_timestamp(self, time_stamp: int):
query = self._asset_table.get_query_select_for_records(
'Validity_Timestamp={}'.format(time_stamp))
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def is_equity_already_available(self, input_data_dict: dict):
query = self._entity_table.get_query_select_for_unique_record_by_dict(
input_data_dict)
# print('is_equity_already_available: query={}'.format(query))
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def get_wave_counter_dict(self, period: str, limit: int = 0):
query = self._wave_table.get_query_for_wave_counter(period, limit)
db_df = DatabaseDataFrame(self, query)
return {row[0]: row[1] for index, row in db_df.df.iterrows()}
