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}")
