def update_scrip_master(scrip_code_id_list): try: scrip_code_id_list = tuple(scrip_code_id_list) connection = db.prod_db_conn() c = connection.cursor() # print(scrip_code_id_list) update_query = '''update scrip_master set isactive ='t' where isactive ='y' and id in {id} '''.format(id = scrip_code_id_list) c.execute(update_query) # commit the transaction connection.commit() # close the database communication c.close() except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) ## finally block Alwasy execute whether exception occured or not ## to close db connection finally: close_db_connection(connection)
def get_screener_list(): try: connection = db.prod_db_conn() c = connection.cursor() get_screener_list_query = ''' SELECT data_table_id, sector, screener_file, output_colomns, csv_output_path, screener_name FROM public.screener_master sm where sm.isdeleted = 'n' ''' c.execute(get_screener_list_query) screener_list = c.fetchall() # close the database communication c.close() return screener_list except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) finally: close_db_connection(connection)
def update_empty_file_status(scrip_code_id_list): try: print(scrip_code_id_list) scrip_code_id_list = tuple(scrip_code_id_list) connection = db.prod_db_conn() c = connection.cursor() # print(scrip_code_id_list) update_query = '''update scrip_master set isactive ='e' where id in {id} '''.format(id = scrip_code_id_list) c.execute(update_query) # commit the transaction connection.commit() # close the database communication c.close() print(str(scrip_code_id_list) + ":: Scrip code staus updated with flag e for empty file") logging.info(str(scrip_code_id_list) + " ::Scrip code staus updated with flag e for empty file") except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) ## finally block Alwasy execute whether exception occured or not ## to close db connection finally: close_db_connection(connection)
def save_data_error(scrip_code_id_list,error_text,exchange,function_name): try: data_error_tuple = [(scrip_code_id_list,error_text,exchange,function_name)] connection = db.prod_db_conn() c = connection.cursor() records_list_template = ','.join(['%s'] * len(data_error_tuple)) insert_query = '''insert into data_error_log (scrip_code_id_list,error,exchange,functin_name) values {r_l_t} '''.format(r_l_t= records_list_template) c.execute(insert_query, data_error_tuple) # commit the transaction connection.commit() # close the database communication c.close() print(exchange + " Data error record inserted successfully in :: data_error_log table") logging.info(exchange + " Data error record inserted successfully in :: data_error_log table") except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) ## finally block Alwasy execute whether exception occured or not ## to close db connection finally: close_db_connection(connection)
def save_scrip_master(scrip_master_ex_list, exchange): try: connection = db.prod_db_conn() c = connection.cursor() records_list_template = ','.join(['%s'] * len(scrip_master_ex_list)) insert_query = '''insert into scrip_master (scrip_code, company_name, market_type, exchange,timeframe,file_name,isactive, created_timestamp, last_updated) values {r_l_t} ON CONFLICT (scrip_code,file_name) DO UPDATE SET market_type = excluded.market_type, exchange = excluded.exchange, company_name = excluded.company_name '''.format(r_l_t= records_list_template) c.execute(insert_query, scrip_master_ex_list) # commit the transaction connection.commit() # close the database communication c.close() print("Records inserted successfully in :: scrip_master for exchange " + exchange) logging.info("Records inserted successfully in :: scrip_master for exchange " + str(exchange)) except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) ## finally block Alwasy execute whether exception occured or not ## to close db connection finally: close_db_connection(connection)
def check_table_exists(table_name): try: connection = db.prod_db_conn() c = connection.cursor() query = ''' SELECT EXISTS ( SELECT 1 FROM pg_tables WHERE tablename = '{t_n}' );'''.format(t_n = table_name) c.execute(query) result = c.fetchone()[0] # close the database communication c.close() return result except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) finally: close_db_connection(connection)
def insert_to_screener_value_table(data_tuple): try: connection = db.prod_db_conn() c = connection.cursor() screener_value_insert = ''' insert into screener_value_table (screener_name,date_time,data_json,last_updated) values {d_t} on conflict (screener_name, date_time) do update set data_json = excluded.data_json, last_updated = excluded.last_updated '''.format(d_t=data_tuple) c.execute(screener_value_insert) connection.commit() # close the database communication c.close() except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) finally: close_db_connection(connection)
def get_datasource_list(): try: connection = db.prod_db_conn() c = connection.cursor() get_datasource_list_query = ''' select id,exchange,timeframe_m,market_type,path, handler_path,resample_tf from datasource_master where isactive = 'y' ''' c.execute(get_datasource_list_query) datasource_list = c.fetchall() # close the database communication c.close() return datasource_list except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) finally: close_db_connection(connection)
def get_scrip_list(exchange,market_type,timeframe): try: connection = db.prod_db_conn() c = connection.cursor() get_scrip_list_query = ''' select scrip_code, company_name,sm.file_name, dm.timeframe_m as tf,sm.id from scrip_master sm inner join datasource_master dm on dm.exchange = sm.exchange and dm.timeframe_m = sm.timeframe and dm.market_type = sm.market_type where dm.timeframe_m = {tf} and dm.exchange = '{ex}' and dm.market_type = '{mt}' and sm.isactive in ('y','t') '''.format(ex=exchange, mt=market_type, tf=timeframe) c.execute(get_scrip_list_query) scrip_list = c.fetchall() # close the database communication c.close() return scrip_list except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) finally: close_db_connection(connection)
def drop_table(table_name): try: connection = db.prod_db_conn() c = connection.cursor() if table_name is not None: drop_old_table = """drop table if exists {table};""".format(table = table_name) c.execute(drop_old_table) connection.commit() print(table_name + " : table deleted successfully") logging.info(table_name + " : table deleted successfully") except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) ## finally block Alwasy execute whether exception occured or not ## to close db connection finally: close_db_connection(connection)
def rename_table(from_table,to_table): try: connection = db.prod_db_conn() c = connection.cursor() if from_table is not None and to_table is not None: rename_current_table_query = """ALTER TABLE IF EXISTS {frm_table} RENAME TO {to_table};""".format(frm_table = from_table,to_table = to_table) c.execute(rename_current_table_query) connection.commit() print("""Table rename from {f_t} to {to_t}""".format(f_t =from_table,to_t = to_table)) logging.info("""Table rename from {f_t} to {to_t}""".format(f_t =from_table,to_t = to_table)) except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) ## finally block Alwasy execute whether exception occured or not ## to close db connection finally: close_db_connection(connection)
def get_ticker_names(): connection = ds.prod_db_conn() cursor = connection.cursor() record = return_record( cursor, "select scrip_code,count(*) as c from ohlcv_bo_equity_1440 group by scrip_code order by c desc" ) pass
def disable_scrip_code(timeframe,exchange,mt,tn): try: r_conn = db.prod_db_conn() c = r_conn.cursor() c.callproc('disable_scrip_code', [ap.mark_inactive_days,timeframe,exchange,mt,tn]) except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) finally: close_db_connection(r_conn)
def create_current_ohlcv_table(d_t_n): table_name = """{tn}_new""".format(tn = d_t_n) drop_table = """DROP TABLE IF EXISTS {table}""".format(table = table_name) command = """ CREATE TABLE if not exists {table} ( id serial PRIMARY KEY, scrip_code character varying(20) NOT NULL, datetime timestamp, Open real, high real, low real, close real, volume real, UNIQUE (scrip_code, datetime) ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; """.format(table = table_name) try: r_conn = db.prod_db_conn() c = r_conn.cursor() c.execute(drop_table) r_conn.commit() c.execute(command) # commit the transaction r_conn.commit() # close the database communication c.close() print(table_name + " : table created successfully") logging.info(table_name + " : table created successfully") except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) finally: close_db_connection(r_conn)
def benchmark_data(exchange, market_type, res, data_len): try: connection = db.prod_db_conn() c = connection.cursor() # Get name of benchmark datas for the exchange sectors c.execute('''select benchmark from benchmark_master where exchange = '{ex}' '''.format(ex=exchange.lower())) benchmark_id = c.fetchall()[0][0] # Data fetch required candles from DB by query df = pd.read_sql_query('''Select * from ( select datetime,open,high,low,close,volume from ohlcv_index_{mt}_{r} where scrip_code = '{bid}' ORDER BY datetime DESC limit {c} ) as c_d order by datetime ASC '''.format(bid=benchmark_id, mt=market_type, r=str(res), c=data_len), con=connection) # Create a Data Feed data = btfeeds.PandasData(dataname=df, timeframe=bt.TimeFrame.Days, compression=1, datetime=0, high=2, low=3, open=1, close=4, volume=5, openinterest=-1) benchmark_name = benchmark_id.split('.')[1] return data, benchmark_name except Exception as e: logging.exception(str(e)) finally: dao.close_db_connection(connection)
def benchmark_name(exchange): try: connection = db.prod_db_conn() c = connection.cursor() c.execute('''select benchmark from benchmark_master where exchange = '{ex}' '''.format(ex=exchange.lower())) benchmark_id = c.fetchall()[0][0] benchmark_name = benchmark_id.split('.')[1] return benchmark_name except Exception as e: logging.exception(str(e)) finally: dao.close_db_connection(connection)
def create_ohlcv_table(exchange,market_type,timeframe): try: table_name = """ohlcv_{ex}_{mt}_{tf}""".format(ex=exchange,mt=market_type,tf=timeframe) command = """ CREATE TABLE if not exists {table} ( id serial PRIMARY KEY, scrip_code character varying(20) NOT NULL, datetime timestamp, Open real, high real, low real, close real, volume real, UNIQUE (scrip_code, datetime) ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; """.format(table = table_name) r_conn = db.prod_db_conn() c = r_conn.cursor() c.execute(command) # commit the transaction r_conn.commit() # close the database communication c.close() print(table_name + " : table for Exchange", exchange,"created successfully") logging.info(table_name + " : table for Exchange", exchange,"created successfully") except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) finally: close_db_connection(r_conn)
def resample_and_insert_data(from_table,to_table,resample_tf): try: r_conn = db.prod_db_conn() c = r_conn.cursor() if check_table_exists(from_table) and resample_tf is not None and resample_tf==10080: print('Resampling Daily to Weekly') c.callproc('resample_daily_to_weekly', [from_table,to_table]) elif check_table_exists(from_table) and resample_tf is not None and resample_tf==43200: print('Resampling Daily to Monthly') c.callproc('resample_daily_to_monthly', [from_table,to_table]) else: print(from_table +": table does not exists for resampling") logging.info(from_table +": table does not exists for resampling") except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) finally: close_db_connection(r_conn)
def get_scrip_id_name(): try: connection = db.prod_db_conn() c = connection.cursor() scrip_id_name = ''' select scrip_code, company_name from scrip_master ''' c.execute(scrip_id_name) scrip_id_name_list = c.fetchall() # close the database communication c.close() except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) finally: close_db_connection(connection) return scrip_id_name_list
def data_feed(scrip_code, ex, m_t, t_f, cerebro, min_data): try: connection = db.prod_db_conn() c = connection.cursor() print( f'Fetching data for {scrip_code} scrip(s) from exhange {ex}, timeframe {t_f} & market {m_t}' ) table_name = su.get_table_name(t_f, ex, m_t) if scrip_code == 'all': ## For Ticker list Handling try: p_s_f_amt = ap.penny_stock_filter[ex] except KeyError as e: print('Exchange', e, 'Does not exsist in Application Properties') print( 'Ensure Penny Stock filter Exchange amount pair in Application Properties' ) # Data fetch required candles from DB by query c.execute( '''with scrips_with_close_filter AS (select scdtc.scrip_code from (select distinct on (scrip_code) scrip_code,datetime,close FROM {t_n} order by scrip_code, datetime DESC) as scdtc inner join scrip_master sm on scdtc.scrip_code = sm.scrip_code where scdtc.close > {sf} and sm.isactive in ('t' , 'y')) SELECT scrip_code, array_agg(Array[datetime::text,open::text,high::text,low::text,close::text,volume::text]) FROM (SELECT n.datetime,n.open,n.high,n.low,n.close,n.volume,n.scrip_code,RANK () OVER (PARTITION BY n.scrip_code ORDER BY n.scrip_code,datetime DESC) sc_rank FROM {t_n} as n inner join scrips_with_close_filter cf on n.scrip_code = cf.scrip_code ORDER BY n.scrip_code, n.datetime ASC) as ol where ol.sc_rank <= {candles} GROUP BY ol.scrip_code;'''.format(t_n=table_name, sf=p_s_f_amt, candles=min_data)) scrip_data_list = c.fetchall() for scrip in scrip_data_list: try: scrip_name = scrip[0] print(scrip_name) df = pd.DataFrame.from_records(scrip[1], columns=[ 'datetime', 'open', 'high', 'low', 'close', 'volume' ]) df2 = df.astype({ 'datetime': 'datetime64[ns]', 'open': 'float64', 'high': 'float64', 'low': 'float64', 'close': 'float64', 'volume': 'float64' }) # print(df2) if len(df2) < min_data: print('Minimum candles not present, skipping', scrip_name) continue # Create a Data Feed data = btfeeds.PandasData(dataname=df2, timeframe=bt.TimeFrame.Days, compression=1, datetime=0, high=2, low=3, open=1, close=4, volume=5, openinterest=-1) # cerebro.resampledata(data, timeframe=bt.TimeFrame.Weeks, # compression=1, name=scrip_name) cerebro.adddata(data, name=scrip_name) except Exception as e: # err.error_log(str(e),data_feed.__name__,'bt_run') logging.exception(str(e)) elif isinstance(scrip_code, tuple): for scrip in scrip_code: try: df = pd.read_sql_query('''Select * from ( SELECT datetime,open,high,low,close,volume FROM "{t_n}" WHERE scrip_code = '{sc}' ORDER BY datetime DESC limit {c} ) as c_d order by datetime ASC '''.format(t_n=table_name, sc=scrip, c=min_data), con=connection) if len(df) < min_data: print('Minimum candles not present') continue # Create a Data Feed data = btfeeds.PandasData(dataname=df, timeframe=bt.TimeFrame.Days, compression=1, datetime=0, high=2, low=3, open=1, close=4, volume=5, openinterest=-1) cerebro.adddata(data, name=scrip) except Exception as e: # err.error_log(str(e),data_feed.__name__,'bt_run') logging.exception(str(e)) else: ## For Single Ticker Handling df = pd.read_sql_query(''' Select * from ( SELECT datetime,open,high,low,close,volume FROM "{t_n}" WHERE scrip_code = '{sc}' ORDER BY datetime DESC limit {c} ) as c_d order by datetime ASC '''.format(t_n=table_name, sc=scrip_code, c=min_data), con=connection) # print(df) if len(df) < min_data: print('Minimum candles not present') return # Create a Data Feed data = btfeeds.PandasData(dataname=df, timeframe=bt.TimeFrame.Days, compression=1, datetime=0, high=2, low=3, open=1, close=4, volume=5, openinterest=-1) cerebro.adddata(data, name=scrip_code) ## Add Benchmark Data to Cerebro b_data, benchmark_name = benchmark_data(ex, m_t, t_f, min_data) cerebro.adddata(b_data, name=benchmark_name) print('Benchmark Added') return cerebro except Exception as e: logging.exception(str(e)) finally: dao.close_db_connection(connection)
def ohlcv_csv_to_db(exchange, market_type, timeframe, path): scrip_list = dao.get_scrip_list(exchange, market_type, timeframe) if len(scrip_list) > 0: if cu.isDirectoryAndPathExists(path): if cu.isDirectoryEmpty(path): print(path + ":: Directory is empty") logging.info(path + ":: Directory is empty") else: fromDate = ap.fromDate r_conn = db.prod_db_conn() c = r_conn.cursor() scrip_code_id_list = [] empty_file_scrip_code_id = [] error_text = "" dynamic_table_name = cu.get_table_name(timeframe, exchange, market_type) temp_ohlcv_query = dao.create_temp_ohlcv_table() c.execute(temp_ohlcv_query) # Temp table session start logging.info("ohlcv_temp table created successfully..") print("ohlcv_temp table created successfully..") dao.create_current_ohlcv_table(dynamic_table_name) table_name = '{d_t_n}_new'.format(d_t_n=dynamic_table_name) insert_ohlcv_query = """insert into {t_n}(scrip_code,datetime,open,high,low,close,volume) select scrip_code,datetime::timestamp,open,high,low,close,volume from ohlcv_temp where datetime::timestamp >= '{frm_date}'; """.format(t_n=table_name, frm_date=fromDate) # print(insert_ohlcv_query) logging.info( "Copying CSV data to ohlcv_temp table is in Progress..") print("Copying CSV data to ohlcv_temp table is in Progress..") for scrip_data in scrip_list: scrip_code_id = scrip_data[4] # scrip_code = scrip_data[0] # company_name = scrip_data[1] file_name = scrip_data[2] # scrip_csv_name = scrip_code + '#' + company_name +'.csv' csv_complete_pth = path + file_name try: if cu.isFileExists(csv_complete_pth): if not cu.isEmpty(csv_complete_pth): with open(csv_complete_pth, 'r') as f: next(f) # Skip the header row. c.copy_from(f, 'ohlcv_temp', sep=',', columns=[ 'scrip_code', 'interval', 'datetime', 'open', 'high', 'low', 'close', 'volume' ]) else: empty_file_scrip_code_id.append(scrip_code_id) print('File is Empty', file_name) logging.info('File is Empty' + str(file_name)) continue else: scrip_code_id_list.append(scrip_code_id) error_text = "File Not Found" print('File not found for scrip', file_name) logging.info('File not found for scrip' + str(file_name)) continue except psycopg2.IntegrityError as e: stack_trace = traceback.format_exc() print('Possible Duplicate Entries', file_name) logging.exception(str(stack_trace)) print(str(e) + stack_trace) continue except psycopg2.DataError as e: scrip_code_id_list.append(scrip_code_id) error_text = "DataError" print('Possible DataError', file_name) logging.exception(str(e)) print(e) continue except psycopg2.Error as e: stack_trace = traceback.format_exc() print('General Error in', file_name) logging.exception(str(stack_trace)) print(str(e) + stack_trace) continue except Exception as e: stack_trace = traceback.format_exc() print(str(e) + stack_trace) print('General Error in111', file_name) logging.exception(str(stack_trace)) continue try: logging.info( "Started transfering data from ohlcv_temp table to main table..." ) print( "Started transfering data from ohlcv_temp table to main table..." ) c.execute(insert_ohlcv_query) r_conn.commit() print( "Records inserted successfully from temp_table to ::" + table_name) logging.info( "Records inserted successfully from temp_table to ::" + table_name) old_ohlv_table_name = """{d_t_n}_old""".format( d_t_n=dynamic_table_name) dao.drop_table(old_ohlv_table_name) from_table_name = dynamic_table_name to_table_name = old_ohlv_table_name dao.rename_table(from_table_name, to_table_name) from_table_name = table_name to_table_name = dynamic_table_name dao.rename_table(from_table_name, to_table_name) if len(scrip_code_id_list) > 0: dao.save_data_error(scrip_code_id_list, error_text, exchange, ohlcv_csv_to_db.__name__) dao.update_scrip_master(scrip_code_id_list) if len(empty_file_scrip_code_id) > 0: ## update the scrip_code status to 'e' if any file is empty dao.update_empty_file_status(empty_file_scrip_code_id) ### update isactive='n' in scrip_code table if we did not ### receive data for any scrip_code in last 7 Days ### update isactive='t' in scrip_code table if we did not ### receive data for any scrip_code within 7 days (temporarily in active) dao.disable_scrip_code(timeframe, exchange, market_type, dynamic_table_name) print("scrip_code disabled successsfully..") except Exception: stack_trace = traceback.format_exc() print(stack_trace) logging.exception(str(stack_trace)) ## finally block Alwasy execute whether exception occured or not ## to close db connection finally: c.close() r_conn.close() # This closes temp_table session else: print(path + " :: Directory don't exists") logging.info(path + " :: Directory don't exists") else: print("All scrip code is in active for exchange :" + exchange + "," + timeframe) logging.info("All scrip code is in active for exchange :" + str(exchange) + "," + str(timeframe))