def add_to_archive(self, url, video_item):
#created file from youtube-dl, lets say
f = open(
"archives/" + self._archive_name + "/" +
video_item.items['title'] + ".mp4", 'w')
f.close()
# insert it into the table as well
# check by title to see if this video already exists, dont add it if nessecary.
sql = "INSERT INTO Videos VALUES " + helpers.create_sql_list(
video_item.items)
print(sql)
self._db.execute_sql(sql)
def get_tasks(orders, arch_db):
"""Extracts list of EMI tasks to be used in merging with special parameters"""
orders = create_sql_list("and", "mancode", orders)
sql_prd = f"""select mancode, manindex, taskid, batchid, elementid,
pfccode, title from elan2406prd.e2s_pfc_task_man
where status <> 6
{orders}"""
df_prd = db.xfp_run_sql(sql_prd)
if arch_db:
sql_arch = f"""select mancode, manindex, taskid, batchid, elementid,
pfccode, title from arch2406prd.e2s_pfc_task_man
where status <> 6
{orders}"""
df_arch = db.xfp_run_sql(sql_arch)
df_tasks = pd.concat([df_prd, df_arch],
ignore_index=True,
sort=False) \
.drop_duplicates().reset_index(drop=True)
else:
df_tasks = df_prd.drop_duplicates().reset_index(drop=True)
return df_tasks
def get_parameters(redo, time=None, params=None, orders=None):
"""Get parameters from XFP"""
# if there is df there realy should be no orders or params
sql_text = ""
# if df is not None:
# sql_text = create_sql_snippet("and", ["mancode", "batchid", "parametercode"], df)
# else:
if orders:
orders = create_sql_list("and", "mancode", orders)
sql_text += orders
if params:
params = create_sql_list("and", "parametercode", params)
sql_text += params
if time:
time = f"""and inputdate >= TO_DATE('{time}',
'yyyy-mm-dd hh24:mi:ss')"""
else:
time = ""
def get_string(dbname, sql_text, time):
return f"""select picode as picode, mancode, batchid,
parametercode as parametercode, inputindex,
inputdate, operationnumber, tagnumber, datatype,
numvalue, datevalue,
textvalue as textvalue,
browsingindex
from {dbname}.e2s_pidata_man
where tagnumber <> 0 --filter out output parameters
and forced = 0
{sql_text}
{time}"""
sql_string = get_string("ELAN2406PRD", sql_text, time)
df_prd = db.xfp_run_sql(sql_string)
print(f"Got {df_prd.shape[0]} params frpm XFP prod")
if redo:
print("Getting parameters from the XFP Archive DB")
sql_string = get_string("ARCH2406PRD", sql_text, time)
df_arch = db.xfp_run_sql(sql_string)
print(f"Got {df_arch.shape[0]} params frpm XFP archive")
df_params = pd.concat([df_prd, df_arch],
ignore_index=True,
sort=False) \
.drop_duplicates().reset_index(drop=True)
else:
df_params = df_prd.drop_duplicates().reset_index(drop=True)
# Exit early if df is empty
if df_params.empty:
return df_params
# for some reason there are some strange dates in the database
df_params.drop(df_params.loc[(df_params["DATATYPE"] == 2) & (
df_params["DATEVALUE"].astype(str).str.startswith("0"))].index,
inplace=True,
axis=0)
df_params.drop(df_params.loc[(df_params["DATATYPE"] == 2) & (
df_params["DATEVALUE"].astype(str).str.startswith("28"))].index,
inplace=True,
axis=0)
df_params.drop(df_params.loc[(df_params["DATATYPE"] == 2) & (
df_params["DATEVALUE"].astype(str).str.startswith("1900"))].index,
inplace=True,
axis=0)
# Rounding
df_params["NUMVALUE"] = df_params["NUMVALUE"].round(2)
# check and save an actual value
df_params["VALUE"] = df_params["NUMVALUE"]
df_params.loc[df_params["DATATYPE"] == 0, "VALUE"] = \
df_params["TEXTVALUE"]
df_params.loc[df_params["DATATYPE"] == 2, "VALUE"] = \
df_params["DATEVALUE"].dt.strftime('%d-%m-%Y %H:%M:%S')
# drop Null values
df_params.drop(df_params.loc[df_params["VALUE"].isnull()].index,
inplace=True,
axis=0)
return trim_all_columns(df_params)