def tabledatacount(): config = conf_reader.get_config() inputdbprop = config.get("inputdbprop", None) inputstockdatapath = config.get("inputstockdatapath") try: cur, cnx = db_connet(inputdbprop.get('dbname'), inputdbprop.get('dbusername'), inputdbprop.get('dbpassword'), inputdbprop.get('hostname')) except: cur, cnx = sql_connet(inputdbprop.get('dbusername'), inputdbprop.get('dbpassword'), inputdbprop.get('hostname')) files = os.listdir(inputstockdatapath) tablecount = dict() for i in files: tablename = i.split(".")[0] try: query = "select count(1) as count from(select *from %s limit 1) data" % ( tablename) cur.execute(query) recordcount = cur.fetchall() recordcount = recordcount[0] recordcount = recordcount[0] except: recordcount = 0 tablecount[tablename] = recordcount return tablecount
def loadtodb(): config = conf_reader.get_config() inputdbprop = config.get("inputdbprop", None) inputstockdatapath = config.get("inputstockdatapath") dbname = inputdbprop.get('dbname') checklist = check_list_sql.tabledatacount() files = os.listdir(inputstockdatapath) for i in files: tablename = i.split(".")[0] recordcount = checklist.get(tablename) if recordcount == 1: print('data already there in database...') continue if recordcount == 0: cur, cnx = sql_connet(inputdbprop.get('dbusername'), inputdbprop.get('dbpassword'), inputdbprop.get('hostname')) files = os.listdir(inputstockdatapath) for i in files: with open(inputstockdatapath + str(i), "rt", encoding='utf-8') as f: count = 0 for line in f: l = line.strip() query = l.split(';') query = query[0] print(str(query) + 'load to sql execution started...') if count <= 1: query = query.replace("?", dbname) if count >= 2: query = query try: cur.execute(query) except: pass
def loaddatatosql(): todays_date = datetime.datetime.today() dateformat = todays_date.strftime("%d%m%Y") config = conf_reader.get_config() inputdbprop = config.get("inputdbprop", None) inputstockdatapath = config.get("inputstockdatapath") sqltablename = config.get("sqltablename") latestdatafromnsepath = config.get("latestdatafromnsepath") cur, cnx = sql_connet(inputdbprop.get('dbname'), inputdbprop.get('dbusername'), inputdbprop.get('dbpassword'), inputdbprop.get('hostname')) table_name = sqltablename input_path = str(latestdatafromnsepath) + "_latest" + str( dateformat) + ".csv" filtered_set = pd.read_csv(input_path) lst = [] for i in filtered_set.values: lst.append(tuple(i)) valstrg = str(lst)[1:-1] columns = ', '.join("`" + key + "`" for key in filtered_set.keys()) sql = "INSERT INTO %s ( %s ) VALUES %s" % (table_name, columns, valstrg) try: cur.execute(sql) except: pass
def package_installation(logger): try: reqs = subprocess.check_output([sys.executable, '-m', 'pip', 'freeze']) except: try: reqs = subprocess.check_output( [sys.executable, '-m', 'pip3', 'freeze']) except: print( "Please ensure that pip or pip3 is installed on your laptop and redo the setup" ) installed_packages = [r.decode().split('==')[0] for r in reqs.split()] config = conf_reader.get_config() requirements = config.get("requirements", None) print('packages execution started...') packages = [] try: with open(requirements, "rt") as f: for line in f: l = line.strip() package = l.split(',') package = package[0] packages.append(package) for i in packages: if i not in installed_packages: working_set = WorkingSet() try: dep = working_set.require('paramiko>=1.0') except DistributionNotFound: pass whoami = os.getlogin() if whoami == 'root': install([i]) if whoami != 'root': try: subprocess.check_call(["pip", "install", "--user", i]) except: try: subprocess.check_call( ["pip3", "install", "--user", i]) except: print( "Check whether this user has admin privileges for installing package" ) except Exception as e: logger.exception( 'ERROR:: Some issue in reading the Config...check config_reader.py script in bin Folder....' ) raise e
def main(): config = conf_reader.get_config() inputdbprop = config.get("inputdbprop") # olduser = input ("Enter sql old user name :") # oldpass = input ("Enter sql old user password :"******"Enter sql hostname :") olduser = config.get("sql_old_username") oldpass = config.get("sql_old_userpassword") host = inputdbprop.get("hostname") mkuser = inputdbprop.get("dbusername") mkpass = inputdbprop.get("dbpassword") cur, cnx = user_connect(olduser, oldpass, host) results = user_creation(mkuser, mkpass, host, cur, cnx)
#Libraries import warnings warnings.filterwarnings("ignore") import numpy as np import pandas as pd import datetime import pymysql import conf_reader print('get average execution started...') def db_connet(database,user,password,host): cnx = pymysql.connect(user=user, password=password,host=host,database=database,charset='utf8', autocommit=True) cur=cnx.cursor() return cur,cnx config = conf_reader.get_config() inputdbprop = config.get("inputdbprop", None) #### Connect to database cur,cnx = db_connet(inputdbprop.get('dbname'),inputdbprop.get('dbusername'),inputdbprop.get('dbpassword'),inputdbprop.get('hostname')) databasename=inputdbprop.get('dbname') usedb="USE %s "% (databasename) cur.execute(usedb) #### Import required data from database fetchsql="select SYMBOL, SERIES, TIMESTAMP, CLOSE, PREVCLOSE, TOTTRDQTY, TOTTRDVAL from nsedailybhavhist where SERIES='EQ' and date(timestamp)= (select date(max(timestamp)) from nsedailybhavhist) order by symbol, timestamp desc;" cur.execute(fetchsql) nsedailybhavhistrecords = cur.fetchall() count=0 ptime=datetime.datetime.today() cur.execute("select date(max(timestamp)) from nsedailybhavhist")
def get_latest_data(): config = conf_reader.get_config() inputdbprop = config.get("inputdbprop", None) sqltablename = config.get("sqltablename") latestdatafromnsepath = config.get("latestdatafromnsepath") olddatafromnsepath = config.get("olddatafromnsepath") cur, cnx = sql_connet(inputdbprop.get('dbname'), inputdbprop.get('dbusername'), inputdbprop.get('dbpassword'), inputdbprop.get('hostname')) table_name = sqltablename lastupdated = "select max(exdate) from nsecorporateaction;" cur.execute(lastupdated) lastupdated = cur.fetchall() lastupdated = lastupdated[0] lastupdated = lastupdated[0] todays_date = dt.datetime.today() dateformat = todays_date.strftime("%d%m%Y") downday = todays_date.strftime("%Y-%m-%d %H:%M:%S") downday = dt.datetime.strptime( downday, '%Y-%m-%d %H:%M:%S').strftime("%Y-%m-%d %H:%M:%S") input_path = str(latestdatafromnsepath) + ".csv" dataset = pd.read_csv(input_path) dataset.columns = [ 'SYMBOL', 'COMPANY', 'INDUSTRY', 'SERIES', 'FACEVALUE', 'PURPOSE', 'EXDATE', 'RECORDDATE', 'BCSTARTDATE', 'BCENDDATE', 'NDSTARTDATE', 'NDENDDATE' ] dataset['DOWNDAY'] = downday dataset['EXDATE'] = pd.to_datetime(dataset['EXDATE']) filtered_set = dataset[(dataset['EXDATE'] > lastupdated)] try: filtered_set['RECORDDATE'] = dt.datetime.strptime( filtered_set['RECORDDATE'], '%d-%b-%Y').strftime("%Y-%m-%d") except: filtered_set['RECORDDATE'] = '0000-00-00' try: filtered_set['BCSTARTDATE'] = dt.datetime.strptime( filtered_set['BCSTARTDATE'], '%d-%b-%Y').strftime("%Y-%m-%d") except: filtered_set['BCSTARTDATE'] = '0000-00-00' try: filtered_set['BCENDDATE'] = dt.datetime.strptime( filtered_set['BCENDDATE'], '%d-%b-%Y').strftime("%Y-%m-%d") except: filtered_set['BCENDDATE'] = '0000-00-00' try: filtered_set['NDSTARTDATE'] = dt.datetime.strptime( filtered_set['NDSTARTDATE'], '%d-%b-%Y').strftime("%Y-%m-%d") except: filtered_set['NDSTARTDATE'] = '0000-00-00' try: filtered_set['NDENDDATE'] = dt.datetime.strptime( filtered_set['NDENDDATE'], '%d-%b-%Y').strftime("%Y-%m-%d") except: filtered_set['NDENDDATE'] = '0000-00-00' try: ye_date = datetime.strftime(datetime.now() - timedelta(1), '%d%m%Y') files = [ str(latestdatafromnsepath) + '_latest' + str(ye_date) + '.csv' ] for f in files: shutil.move( f, str(olddatafromnsepath) + '_latest' + str(ye_date) + '.csv') except: pass filename = str(latestdatafromnsepath) + "_latest" + str( dateformat) + ".csv" filtered_set.to_csv(filename, index=False)