**************************************************************************
qry = f"""
SELECT {DBNAME}.[Link] FROM {DBNAME}.dashboard_predictions as prediction
join {DBNAME}.dashboard_stores as store
on prediction.store_id = [Link]
where
DAYNAME([Link]) = 'Saturday'
AND [Link] >= '2021-01-16'
AND prediction.prediction_value != 0
AND store.client_id = 1;
"""
[Link](qry)
all_ids = ",".join([str(id[0]) for id in [Link]()])
******************************************************************************
file_name = "This is a file"
dt = [Link]()
date = [Link]("%Y-%m-%d")
print(date)
print(file_name)
qry = """INSERT INTO prediction_record_file(file_name, file_received_date)
VALUES('%s', '%s')""" %(file_name, date)
print(qry)
[Link](qry)
[Link]()
print("Data inserted in table")
*********************************************************************************
import pandas as pd
data = {
"calories": [420, 380, 390],
"duration": [50, 40, 45]
}
#load data into a DataFrame object:
df = [Link](data)
if ~[Link]:
print(df)
print("hello")
********************************************************************************
yesterday = [Link]() - [Link](days=1)
date = [Link]("%Y-%m-%d") # date = "2021-10-27"
file_name = f'greenyard/qa/output/{date}.csv' #previous-day date file
print(file_name)
**************************************************************************
predi_file = f"predictions_{date}.csv"
qry = f""" SELECT -----------------------------------
"""
[Link](qry)
data = [Link]([Link](), columns=['Forecasted date', 'Article category', 'Article
family', 'Article subfamily', 'Article name', 'Store id', 'Store name', 'Client', 'Unit', 'product id',
'Prediction', 'Prediction made on'])
s3 = [Link]('s3',
aws_access_key_id = ACCESS_ID,
aws_secret_access_key= ACCESS_KEY)
try:
filePath = [Link](BUCKET_NAME,
f"{client_folder_name}/{ENVIRONMENT}/predictions/{predi_file}")
csv_buffer = StringIO()
data.to_csv(csv_buffer, index=False)
[Link](Body=csv_buffer.getvalue())
except [Link] as e:
print(f"ERROR in prediction file: {e}")
*******************************************************************************************
df = pd.read_csv(file_name,
names=["IPER","REP","SR","FAM","ART","ETI","DES_ART","DES_ETI","DATA","T_MOV","
Q","PV"],
skiprows=1, sep=';', encoding= 'unicode_escape')
try:
[Link](f"""TRUNCATE {DBNAME}.staging_sales_table""")
print("\tTable staging_sales_table is truncated")
sqlEngine = create_engine(
"mysql+pymysql://{user}:{pw}@foresightee-dashboard-qa-instance-
[Link]/{db}"
.format(user=USR, pw=PASS, db=DBNAME)
)
df.to_sql('staging_sales_table', con=sqlEngine, if_exists='append', chunksize=10000,
index=False)
print("\tData was inserted into staging_sales_table")
except Exception as e:
print("Exit: Database connection failed due to {}".format(e))
return
**********************************************************************************************
def main():
file_names = ['VENDITE_2016.CSV', 'VENDITE_2017.CSV', 'VENDITE_2018.CSV',
'VENDITE_2019.CSV', 'VENDITE_2020.CSV']
for file in file_names:
print("\n* * * Start processing file : %s * * * " %file)
read_promo_file(file)
print("\tEND: (data uploading for file [%s] in done : ***)\n" %file)
if __name__ == '__main__':
if str(input("\nDo you want to proceed to databaes: [%s]?\n \t\ty/n: " %DBNAME)).lower()
== 'y':
main()
else:
print("Exit with NO operation.")
*******************************************************************
file_path = 'greenyard/qa/output/[Link]'
date = file_path.split("/")[-1].split(".csv")[0]
print("Prediction file date: ", date)
*********************************************************************************************************
#**********To upload all same type excel files of a folder in database single table***********
def read_mail_file():
sqlEngine = create_engine(
"mysql+pymysql://{user}:{pw}@foresightee-dashboard-qa-instance-
[Link]/{db}"
.format(user=USR, pw=PASS, db=DBNAME)
)
files = [f for f in listdir('C:/hariom/mail_file') if isfile(join('C:/hariom/mail_file', f))]
for file in files:
file = 'C:/hariom/mail_file' + '/' + file
print("file :", file)
df = pd.read_excel(file, sheet_name = 0,
names=["DeliveryDate","ClientId","NameLongClient","ArticleId","NameLongArticle","UomSal
es","QtyColli","QtyPieces","QtyWeight","QtyDelivered"],
skiprows=1 )
try:
df.to_sql('staging_16_12_21', con=sqlEngine, if_exists='append', chunksize=10000,
index=False)
print("\tData was inserted ")
except Exception as e:
print("Exit: Database connection failed due to {}".format(e))
return
print("Done ")
************************************************************************************
db_data = [Link]()
print(db_data)
for row in db_data:
data = str(row).strip(".0,()")
print("row[0]:", data)
******************************************************************************
To append a dictionary in lit
with open(self.recepient_file) as f:
lines = [Link]()
[Link]()
records = []
for line in lines:
record = {'name': [Link](",")[0].strip(), 'email': [Link](",")[1].strip()}
[Link](record)
print(“Records :- , records)
Or
with open(self.recepient_file) as f:
lines = [Link]()
records = [{'name': [Link](",")[0].strip(), 'email': [Link](",")
[1].strip()}for line in lines]
[Link]()
—-----------------------------------------------------------------------------------------------
To create a .exe file of python code
Link:- [Link]
1) Run pip install pyinstaller
2) Go to python code directory and open power shell window by shift+right click mouse
3) Run pyinstaller --onefile -w '[Link]'
4) Go in dist folder and find your “[Link]” file
—-------------------------------------------------------------------------------------------------
To read .csv file from S3 bucket without to data frame or pandas
import os
import glob
import boto3
import pandas as pd
from io import BytesIO
s3_client = [Link]('s3')
bucket_name = "dnb-info"
s3_file_path = "dev/batch-algo/requests/[Link]"
resp = s3_client.get_object(Bucket=bucket_name, Key=s3_file_path)
with open('[Link]', 'wb') as f:
[Link](BytesIO(resp['Body'].read()).getbuffer())
print("done")
pd.read_csv('[Link]')
***************************************************************************
try this first..(To create new branch and delete old one main branch then create new on and
pull
git reset .
git checkout .
If not working then
1)
git checkout -b tmp
or
git checkout tmp
2)
git branch -D master
3) git fetch
4) git checkout master
5)
git pull origin master