def eliminar(cls, persona): with Conexion.obtenerConexion(): with Conexion.obtenerCursor() as cursor: valores = (persona.id_persona, ) cursor.execute(cls._ELIMINAR, valores) log.debug(f'Objeto eliminado: {persona}') return cursor.rowcount
def actualizar(cls, persona): with CursorDelPool() as cursor: valores = (persona.nombre, persona.apellido, persona.email, persona.id_persona) cursor.execute(cls._ACTUALIZAR, valores) log.debug(f'Persona actualizada: {persona}') return cursor.rowcount
def validationPassword(self, result: tuple): if result != None: log.debug(f"User {self.username} logged succesfully") return 3 else: log.error(f"Password incorrect to user {self.username}") return -3
def insertar(cls, persona): with Conexion.obtenerConexion(): with Conexion.obtenerCursor() as cursor: valores = (persona.nombre, persona.apellido, persona.email) cursor.execute(cls._INSERTAR, valores) log.debug(f'Persona insertada: {persona}') return cursor.rowcount
def login(self): try: with CursorPool() as cursor: values: tuple = (self.username, ) cursor.execute(self._LOGIN_USERNAME, values) result_login_username: tuple = cursor.fetchone() log.debug(result_login_username) validateUser = self.validationUsername( result=result_login_username) if validateUser == self._STATUS_USERNAME_OK: newValues: tuple = (self.username, self.password) cursor.execute(self._LOGIN, newValues) result_login: tuple = cursor.fetchone() validatePassword = self.validationPassword( result=result_login) if validatePassword == self._STATUS_PASSWORD_OK: user: User = User(result_login[0], result_login[1], result_login[2], result_login[3], result_login[4], result_login[5]) isAdmin: bool = user.isAdmin return isAdmin else: return self._STATUS_PASSWORD_FAILED else: return self._STATUS_USERNAME_FAILED except Exception as e: log.error(f"Error happened while login user {self.username}: {e}")
def getConnection(cls): try: connection = cls.getPool().getconn() log.debug(f"Connection obtained: {connection}") return connection except Exception as e: log.error(f"Error happened while getting connection: {e}") sys.exit()
def insertar(cls, persona): with ConexionDB.obtenerConexion() as conexion: with conexion.cursor() as cursor: valores = (persona.nombre, persona.apellido, persona.email, persona.year) cursor.execute(cls._INSERTAR, valores) log.debug(f'Dato a insertados: {persona}') return cursor.rowcount
def insertTerminal(cls,terminal:Terminal): try: with CursorPool() as cursor: values:tuple = (terminal.name,terminal.place,cls.asignNumberTerminal(terminal)) cursor.execute(cls._INSERT,values) log.debug(f"Terminal inserted: {terminal.name}") return cursor.rowcount except Exception as e: log.error(f"Error happened while inserting terminal {terminal.name}: {e}")
def updateTerminal(cls,terminal:Terminal): try: with CursorPool() as cursor: values:tuple = (terminal.name,terminal.place,cls.asignNumberTerminal(terminal),terminal.idTerminal) cursor.execute(cls._UPDATE,values) log.debug(f"Terminal {terminal.name} updated succesfully") cursor.rowcount except Exception as e: log.error(f"Error happened while updating terminal {terminal.name}: {e}")
def deleteTerminal(cls,terminal:Terminal): try: with CursorPool() as cursor: values:tuple = (terminal.idTerminal,) cursor.execute(cls._DELETE,values) log.debug(f"Terminal {terminal.name} (id: {terminal.idTerminal} deleted succesffuly") cursor.rowcount except Exception as e: log.error(f"Error happened while deleting terminal {terminal.name}: {e}")
def deleteBus(cls,bus:Bus): try: with CursorPool() as cursor: values:tuple = (bus.bus_plate,) cursor.execute(cls._DELETE,values) log.debug(f"Bus {bus.bus_plate} deleted succesfully") return cursor.rowcount except Exception as e: log.error(f"Error happened while deleting bus: {e}")
def __enter__(self): try: self._connection = Connection.getConnection() self._cursor = self._connection.cursor() log.debug( f"Connection and Cursor obtained succesfully: {self._cursor}") return self._cursor except Exception as e: log.error(f"Error happened while getting Cursor: {e}")
def updatePlace(cls,place:PlaceToTravel): try: with CursorPool() as cursor: values:tuple = (place.namePlace,place.idPlace) cursor.execute(cls._UPDATE,values) log.debug(f"Place updated: {place}") cursor.rowcount except Exception as e: log.error(f"Error happened while updating place: {e}")
def insertPlace(cls,place:PlaceToTravel): try: with CursorPool() as cursor: values:tuple = (place.namePlace,) cursor.execute(cls._INSERT,values) log.debug(f"Place inserted: {place.namePlace}") return cursor.rowcount except Exception as e: log.error(f"Error happened while inserting place: {e}")
def deleteUser(cls, user: User): try: with CursorPool() as cursor: values: tuple = (user.idUser, ) cursor.execute(cls._DELETE, values) log.debug(f"User deleted: {user}") return cursor.rowcount except Exception as e: log.error(f"Error happened while deleting user: {e}")
def updateUser(cls, user: User): try: with CursorPool() as cursor: values: tuple = (user.username, user.password, user.name, user.lastName, user.idUser) cursor.execute(cls._UPDATE, values) log.debug(f"User updated: {user}") return cursor.rowcount except Exception as e: log.error(f"Error happened while updating user: {e}")
def insertUser(cls, user: User): try: with CursorPool() as cursor: values: tuple = (user.username, user.password, user.name, user.lastName, user.isAdmin) cursor.execute(cls._INSERT, values) log.debug(f"User registred: {user}") return cursor.rowcount except Exception as e: log.error(f"Error happened while inserting user: {e}")
def seleccionar(cls): with CursorDelPool() as cursor: log.debug('Seleccionando usuarios.') cursor.execute(cls._SELECCIONAR) registros = cursor.fetchall() usuarios = [] for registro in registros: usuario = Usuario(registro[0], registro[1], registro[2]) usuarios.append(usuario) return usuarios
def __exit__(self, typeException, valueException, traceback): if valueException: self._connection.rollback() log.error( f"Error happened while executing query. We do rollback. Value Exception: {valueException}" ) else: self._connection.commit() log.debug(f"Commit running") self._cursor.close() Connection.unlockConnection(self._connection)
def terminalsByPlace(cls,terminal:Terminal) -> int: try: with CursorPool() as cursor: values:tuple = (terminal.place,) cursor.execute(cls._SELECT_TERMINAL_BY_PLACE,values) registers:tuple = cursor.fetchall() quantity:int = len(registers) log.debug(f"Terminals in this place ({terminal.place}): {quantity}") return quantity except Exception as e: log.error(f"Error happened while searching quantity terminals by place: {e}")
def obtenerCursor(cls): if cls._cursor is None: try: cls._cursor = cls.obtenerConexion().cursor() log.debug(f'Cursor exitoso: {cls._cursor}') return cls._cursor except Exception as e: log.error(f"Ocurrió una exception al obtener el cursor: {e}") sys.exit() else: return cls._cursor
def __exit__(self, tipo_excepcion, valor_excepcion, detalle_excepcion): log.debug('Se ejecuta método __exit__') if valor_excepcion: self._conexion.rollback() log.error( f'Ocurrió una excepción, se hace rollback: {valor_excepcion} {tipo_excepcion} {detalle_excepcion}' ) else: self._conexion.commit() log.debug('Commit de la transacción') self._cursor.close() Conexion.liberarConexion(self._conexion)
def insertBus(cls,bus:Bus): try: quantity_bus_by_terminal:int = cls.verifyQuantBusByTerminal(bus.terminal_id) if quantity_bus_by_terminal < 2: with CursorPool() as cursor: values:tuple = (bus.bus_plate,bus.capacity,bus.terminal_id) cursor.execute(cls._INSERT,values) log.debug(f"Bus {bus.bus_plate} registered succesfully") return cursor.rowcount else: log.error(f"Error happened while inserting bus. Exist {quantity_bus_by_terminal} buses on terminal with id {bus.terminal_id}") except Exception as e: log.error(f"Error happened while inserting bus: {e}")
def obtenerConexion(cls): if cls._conexion is None: try: cls._conexion = db.connect(host=cls._HOST, user=cls._USERNAME, password=cls._PASSWORD, port=cls._PORT, database=cls._DATABASE) log.debug(f'Conexion exitosa: {cls._conexion}') return cls._conexion except Exception as e: log.error(f"Ocurrió una exception: {e}") sys.exit() else: return cls._conexion
def getPool(cls): if cls._pool == None: try: cls._pool = pool.SimpleConnectionPool(cls._MIN_CON, cls._MAX_CON, host=cls._HOST, user=cls._USERNAME, password=cls._PASSWORD, port=cls._DB_PORT, database=cls._DATABASE) log.debug(f"Pool getted: {cls._pool}") return cls._pool except Exception as e: log.error(f"Error happenned while getting pool: {e}") else: return cls._pool
def getAllUsers(cls): try: with CursorPool() as cursor: users = [] cursor.execute(cls._SELECT) registers = cursor.fetchall() for register in registers: user: User = User(idUser=register[0], username=register[1], password=register[2], name=register[3], lastName=register[4]) log.debug(user) log.debug("Getting Users Succesfully") return users except Exception as e: log.error(f"Error happened while getting all users")
def getAllPlaces(cls): try: with CursorPool() as cursor: places:list = [] cursor.execute(cls._SELECT) registers = cursor.fetchall() for register in registers: place:PlaceToTravel = PlaceToTravel( idPlace=register[0], namePlace=register[1] ) places.append(place) log.debug(place) log.debug("Getting Places Succesfully") return places except Exception as e: log.error(f"Error happened while getting all places to travel: {e}")
def getAllBuses(cls): try: with CursorPool() as cursor: buses:list = [] cursor.execute(cls._SELECT) registers:tuple = cursor.fetchall() for register in registers: bus:Bus = Bus( bus_plate=register[0], capacity=register[1], terminal_id=register[2] ) buses.append(bus) log.debug(bus) return buses except Exception as e: log.error(f"Error happened while getting all buses: {e}")
def getAllTerminals(cls): try: with CursorPool() as cursor: terminals:list = [] cursor.execute(cls._SELECT) registers:tuple = cursor.fetchall() for register in registers: terminal:Terminal = Terminal( idTerminal=register[0], name=register[1], place=register[2], number=register[3] ) terminals.append(terminal) log.debug(terminal) log.debug("Getting Terminals Succesfully") return terminals except Exception as e: log.error(f"Error while getting terminals: {e}")
def verifyQuantBusByTerminal(cls,terminal_id:int): try: with CursorPool() as cursor: values:tuple = (terminal_id,) cursor.execute(cls._COUNT_TERMINAL,values) result:tuple = cursor.fetchone() quantity:int = result[0] if quantity == 0: log.debug(f"Don't exist bus on terminal with id:{terminal_id}") elif quantity == 1: log.debug(f"Exist {quantity} terminal on terminal with id: {terminal_id}") else: log.debug(f"Exist {quantity} terminals on terminal with id: {terminal_id}") return quantity except Exception as e: log.error(f"Error happened while verifying quantity bus by terminal: {e}")