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)
