def get_table_row(self, table_model, access_key):
msg0=f"[{table_model.__tablename__}] [[get_table_row]] :"
filter_dict = self.smart_locate_expression(table_model, access_key)
msg1=f"search filter:#CYAN#{filter_dict}#RESET#"
query = self.build_query(table_model, filter_dict)
if not query:
msgx = f"#ERROR#query failed#RESET#"
msg = msg0 + " " + msgx + " " + msg1
if thisApp.application_configuration.database_engine_debug: print_message(msg,printLevel=1)
return None
query_rows = query.count()
str(query.count())
if not query_rows >= 1:
msgx = f"#ERROR#NOT FOUND#RESET# query result rows:#RED#{query_rows}#RESET#"
msg = msg0 + " " + msgx + " " + msg1
if thisApp.application_configuration.database_engine_debug: print_message(msg,printLevel=1)
return None
current_record_obj = query.first()
color = '#GREEN#'
if query_rows > 1:
color = '#RED#'
msgx = f"query result rows:{color}{query_rows}#RESET#"
msg = msg0 + " " + msgx + " " + msg1
if current_record_obj.debug_is_on() or thisApp.application_configuration.database_engine_debug: print_message(msg,printLevel=1)
return current_record_obj
def delete_rows(self, filter_dict={}, commit=True):
debug = self.debug
_api_name = 'delete_rows'
_api_action = 'DELETE'
msg0 = f"[{self.model.__tablename__}] [[delete_rows]] :"
action_status = 'error'
rows_deleted = 0
#messages = []
filter_dict = self.smart_locate_expression(filter_dict)
msg1 = f"filter:#CYAN#{filter_dict}#RESET#"
query = self.build_query(filter_dict)
if not query:
msgx = f"#ERROR#query failed#RESET#"
msgP = msg0 + " " + msgx + " " + msg1
if debug: print_message(msgP, printLevel=1)
return None
query_rows = query.count()
# current_record_objects = query.all()
msgx = f"query result rows:#GREEN#{query_rows}#RESET#"
if query_rows <= 0:
msg = f'zero rows to delete'
else:
query.delete()
if commit:
self.session.commit()
msg = f'OK. [{query_rows}] deleted'
rows_deleted = query_rows
else:
msg = f'OK. [{query_rows}] deleted (not committed)'
action_status = 'success'
api_result = {
'api_status': action_status,
'api_message': msg,
'api_data': {},
'rows_deleted': rows_deleted,
'api_action': _api_action.upper(),
'api_name': _api_name
}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return api_result
def update_validation(self, input_dict):
debug = self.debug
msg0 = f"[{self.model.__tablename__}] [[update_validation]] :"
current_record_obj = self.model()
valid_fields_dictionary = current_record_obj.valid_model_fields_dictionary(
input_dict)
current_record_obj.update_from_dict(printLevel=1,
debug=debug,
**valid_fields_dictionary)
(ok, messages
) = current_record_obj.update_validation(valid_fields_dictionary)
if not ok:
msg = f'update validation errors for model [{self.model_name}]'
msgC = colorized_message(msg, "#ERROR#")
else:
msg = f'OK. update validation passed for model [{self.model_name}]'
msgC = colorized_message(msg, "#SUCCESS#")
msgP = f"{msg0} {msgC}. OK=[{ok}] messages=[[{messages}]]"
if debug: print_message(msgP, printLevel=1)
return (ok, messages)
def get_row(self, access_key):
debug = self.debug
msg0 = f"[{self.model.__tablename__}] [[get_row]] :"
filter_dict = self.smart_locate_expression(access_key)
msg1 = f"search filter:#CYAN#{filter_dict}#RESET#"
query = self.build_query(filter_dict)
if not query:
msgx = f"#ERROR#query failed#RESET#"
msgP = msg0 + " " + msgx + " " + msg1
if debug: print_message(msgP, printLevel=1)
return None
query_rows = query.count()
if not query_rows >= 1:
msgx = f"#ERROR#NOT FOUND#RESET# query result rows:#RED#{query_rows}#RESET#"
msgP = msg0 + " " + msgx + " " + msg1
if debug: print_message(msgP, printLevel=1)
return None
current_record_obj = query.first()
color = '#GREEN#'
if query_rows > 1:
color = '#RED#'
msgx = f"query result rows:{color}{query_rows}#RESET#"
msgP = msg0 + " " + msgx + " " + msg1
if debug: print_message(msgP, printLevel=1)
return current_record_obj
def remove(self, input_dict, commit=False):
debug = self.debug
_api_name = 'remove'
_api_action = 'REMOVE'
msg0 = f"[{self.model.__tablename__}] [[remove]] :"
action_status = 'error'
rows_removed = 0
messages = []
current_record_obj = self.get_row(input_dict)
if not current_record_obj:
msg = f'[{self.model_name}] not found'
action_status = 'error'
current_record_obj = self.model()
valid_fields_dictionary = current_record_obj.valid_model_fields_dictionary(
input_dict)
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': valid_fields_dictionary, 'rows_removed': rows_removed, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return None
else:
self.session.delete(current_record_obj)
if commit:
self.session.commit()
msg = f'OK. [{self.model_name}] removed'
rows_removed = 1
else:
rows_removed = 0
msg = f'OK. [{self.model_name}] ready for remove (not committed)'
current_record_dict = current_record_obj.to_dict()
action_status = 'success'
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': current_record_dict, 'messages': messages, 'rows_removed': rows_removed, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return current_record_obj
def insert(self, input_dict, commit=False):
debug = self.debug
_api_name = 'insert'
_api_action = 'INSERT'
msg0 = f"[{self.model.__tablename__}] [[insert]] :"
action_status = 'error'
# rows_added = 0
# rows_updated = 0
# messages = []
current_record_obj = self.get_row(input_dict)
if current_record_obj:
msg = f'[{self.model_name}] already exists'
action_status = 'error'
current_record_dict = current_record_obj.to_dict()
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': current_record_dict, 'messages': messages, 'rows_added': rows_added, 'rows_updated': rows_updated, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return current_record_obj
else:
current_record_obj = self.model()
valid_fields_dictionary = current_record_obj.valid_model_fields_dictionary(
input_dict)
(ok, messages) = self.input_validation(valid_fields_dictionary)
if not ok:
msg = f'input validation errors for model [{self.model_name}]'
action_status = 'error'
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': valid_fields_dictionary, 'messages': messages, 'rows_added': rows_added, 'rows_updated': rows_updated, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return None
current_record_obj.update_from_dict(printLevel=1,
debug=debug,
**valid_fields_dictionary)
self.session.add(current_record_obj)
if commit:
self.session.commit()
msg = f'OK. [{self.model_name}] committed'
rows_added = 1
else:
msg = f'OK. [{self.model_name}] ready for insert (not committed)'
current_record_dict = current_record_obj.to_dict()
action_status = 'success'
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': current_record_dict, 'messages': messages, 'rows_added': rows_added, 'rows_updated': rows_updated, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return current_record_obj
def insert_or_update(self, table_model, input_dict,commit=False):
_api_name='insert_or_update'
msg0=f"[{table_model.__tablename__}] [[insert_or_update]] :"
filter_dict = self.smart_locate_expression(table_model, input_dict)
msg1=f"search filter:#CYAN#{filter_dict}#RESET#"
query = self.build_query(table_model, filter_dict)
if not query:
msgx = f"#ERROR#query failed#RESET#"
msg = msg0 + " " + msgx + " " + msg1
if thisApp.application_configuration.database_engine_debug: print_message(msg,printLevel=1)
return None
query_rows = query.count()
if not query_rows >= 1:
msgx = f"#ERROR#NOT FOUND#RESET# query result rows:#RED#{query_rows}#RESET#"
msg = msg0 + " " + msgx + " " + msg1
if thisApp.application_configuration.database_engine_debug: print_message(msg,printLevel=1)
current_record_obj = None
else:
current_record_obj = query.first()
color = '#GREEN#'
if query_rows > 1:
color = '#RED#'
msgx = f"query result rows:{color}{query_rows}#RESET#"
msg = msg0 + " " + msgx + " " + msg1
if current_record_obj.debug_is_on() or thisApp.application_configuration.database_engine_debug: print_message(msg,printLevel=1)
if not current_record_obj:
_api_action='ADD'
current_record_obj=table_model()
valid_fields_dictionary = current_record_obj.valid_model_fields_dictionary(input_dict)
current_record_obj.update_from_dict(**valid_fields_dictionary)
(ok,messages) = current_record_obj.input_validation(valid_fields_dictionary)
if not ok:
msg=f'input validation errors'
api_result = {'api_status': 'error', 'api_message': msg, 'api_data': valid_fields_dictionary, 'messages':messages, 'rows_added':0, 'api_action': _api_action.upper(), 'api_name':_api_name }
if thisApp.application_configuration.database_engine_debug: print_result(f"{msg0} result:", api_result, printLevel=1)
return None
self.session.add(current_record_obj)
if commit:
self.session.commit()
msg = f'OK. {table_model.__name__.upper()} added'
rows_added=1
else:
rows_added=0
msg = f'OK. {table_model.__name__.upper()} ready for addition'
current_record_dict = current_record_obj.to_dict()
api_result = {'api_status': 'success', 'api_message': msg, 'api_data': current_record_dict, 'rows_added':rows_added,'rows_updated':0, 'api_action': _api_action.upper(), 'api_name': _api_name}
if thisApp.application_configuration.database_engine_debug: print_result(f"{msg0} result:", api_result, printLevel=1)
return current_record_obj
else:
_api_action='UPDATE'
valid_fields_dictionary = current_record_obj.valid_model_fields_dictionary(input_dict)
current_record_obj.update_from_dict(**valid_fields_dictionary)
if not current_record_obj.has_model_changed():
msg=f"OK. {table_model.__name__.upper()} is synchronized. no changes applied"
api_result = {'api_status': 'success', 'api_message': msg, 'api_data': valid_fields_dictionary, 'rows_added':0,'rows_updated':0, 'api_action': _api_action.upper(), 'api_name':_api_name }
if thisApp.application_configuration.database_engine_debug: print_result(f"{msg0} result:", api_result, printLevel=1)
return current_record_obj
(ok,messages) = current_record_obj.update_validation(valid_fields_dictionary)
if not ok:
msg=f'update validation errors'
api_result = {'api_status': 'error', 'api_message': msg, 'api_data': api_record, 'messages':messages, 'rows_updated':0, 'api_action': _api_action.upper(), 'api_name':_api_name }
if thisApp.application_configuration.database_engine_debug: print_result(f"{msg0} result:", api_result, printLevel=1)
return None
self.session.add(current_record_obj)
if commit:
self.session.commit()
msg = f'OK. {table_model.__name__.upper()} updated'
rows_updated=1
else:
rows_updated=0
msg = f'OK. {table_model.__name__.upper()} ready for update'
current_record_dict = current_record_obj.to_dict()
api_result = {'api_status': 'success', 'api_message': msg, 'api_data': current_record_dict, 'rows_added':0,'rows_updated':rows_updated, 'api_action': _api_action.upper(), 'api_name': _api_name}
if thisApp.application_configuration.database_engine_debug: print_result(f"{msg0} result:", api_result, printLevel=1)
return current_record_obj
def update_rows(self, input_dict, filter_dict, commit=False):
debug = self.debug
_api_name = 'update_rows'
_api_action = 'UPDATE_ROWS'
msg0 = f"[{self.model.__tablename__}] [[update_rows]] :"
action_status = 'error'
row_count = 0
rows_updated = 0
messages = []
errors = 0
locate_dict = {**filter_dict}
update_rows = self.session.get_rows(locate_dict)
row_count = len(update_rows)
msg = f"OK. {row_count} rows retrieved"
if len(update_rows) <= 0:
msg = f"zero records found. No Update"
action_status = 'error'
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': [], 'messages': messages, 'rows_retrieved': row_count, 'rows_updated': rows_updated, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return None
for rowObj in update_rows:
valid_fields_dictionary = rowObj.valid_model_fields_dictionary(
input_dict)
rowObj.update_from_dict(printLevel=1,
debug=debug,
**valid_fields_dictionary)
if not rowObj.has_model_changed():
rows_synchronized = rows_synchronized + 1
rowuid = self.model.get_unique_identifier()
msg = f"[{rowuid}] is synchronized. no changes applied"
messages.append(msg)
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
continue
else:
(ok,
messages) = self.update_validation(valid_fields_dictionary)
if not ok:
errors = errors + 1
rowuid = self.model.get_unique_identifier()
msg = f"[{self.model_name}] [[{rowuid}]] with validation errors. update skipped"
messages.append(msg)
action_status = 'error'
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
continue
rowObj.update_from_dict(printLevel=1,
debug=debug,
**valid_fields_dictionary)
rows_updated = rows_updated + 1
if errors > 0:
action_status = 'error'
msg = f'errors encountered during update. on {errors} row(s)'
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': [], 'messages': messages, 'rows_retrieved': row_count, 'rows_updated': rows_updated, 'rows_with_errors': errors, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return None
action_status = 'success'
if rows_updated > 0:
if commit:
self.session.commit()
msg = f'OK. {rows_updated} [{self.model_name}] rows updated'
else:
msg = f'OK. {rows_updated} [{self.model_name}] rows updated (not committed)'
updated_rows_dict = self.rows_to_dict(update_rows)
api_result = {
'api_status': action_status,
'api_message': msg,
'api_data': updated_rows_dict,
'messages': messages,
'rows_retrieved': row_count,
'rows_updated': rows_updated,
'api_action': _api_action.upper(),
'api_name': _api_name
}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return api_result
def insert_or_update(self, input_dict, commit=False):
debug = self.debug
_api_name = 'insert_or_update'
_api_action = ''
msg0 = f"[{self.model.__tablename__}] [[insert_or_update]] :"
action_status = 'error'
rows_added = 0
rows_updated = 0
messages = []
current_record_obj = self.get_row(input_dict)
if not current_record_obj:
_api_action = 'ADD'
current_record_obj = self.model()
valid_fields_dictionary = current_record_obj.valid_model_fields_dictionary(
input_dict)
(ok, messages) = self.input_validation(valid_fields_dictionary)
if not ok:
msg = f'input validation errors for model [{self.model_name}]'
action_status = 'error'
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': valid_fields_dictionary, 'messages': messages, 'rows_added': rows_added, 'rows_updated': rows_updated, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return None
current_record_obj.update_from_dict(printLevel=1,
debug=debug,
**valid_fields_dictionary)
self.session.add(current_record_obj)
if commit:
self.session.commit()
msg = f'OK. [{self.model_name}] committed'
rows_added = 1
else:
msg = f'OK. [{self.model_name}] ready for addition (not committed)'
current_record_dict = current_record_obj.to_dict()
action_status = 'success'
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': current_record_dict, 'messages': messages, 'rows_added': rows_added, 'rows_updated': rows_updated, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return current_record_obj
else:
_api_action = 'UPDATE'
valid_fields_dictionary = current_record_obj.valid_model_fields_dictionary(
input_dict)
current_record_obj.update_from_dict(printLevel=1,
debug=debug,
**valid_fields_dictionary)
if not current_record_obj.has_model_changed():
msg = f"OK. [{self.model_name}] is synchronized. no changes applied"
action_status = 'success'
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': valid_fields_dictionary, 'messages': messages, 'rows_added': rows_added, 'rows_updated': rows_updated, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return current_record_obj
(ok, messages) = self.update_validation(valid_fields_dictionary)
if not ok:
msg = f'update validation errors for model [{self.model_name}]'
action_status = 'error'
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': valid_fields_dictionary, 'messages': messages, 'rows_added': rows_added, 'rows_updated': rows_updated, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return None
self.session.add(current_record_obj)
if commit:
self.session.commit()
msg = f'OK. [{self.model_name}] updated'
rows_updated = 1
else:
rows_updated = 0
msg = f'OK. [{self.model_name}] ready for update'
current_record_dict = current_record_obj.to_dict()
action_status = 'success'
#api_result = {'api_status': action_status, 'api_message': msg, 'api_data': valid_fields_dictionary, 'messages': messages, 'rows_added': rows_added, 'rows_updated': rows_updated, 'api_action': _api_action.upper(), 'api_name': _api_name}
msgP = msg0 + " " + colorized_message(
msg, "#" + action_status.upper() + "#")
if debug: print_message(msgP, printLevel=1)
return current_record_obj
def __init__(self,
model,
model_base,
schema,
engine,
session,
debug=None,
table_name=''):
if not model:
msg = f'model not provided for [[leandroutechnologyforward_database_table_class]]. STOPPED'
msgP = colorized_message(msg, "#ERROR#")
if debug: print_message(msgP, printLevel=1)
exit(0)
if not model_base:
msg = f'model Base not provided for [[leandroutechnologyforward_database_table_class]]. STOPPED'
msgP = colorized_message(msg, "#ERROR#")
if debug: print_message(msgP, printLevel=1)
exit(0)
if not engine and not session:
msg = f'engine or session not provided for [[leandroutechnologyforward_database_table_class]]. STOPPED'
msgP = colorized_message(msg, "#ERROR#")
if debug: print_message(msgP, printLevel=1)
exit(0)
self.model_base = model_base
self.model = model
self.table_name = self.model.__tablename__
self.model_name = self.model.__name__
if not table_name:
table_name = self.model.__tablename__.upper() + '_TABLE'
self.name = table_name
if not engine:
self.engine = self.session.bind.engine
else:
self.engine = engine
if not session:
Session = sessionmaker(bind=self.engine)
session = Session()
self.session = session
self.debug = None
if hasattr(self.model, "_debug"):
self.debug = self.model._debug
if not debug == None:
self.debug = debug
if self.debug == None:
debug = False
try:
xdebug = thisApp.application_configuration.database_engine_debug
if xdebug:
debug = thisApp.application_configuration.database_engine_debug
except:
pass
try:
xdebug = thisApp.application_configuration.database_debug
if xdebug:
debug = thisApp.application_configuration.database_debug
except:
pass
try:
xdebug = thisApp.application_configuration.database_tables_debug
if xdebug:
debug = thisApp.application_configuration.database_tables_debug
except:
pass
try:
xdebug = thisApp.application_configuration.database_models_debug
if xdebug:
debug = thisApp.application_configuration.database_models_debug
except:
pass
# #create the table if not exists
# self.model.__table__.create(self.engine,checkfirst=True)
# #synchronize_physical_table
# self.synchronize_physical_table()
# #check_table
# self.check_table()
#store in schema dictionary
schema.update({self.name: self})