def crearConexion(cls):
sucursales = cls.seleccionar()
lis_con = []
for sucursal in sucursales:
if sucursal.central == True:
pass
elif sucursal.activa == True:
SERVER = sucursal.server
HOST = sucursal.ip
USER = sucursal.username
PASSWORD = sucursal.password
DB_NAME = 'siipapx'
if cls.conexion is None:
try:
conexion = db.connect(HOST, USER, PASSWORD,
DB_NAME)
lis_con.append(conexion)
except Exception as e:
logger.error(f'OCURRIO EN ERROR --> {e}')
pass
else:
logger.error(f'ERROR AL CREAR OBJETO CONEXION{SERVER}')
pass
return lis_con
def get_connection(cls):
try:
cls.__connect = sqlite3.connect("datablog.db")
logger.debug(f"Conexion obtenida exitosamente: {cls.__connect}")
return cls.__connect
except Exception as e:
logger.error(f'Error al obtener la conexion: {cls.__connect}')
def get_cursor(cls):
try:
cls.__cursor = cls.__connect.cursor()
logger.debug(f"cursor obtneido exitosamente:{cls.__cursor}")
return cls.__cursor
except Exception as e:
logger.error(f'Error al obtner cursor:{cls.__cursor}')
def obtenerCursor(cls):
if cls.__cursor is None:
try:
cls.__cursor = cls.__conexion.cursor()
logger.debug(f'Se ha establecido el cursor: {cls.__cursor}')
except Exception as e:
logger.error(f"Error al establecer el cursor {e}")
sys.exit()
else:
return cls.__cursor
def close(cls):
try:
cls.__cursor.close()
logger.debug(f"cursor cerrado exitosamente:{cls.__cursor}")
except Exception as e:
logger.error(f'Error al cerrar cursor:{cls.__cursor}')
try:
cls.__connect.close()
logger.debug(f"conexion cerrada exitosamente:{cls.__cursor}")
except Exception as e:
logger.debug(f"Error al cerrar conexion:{cls.__cursor}")
def obtenerCursor(cls):
if cls.__cursor is None:
try:
cls.__cursor = cls.obtenerConexion().cursor()
logger.debug(f'Se abrio el cursor con éxito: {cls.__cursor}')
return cls.__cursor
except Exception as e:
logger.error(f'Error al obtener cursor:{e}')
sys.exit()
else:
return cls.__cursor
def __exit__(self, exception_type, exception_value, excepction_traceback):
logger.debug('Se ejecuta metodo __exit__')
# si exception_value , significa que hubo algun error
if exception_value:
self.__conn.rollback()
logger.error(f'Ocurrio una exepcion: {exception_value}')
else:
self.__conn.commit() # commit de la informacion (inserte, update, delete)
logger.debug('Commit de la transaccion y regresando la conexion al pool')
self.__cursor.close() # cerramos el cursor
Conexion.liberarConexion(self.__conn)
def cerrar(cls):
if cls.__cursor is not None:
try:
cls.__cursor.close()
except Exception as e:
logger.error(f'Error al cerrar cursor: {e}')
if cls.__conexion is not None:
try:
cls.__conexion.close()
except Exception as e:
logger.error(f'Error al cerrar conexión: {e}')
logger.debug('Se han cerrado los objetos de conexión y cursor')
def obtenerCursor(cls):
if cls.__cursor is None:
try:
# asginamos la conexion a la variable de clase, y llamamos la funcion .cursor() ya que retorna un objeto de conexion
cls.__cursor = cls.obtenerConexion().cursor()
logger.debug(
f'Se abrio el cursor correctamente: {cls.__cursor}')
return cls.__cursor
except Exception as e:
logger.error(f'Error al obtener el cursor: {e}')
sys.exit()
else:
return cls.__cursor
def close(cls):
if cls.__cursor is not None:
try:
cls.__cursor.close()
except Exception as e:
logger.error(f'Error to the close Cursor: {e}')
if cls.__connection is not None:
try:
cls.__connection.close()
except Exception as e:
logger.error(f'Error to the close Connection: {e}')
logger.debug('the connection and cursor objects have been closed')
def get_cursor(cls):
if cls.__cursor is None:
try:
cls.__cursor = cls.get_connected().cursor()
logger.debug(
f'the cursor was successfully opened: {cls.__cursor}')
return cls.__cursor
except Exception as e:
logger.error(f'error when get to the Cursor: {e}')
sys.exit()
else:
return cls.__cursor
def cerrar(cls):
if cls.__cursor is not None:
try:
cls.__cursor.close()
logger.debug("Se ha cerrado el cursor")
except Exception as e:
logger.error(f"Error al cerrar cursor {e}")
if cls.__db is not None:
try:
cls.__db.close()
logger.debug("Se ha cerrado la conexión")
except Exception as e:
logger.error(f"Error al cerrar conexión: {e}")
def cerrar(cls):
if cls.__conexion is not None:
try:
cls.__conexion.close()
except Exception as e:
logger.error(f'Conexión cerrada fallida: {e}')
if cls.__cursor is not None:
try:
cls.__cursor.close()
except Exception as e:
logger.error(f'Cursor cerrada fallida: {e}')
def eliminar(cls, persona):
try:
db = Conexion.obtenerConexion()
cursor = Conexion.obtenerCursor()
logger.debug(cursor.mogrify(cls.__delete))
logger.debug(f"Persona a eliminar: {persona}")
valores = (persona.getID(), )
cursor.execute(cls.__delete, valores)
db.commit()
return cursor.rowcount
except Exception as e:
db.rollback()
logger.error(f"Excepción al eliminar persona: {e}")
finally:
Conexion.cerrar()
def cerrar(cls):
if cls.__cursor is not None:
try:
cls.__cursor.close()
logger.debug(f'Se ha cerrado el cursor: {cls.__cursor}')
except Exception as e:
logger.error(f'Error al cerrar el cursor {e}')
sys.exit
if cls.__conexion is not None:
try:
cls.__conexion.close()
logger.debug(f'Se ha cerrado la conexion: {cls.__conexion}')
except Exception as e:
logger.error(f'Error al cerrar la conexión: {e}')
logger.debug("Se han cerrado todos los objetos de conexion y cursor")
def insertar(cls, persona):
try:
conexion = Conexion.obtenerConexion()
cursor = Conexion.obtenerCursor()
logger.debug(cursor.mogrify(cls.__INSERTAR))
logger.debug(f'Persona a insertar: {persona}')
valores = (persona.get_nombre(), persona.get_apellido(), persona.get_email())
cursor.execute(cls.__INSERTAR, valores)
conexion.commit()
return cursor.rowcount
except Exception as e:
conexion.rollback()
logger.error(f'Excepción al insertar persona: {e}')
finally:
Conexion.cerrar()
def eliminar(cls, persona):
try:
conexion = Conexion.obtenerConexion()
cursor = Conexion.obtenerCursor()
logger.debug(cursor.mogrify(cls.__ELIMINAR))
logger.debug(f'Persona a eliminar: {persona}')
valores = (persona.get_id_persona(), )
cursor.execute(cls.__ELIMINAR, valores)
conexion.commit()
return cursor.rowcount
except Exception as e:
conexion.rollback()
logger.error(f'Excepcion al eliminar')
finally:
Conexion.cerrar()
def actualizar(cls, persona):
try:
conexion = Conexion.obtenerConexion()
cursor = Conexion.obtenerCursor()
logger.debug(cursor.mogrify(cls.__ACTUALIZAR))
logger.debug(f'Persona a actualizar: {persona}')
valores_persona = (persona.get_nombre(), persona.get_apellido(),
persona.get_email(), persona.get_id_persona())
cursor.execute(cls.__ACTUALIZAR, valores_persona)
conexion.commit()
except Exception as e:
conexion.rollback()
logger.error(f'Excepcion al actualizar persona {e}')
finally:
Conexion.cerrar()
def obtenerConexion(cls):
if cls.__db is None:
try:
cls.__db = psycopg2.connect(user = cls.__userName,
password = cls.__password,
host = cls.__host,
port = cls.__dbPort,
database = cls.__database)
logger.debug(f"Conexión exitosa: {cls.__db}")
return cls.__db
except Exception as e:
logger.error(f"Error conectar a la BD: {e}")
sys.exit()
else:
return cls.__db
def obtenerConexion(cls):
if cls.__conexion is None:
try:
cls.__conexion = db.connect(host=cls.__HOST,
user=cls.__USERNAME,
password=cls.__PASSWORD,
port=cls.__DB_PORT,
database=cls.__DATABASE)
logger.debug(f'Conexión exitosa: {cls.__conexion}')
return cls.__conexion
except Exception as e:
logger.error(f'Error al conectar a la BD: {e}')
sys.exit()
else:
return cls.__conexion
def obtenerConexion(cls):
# verifica si no se ha inicializado la conexion
if cls.__conexion is None:
try:
cls.__conexion = db.connect(host=cls.__HOST,
user=cls.__USERNAME,
password=cls.__PASSWORD,
port=cls.__DB_PORT,
database=cls.__DATABASE)
logger.debug(f'Conexion exitosa: {cls.__conexion}')
return cls.__conexion
except Exception as e:
logger.error(f'Error al conectar a la BD: {e}')
sys.exit() # finaliza la ejecucion del programa
return cls.__conexion
def actualizar(cls, persona):
try:
db = Conexion.obtenerConexion()
cursor = Conexion.obtenerCursor()
logger.debug(cursor.mogrify(cls.__update))
logger.debug(f"Persona a actualizar: {persona}")
valores = (persona.getNombre(), persona.getApellido(),
persona.getEmail(), persona.getID())
cursor.execute(cls.__update, valores)
db.commit()
return cursor.rowcount
except Exception as e:
db.rollback()
logger.error(f"Excepción al actualizar persona: {e}")
finally:
Conexion.cerrar()
def get_connected(cls):
if cls.__connection is None:
try:
cls.__connection = db.connect(host=cls.__HOST,
user=cls.__USERNAME,
password=cls.__PASSWORD,
port=cls.__DB_PORT,
database=cls.__DATABASE)
logger.debug(f'successful connection: {cls.__connection}')
return cls.__connection
except Exception as e:
logger.error(f'error when connecting to the DB: {e}')
sys.exit()
else:
return cls.__connection
def obtenerCursor(cls):
if cls.__cursor is None:
try:
cls.__cursor = cls.__conexion.cursor()
logger.info(f'Cursor exitoso: {cls.__cursor}')
return cls.__cursor
except Exception as e:
logger.error(f'Cursor fallido: {e}')
sys.exit()
else:
return cls.__cursor
def obtenerPool(cls):
if cls.__pool is 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)
logger.debug(f'Creación de pool exitosa: {cls.__pool}')
return cls.__pool
except Exception as e:
logger.error(f'Error al crear el pool de conexiones: {e}')
sys.exit()
else:
return cls.__pool
def getPool(cls):
if cls.__pool is None:
try:
cls.__pool = pool.SimpleConnectionPool(cls.__MIN_CON,
cls.__MAX_CON,
host=cls.__HOST,
port=cls.__DB_PORT,
user=cls.__USERNAME,
password=cls.__PASSWORD,
database=cls.__DATABASE)
logger.debug(f'Pool Creation Successful')
return cls.__pool
except Exception as e:
logger.error(f'Erro Pool Creating => {e}')
sys.exit()
else:
return cls.__pool
def seleccionar(cls):
try:
Conexion.obtenerConexion()
cursor = Conexion.obtenerCursor()
logger.debug(f'Datos: {cursor.mogrify(cls.__SELECCIONAR)}')
cursor.execute(cls.__SELECCIONAR)
datos = cursor.fetchall()
logger.debug(datos)
except Exception as e:
logger.error(f'Error de consulta: {e}')
sys.exit()
finally:
Conexion.cerrar()
def cerrarConexion(cls):
# pregunta si no es None
if cls.__cursor is not None:
try:
cls.__cursor.close()
logger.debug('Cerro el cursor con exito')
except Exception as e:
logger.error(f"Error al cerrar cursor: {e}")
sys.exit()
if cls.__conexion is not None:
try:
cls.__conexion.close()
logger.debug('Cerro la conexion a la BD con exito')
except Exception as e:
logger.error(f"Error al cerrar la conexion: {e}")
sys.exit()
def delete(cls, person):
try:
connection = Connection.get_connected()
cursor = Connection.get_cursor()
logger.debug(cursor.mogrify(cls.__DELETE))
logger.debug(f'Person to delete: {person}')
value = (person.id_person, )
cursor.execute(cls.__DELETE, value)
connection.commit()
return cursor.rowcount
except Exception as e:
connection.rollback()
logger.error(f'Exception to delete person: {e}')
finally:
Connection.close()
def obtener_pool(cls):
if cls.__pool is None:
try:
# Abrir pool de conexiones
cls.__pool = pool.SimpleConnectionPool(cls.__MIN_CON,
cls.__MAX_CON,
user=cls.__USERNAME,
password=cls.__PASSWORD,
host=cls.__HOST,
port=cls.__DB_PORT,
database=cls.__DATABASE)
logger.debug(f'Pool creado: {cls.__pool}')
return cls.__pool
except Exception as e:
logger.error(f'Error al crear el pool: {e}')
sys.exit() # Termina la ejecución de nuestro programa
else:
return cls.__pool