def single_pg_send(df, tablename):
"""
uses: new_tablename, tablecheck, ingesterv2.main_ingest
table_create, datetime_type_assert
"""
d = db('dima')
print("STARTING INGEST")
df = blank_fixer(df)
df = significant_digits_fix_pandas(df)
df = datetime_type_assert(df)
# df = openingsize_fixer(df) if "openingSize" in df.columns else df
if 'ItemType' in df.columns:
newtablename = new_tablename(df)
if tablecheck(newtablename):
print(f'network table "{newtablename}" exists, ingesting..')
ingesterv2.main_ingest(df, newtablename, d.str, 10000)
else:
table_create(df, newtablename, 'dima')
print(f'created network table: {newtablename}')
ingesterv2.main_ingest(df, newtablename, d.str, 10000)
else:
print("not a network table")
newtablename = tablename
if tablecheck(tablename):
print("FOUND THE TABLE IN PG")
ingesterv2.main_ingest(df, newtablename, d.str, 10000)
else:
print("DID NOT FIND TABLE IN PG, CREATING...")
table_create(df, tablename, 'dima')
ingesterv2.main_ingest(df, newtablename, d.str, 10000)
def modelrun_key_check(modelrunkey):
d = db("aero")
if tablecheck("ModelRuns", "aero"):
try:
con = d.str
cur = con.cursor()
exists_query = '''
select exists (
select 1
from "ModelRuns"
where "ModelRunKey" = %s
)'''
cur.execute(exists_query, (modelrunkey, ))
return cur.fetchone()[0]
except Exception as e:
print(e, "error selecting modelruns table.")
con = d.str
cur = con.cursor()
else:
print("ModelRun table does not exist.")
def model_run_updater(batchpath, modelrunkey, source=None):
"""
1. creates a table in postgres with supplied dataframe
2. appends data to postgres table
"""
d = db("aero")
df = txt_read(batchpath)
if source != None:
df['Source'] = source
else:
pass
df['ModelRunKey'] = modelrunkey
if tablecheck('aero_runs'):
print('aero_runs exists, skipping table creation')
update_model(batchpath, modelrunkey)
ingesterv2.main_ingest(df, "aero_runs", d.str, 100000)
else:
print('creating aero_runs table..')
table_create(df, "aero_runs", "aero")
update_model(batchpath, modelrunkey)
ingesterv2.main_ingest(df, "aero_runs", d.str, 100000)
def update_model(path_in_batch, modelrunkey):
""" ingests a project metadata file if project key does not exist.
- would be better if it would automatically pull projectkey from
the excel file is handling to check
- projectkey is made up of what?
- creates datafrmae from excel metadata table
- adds projectkey field
- ingests/updates project table in pg
1. check if table exists, if not create it
2. check if project key exists, if not update it
"""
tempdf = template()
# check if table exists
if tablecheck("ModelRuns", "aero"):
if modelrun_key_check(modelrunkey):
print(
f"modelrunkey exists, aborting 'ModelRuns' update with ModelRunKey = {modelrunkey}."
)
print("continuing without update..")
pass
else:
update = read_template(path_in_batch, tempdf)
# update['ModelRunKey'] = modelrunkey
send_model(update)
# if no, create table and update pg
else:
table_create(tempdf, "ModelRuns", "aero")
add_modelrunkey_to_pg()
update = read_template(path_in_batch, tempdf)
# tempdf = read_template(path_in_batch,tempdf)
# update['ModelRunKey'] = modelrunkey
send_model(update)
def batch_looper(dimacontainer, projkey=None, dev=False, pg=False):
"""
addition
creates an exhaustive list of tables across all dimas in a folder
and then uses looper to gothrough the list of tables and create csv's for
each.
"""
if dev == False:
d = db('dima')
keyword = "dima"
else:
d = db("dimadev")
keyword = "dimadev"
tablelist = None
while tablelist is None:
print('gathering tables within dimas..')
tablelist = table_collector(dimacontainer)
print(tablelist, "tablelist check # 1")
else:
print('creating csvs for each table..')
for table in tablelist:
if pg != True:
looper(dimacontainer, table, csv=True)
else:
df = looper(dimacontainer, table,
csv=False) if 'tblPlots' not in table else looper(
dimacontainer, table, projkey, csv=False)
print(df.shape, "looper dataframe check # 2")
if 'ItemType' in df.columns:
# if one of the non-vegetation bsne tables, use 'new_tablename' ,
# function to produce a new tablename: 'tblHorizontalFlux' or
# 'tblDustDeposition'
newtablename = new_tablename(df)
if tablecheck(newtablename, keyword):
print('MWACK')
df = dateloadedcheck(df)
df = ovenTemp_INT(df)
ingesterv2.main_ingest(df, newtablename, d.str, 10000)
else:
table_create(df, newtablename, keyword)
print('DDT ')
df = dateloadedcheck(df)
df = ovenTemp_INT(df)
ingesterv2.main_ingest(df, newtablename, d.str, 10000)
else:
print("NOT A HORFLUX TABLE")
newtablename = table
if tablecheck(table, keyword):
print(f"FOUND THE {newtablename} IN PG")
df = dateloadedcheck(df)
ingesterv2.main_ingest(df, newtablename, d.str, 10000)
else:
print(
f"DID NOT FIND {newtablename} IN PG, CREATING...")
table_create(df, table, keyword)
df = dateloadedcheck(df)
ingesterv2.main_ingest(df, newtablename, d.str, 10000)
def pg_send(table: str, path: str, csv=None, debug=None):
plot = None
""" Sends dataframe to postgres or prints out CSV.
Given a Dima path and tablename, uses the function main_translate to create
a dataframe and either send it to a postgres database, or print it out to
a CSV in the same directory where the DIMA file is in.
Parameters
----------
table : str
Name of the table in DIMA. example: 'tblLines'
path : str
Physical path to DIMA Access file. example: 'c://Folder/dima_file.mdb'
csv : None or any character. (default value: None)
If not None, it will print out the table dataframe to CSV.
debug : None or any character. (default value: None)
If not None, it will print out each of the steps the function's processes
take.
"""
d = db('dima')
df = main_translate(table, path)
print("STARTING INGEST")
df['DateLoadedInDB'] = datetime.now().strftime("%d-%m-%Y %H:%M:%S")
# dbkey add calibration HERE
if ('calibration' in path) or ('Calibration' in path):
df['DBKey'] = os.path.join(
split(splitext(path)[0])[1].replace(" ", ""), 'calibration')
else:
# creates non-calibration DBKey here
df['DBKey'] = split(splitext(path)[0])[1].replace(" ", "")
if 'ItemType' in df.columns:
# if one of the non-vegetation bsne tables, use 'new_tablename' ,
# function to produce a new tablename: 'tblHorizontalFlux' or
# 'tblDustDeposition'
newtablename = new_tablename(df)
if tablecheck(newtablename):
print('MWACK')
ingesterv2.main_ingest(df, newtablename, d.str,
10000) if csv else csv_fieldcheck(
df, path, table)
else:
table_create(df, newtablename, 'dima')
print('llegue a 2')
ingesterv2.main_ingest(df, newtablename, d.str,
10000) if csv else csv_fieldcheck(
df, path, table)
else:
print("NOT A HORFLUX TABLE")
newtablename = table
if tablecheck(table):
print("FOUND THE TABLE IN PG")
# ingesterv2.main_ingest(df, newtablename, d.str, 10000) if csv else csv_fieldcheck(df,path,table)
else:
print("DID NOT FIND TABLE IN PG, CREATING...")
table_create(df, table, 'dima')