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
コード例 #2
0
    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
コード例 #3
0
 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)
コード例 #4
0
 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
コード例 #5
0
    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
コード例 #6
0
    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
コード例 #8
0
    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
コード例 #9
0
    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
コード例 #10
0
    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})