def __insert_enum_table(self, table_name, form_data, description_id):
"""
Inserts a row for spl cases where the label is read from enum
and a text box is provided for the value. Like Gas field contaminants.
"""
type_name = form_data.get("Type_Name")
label = table_name.replace(type_name + "_", "")
select = Select(self.db)
column_names = select.read_column_names(table_name)
#enum_result = select.read_column_names(table_name, where=label)
enum_value = column_names[1][1].replace("enum(", "")
enum_value = enum_value[:-1]
enum = []
replace_comma = False
for val in enum_value:
if val == "'":
replace_comma = True if not replace_comma else False
if replace_comma and val == ',':
val = "@"
enum.append(val)
enum = "".join(enum)
sql_values = {}
sql_fields = []
sql_statement = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
sql_fields.append("`Description_ID`=:description_id")
sql_values['description_id'] = description_id
for option in enum.split(","):
option = option.replace("'", "")
option = option.replace("@", ",")
print(option, label+"_"+option)
value = form_data.get(label+"_"+option).strip()
if value:
sql_fields.append("`" + label + "`=%(" + label.lower() + ")s")
sql_values[label.lower()] = option
sql_fields.append("`" + column_names[2][0] + "`=%(" + column_names[2][0].lower() + ")s")
sql_values[column_names[2][0].lower()] = value
sql_statement.append(",".join(sql_fields))
print("".join(sql_statement), sql_values)
session = self.db_conn.cursor()
try:
session.execute("".join(sql_statement), sql_values)
self.db_conn.commit()
except Exception as e:
print(e)
finally:
session.close()
return 1
def __insert_nuclear_performance_data(self, table_name, form_data, description_id):
select = Select(self.db)
column_names = select.read_column_names(table_name)
number_of_units = form_data.get("numberOfNuclear_Unit_Description", 0)
if int(number_of_units) == 0:
return 0
unit_result = select.read("Nuclear_Unit_Description",
columns=["Unit_Description_ID"],
where=[["Description_ID",
"=",
description_id]]
)
unit_ids = unit_result.fetchall()
start_decade = 1950
end_decade = 2020
for r, unit_id in enumerate(unit_ids):
row_num = r + 1
unit_id = unit_id[0]
for year in range(start_decade, end_decade):
sql_values = {}
sql_statement = []
sql_fields = []
alt_sql_statement = []
alt_sql_fields = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
alt_sql_statement.append("INSERT INTO " + table_name + " SET ")
sql_fields.append("`Description_ID`=:description_id")
alt_sql_fields.append("`Description_ID`=%(description_id)s")
sql_values['description_id'] = description_id
sql_fields.append("`Unit_Description_ID`=:unit_description_id")
alt_sql_fields.append("`Unit_Description_ID`=%(unit_description_id)s")
sql_values['unit_description_id'] = unit_id
sql_fields.append("`Year_yr`=:year_yr")
alt_sql_fields.append("`Year_yr`=%(year_yr)s")
sql_values['year_yr'] = year
for k in column_names:
field_name = k[0] + "_" + str(row_num) + "_###_" + str(year)
if not form_data.get(field_name) or form_data.get(field_name) == "None" or form_data.get(field_name) == "":
continue
value = form_data.get(field_name)
if k[0].find("_ID") < 0 and value:
key = k[0]
key = key.replace("(", "")
key = key.replace(")", "")
key = key.replace(":", "")
key = key.replace("%", "")
sql_fields.append("`" + k[0] + "`=:" + key.lower())
alt_sql_fields.append("`" + k[0] + "`='%(" + key.lower() + ")s'")
sql_values[key.lower()] = value.strip()
if len(sql_values) <= 3:
continue
sql_statement.append(",".join(sql_fields))
alt_sql_statement.append(",".join(alt_sql_fields))
session = self.db_conn.cursor()
try:
session.execute("".join(alt_sql_statement), sql_values)
self.db_conn.commit()
except Exception:
try:
# may be there is a spl char in the sql stmt
# using connection().execute will not quote the sql stmt
# and some messy hack is needed to avoid param execution
sql_stmt = " ".join(alt_sql_statement)
sql_stmt = sql_stmt.replace("(%)", "(##)")
sql_stmt = sql_stmt.replace("%_", "##_")
sql_stmt = sql_stmt % sql_values
sql_stmt = sql_stmt.replace("(##)", "(%)")
sql_stmt = sql_stmt.replace("##_", "%_")
session.execute(sql_stmt)
self.db_conn.commit()
except Exception:
#session.rollback()
raise
finally:
session.close()
return 1
def __insert_row_column_module(self,
table_name,
form_data,
description_id):
"""
Handles multiple column modules like unit_description.
"""
select = Select(self.db)
column_names = select.read_column_names(table_name)
number_of_rows = form_data.get("numberOf" + table_name, 0)
if int(number_of_rows) == 0:
return 0
for row_num in range(1, int(number_of_rows)+1):
sql_values = {}
sql_statement = []
sql_fields = []
alt_sql_statement = []
alt_sql_fields = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
alt_sql_statement.append("INSERT INTO " + table_name + " SET ")
sql_fields.append("`Description_ID`=:description_id")
alt_sql_fields.append("`Description_ID`=%(description_id)s")
sql_values['description_id'] = description_id
for k in column_names:
field_name = k[0] + "_###_" + str(row_num)
if not form_data.get(field_name):
continue
value = form_data.get(field_name)
if k[0].find("_ID") < 0 and value:
key = k[0]
key = key.replace("(", "")
key = key.replace(")", "")
key = key.replace(":", "")
key = key.replace("%", "")
sql_fields.append("`" + k[0] + "`=:" + key.lower())
alt_sql_fields.append("`" + k[0] + "`=%(" + key.lower() + ")s")
sql_values[key.lower()] = value.strip()
sql_statement.append(",".join(sql_fields))
alt_sql_statement.append(",".join(alt_sql_fields))
session = self.db_conn.cursor(raw=True)
try:
#print("".join(alt_sql_statement) % sql_values)
session.execute("".join(alt_sql_statement), sql_values)
self.db_conn.commit()
print("SQL: " + session.statement)
except Exception as e:
print("ERROR: " + str(e))
try:
# may be there is a spl char in the sql stmt
# using connection().execute will not quote the sql stmt
# and some messy hack is needed to avoid param execution
sql_stmt = " ".join(alt_sql_statement)
sql_stmt = sql_stmt.replace("(%)", "(##)")
sql_stmt = sql_stmt.replace("%_", "##_")
sql_stmt = sql_stmt % sql_values
sql_stmt = sql_stmt.replace("(##)", "(%)")
sql_stmt = sql_stmt.replace("##_", "%_")
print("ALT SQL:")
print(sql_stmt)
session.execute(sql_stmt)
self.db_conn.commit()
except Exception:
#session.rollback()
raise
finally:
session.close()
return 1
def __insert_performance_module(self,
table_name,
form_data,
description_id):
"""
Handles performance data.
"""
start_decade = 1950
end_decade = 2020
table_name = table_name.replace("_Annual", "")
select = Select(self.db)
column_names = select.read_column_names(table_name)
for year in range(start_decade, end_decade):
sql_values = {}
sql_fields = []
alt_sql_fields = []
sql_statement = []
alt_sql_statement = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
alt_sql_statement.append("INSERT INTO " + table_name + " SET ")
sql_fields.extend(["`Description_ID`=:description_id",
"`Year_yr`=:year_yr"])
alt_sql_fields.extend(["`Description_ID`=%(description_id)s",
"`Year_yr`=%(year_yr)s"])
sql_values['description_id'] = description_id
sql_values['year_yr'] = year
for k in column_names:
field_name = k[0] + "_###_" + str(year)
if not form_data.get(field_name):
continue
value = form_data.get(field_name)
if k[0].find("_ID") < 0 and value:
key = k[0]
key = key.replace("(", "")
key = key.replace(")", "")
key = key.replace(":", "")
key = key.replace("%", "")
sql_fields.append("`" + k[0] + "`=:" + key.lower())
alt_sql_fields.append("`" + k[0] + "`='%(" + key.lower() + ")s'")
sql_values[key.lower()] = value.strip()
if len(sql_values) == 0:
continue
sql_statement.append(",".join(sql_fields))
alt_sql_statement.append(",".join(alt_sql_fields))
session = self.db_conn.cursor()
try:
session.execute("".join(alt_sql_statement), sql_values)
self.db_conn.commit()
except Exception:
try:
# may be there is a spl char in the sql stmt
# using connection().execute will not quote the sql stmt
# and some messy hack is needed to avoid param execution
sql_stmt = " ".join(alt_sql_statement)
sql_stmt = sql_stmt.replace("(%)", "(##)")
sql_stmt = sql_stmt % sql_values
sql_stmt = sql_stmt.replace("(##)", "(%)")
session.execute(sql_stmt)
self.db_conn.commit()
except Exception:
#session.rollback()
raise
finally:
session.close()
return 1
def __insert_generic_module(self, table_name, form_data, description_id, dual=0):
"""
Prepares and saves data for a generic module like _Description,
_Location, etc.
"""
sql_values = {}
sql_fields = []
alt_sql_fields = []
insert_id = 0
alt_sql_statement = []
sql_statement = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
alt_sql_statement.append("INSERT INTO " + table_name + " SET ")
if description_id > 0:
sql_fields.append("`Description_ID`=:description_id")
sql_values['description_id'] = description_id
alt_sql_fields.append("`Description_ID`=%(description_id)s")
select = Select(self.db)
column_names = select.read_column_names(table_name)
for k in column_names:
kk = k[0]
if dual > 0:
kk = kk + "_stn" + str(dual)
if not form_data.get(kk):
continue
value = form_data.get(kk)
if kk.find("_ID") < 0 and value:
key = k[0]
key = key.replace("(", "")
key = key.replace(")", "")
key = key.replace(":", "")
key = key.replace("%", "")
sql_fields.append("`" + k[0] + "`=:" + key.lower())
alt_sql_fields.append("`" + k[0] + "`=%(" + key.lower() + ")s")
value = value.strip().replace("'", "")
value = value.replace('"', "")
sql_values[key.lower()] = value
sql_statement.append(",".join(sql_fields))
alt_sql_statement.append(",".join(alt_sql_fields))
session = self.db_conn.cursor(dictionary=True)
try:
print("".join(alt_sql_statement))
print(sql_values)
session.execute("".join(alt_sql_statement), sql_values)
self.db_conn.commit()
insert_id = session.lastrowid
except Exception:
print(session.statement)
"""
try:
# may be there is a spl char in the sql stmt
# using connection().execute will not quote the sql stmt
# and some messy hack is needed to avoid param execution
sql_stmt = " ".join(alt_sql_statement)
sql_stmt = sql_stmt.replace("(%)", "(##)")
sql_stmt = re.sub(r"(\d+)%", "\g<1>##", sql_stmt)
sql_stmt = sql_stmt % sql_values
sql_stmt = sql_stmt.replace("(##)", "(%)")
sql_stmt = re.sub(r"(\d+)##", "\g<1>%", sql_stmt)
#print(sql_stmt)
result = session.execute(sql_stmt)
self.db_conn.commit()
insert_id = result.lastrowid
except Exception:
#session.rollback()
raise
"""
raise
finally:
session.close()
return insert_id
def insert_dual_node_description(self, table_name, form_data, description_id,
station_1_id, station_2_id, connection_id):
"""
for dual node descriptions
"""
sql_values = {}
sql_fields = []
alt_sql_fields = []
alt_sql_statement = []
sql_statement = []
sql_statement.append("INSERT INTO " + table_name + " SET ")
alt_sql_statement.append("INSERT INTO " + table_name + " SET ")
sql_fields.append("`Description_ID`=:description_id,"
"Station_1_ID=:station_1_id,"
"Station_2_ID=:station_2_id,"
"Connection_ID=:connection_id")
sql_values['description_id'] = description_id
alt_sql_fields.append("`Description_ID`=%(description_id)s")
sql_values['station_1_id'] = station_1_id
alt_sql_fields.append("`Station_1_ID`=%(station_1_id)s")
sql_values['station_2_id'] = station_2_id
alt_sql_fields.append("`Station_2_ID`=%(station_2_id)s")
sql_values['connection_id'] = connection_id
alt_sql_fields.append("`Connection_ID`=%(connection_id)s")
select = Select(self.db)
column_names = select.read_column_names(table_name)
print(form_data)
for k in column_names:
if not form_data.get(k[0]):
continue
value = form_data.get(k[0])
if k[0].find("_ID") < 0 and value:
key = k[0]
key = key.replace("(", "")
key = key.replace(")", "")
key = key.replace(":", "")
key = key.replace("%", "")
sql_fields.append("`" + k[0] + "`=:" + key.lower())
alt_sql_fields.append("`" + k[0] + "`='%(" + key.lower() + ")s'")
sql_values[key.lower()] = value.strip()
sql_statement.append(",".join(sql_fields))
alt_sql_statement.append(",".join(alt_sql_fields))
session = self.db_conn.cursor()
try:
session.execute("".join(alt_sql_statement), sql_values)
self.db_conn.commit()
except Exception:
try:
# may be there is a spl char in the sql stmt
# using connection().execute will not quote the sql stmt
# and some messy hack is needed to avoid param execution
sql_stmt = " ".join(alt_sql_statement)
sql_stmt = sql_stmt.replace("(%)", "(##)")
sql_stmt = re.sub(r"(\d+)%", "\g<1>##", sql_stmt)
sql_stmt = sql_stmt % sql_values
sql_stmt = sql_stmt.replace("(##)", "(%)")
sql_stmt = re.sub(r"(\d+)##", "\g<1>%", sql_stmt)
#print(sql_stmt)
session.execute(sql_stmt)
self.db_conn.commit()
except Exception:
#session.rollback()
raise
finally:
session.close()
return 1
