def fetchall_notas(self):
try:
conn = Connection()
query = '''
SELECT * from alumno_nota;
'''
cursor = conn.execute_query(query)
rows = cursor.fetchall()
for row in rows:
print('=====================')
print(f'DNI => {row[0]}')
print(f'Bimestre => {row[8]}')
print(f'Nombre del alumno => {row[1]}')
print(f'Edad => {row[2]}')
print(f'Correo => {row[3]}')
print(f'Salon => {row[4]}')
print(f'Matematica => {row[5]}')
print(f'Comunicacion => {row[6]}')
print(f'Ingles => {row[7]}')
print('=====================')
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def promedio_salon(self, salon):
self.salon = salon
try:
conn = Connection()
query = f'''
SELECT matematica, comunicacion, ingles
from alumno_nota_salon Where salon='{salon}';
'''
cursor = conn.execute_query(query)
rows = cursor.fetchall()
suma_matematica = 0
suma_comunicacion = 0
suma_ingles = 0
cont = 0
for row in rows:
suma_matematica = row[0] + suma_matematica
suma_comunicacion = row[1] + suma_comunicacion
suma_ingles = row[2] + suma_ingles
cont = cont + 1
promedio_matematica = suma_matematica / cont
promedio_comunicacion = suma_comunicacion / cont
promedio_ingles = suma_ingles / cont
print(
f"El promedio en el salon de {salon} en matematica es de {promedio_matematica}"
)
print(
f"El promedio en el salon de {salon} en comunicacion es de {promedio_comunicacion}"
)
print(
f"El promedio en el salon de {salon} en ingles es de {promedio_ingles}"
)
# print('=====================')
# print(f'ID => {row[0]}')
# print(f'Bimestre => {row[8]}')
# print(f'Nombre del alumno => {row[1]}')
# print(f'Edad => {row[2]}')
# print(f'Correo => {row[3]}')
# print(f'Salon => {row[4]}')
# print(f'Matematica => {row[5]}')
# print(f'Comunicacion => {row[6]}')
# print(f'Ingles => {row[7]}')
# print('=====================')
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def delete_alumno(self, id):
try:
conn = Connection()
query = f'''
DELETE FROM alumno esor WHERE id = {id};
'''
cursor = conn.execute_query(query)
conn.commit()
print(f'Se elimino registro con ID {id}')
except Exception as e:
print(f'{str(e)}')
def update_alumno(self, id):
try:
conn = Connection()
query = f'''
UPDATE alumno SET nombre = '{self.nombre_alumno}', edad = {self.edad_alumno} WHERE id = {id};
'''
cursor = conn.execute_query(query)
conn.commit()
print(f'Se actualizo informacion de alumno {id} por -> {self.nombre_alumno} - {self.edad_alumno}')
except Exception as e:
print(f'{str(e)}')
def insert_notas(self):
try:
conn = Connection()
query = f'''
INSERT INTO notas (nota)
VALUES('{self.notas}')
'''
cursor = conn.execute_query(query)
conn.commit()
print(f'Se agrego nota -> {self.notas}')
except Exception as e:
print(f'{str(e)}')
def insert_profesor(self):
try:
conn = Connection()
query = f'''
INSERT INTO profesor (nombre, edad)
VALUES('{self.nombre_profesor}', {self.edad_profesor})
'''
cursor = conn.execute_query(query)
conn.commit()
print(f'Se agrego profesor -> {self.nombre_profesor}')
except Exception as e:
print(f'{str(e)}')
def insert_alumno(self):
try:
#[model, price] => model,price
conn = Connection()
query = f'''
INSERT INTO alumno (nombre, edad)
VALUES('{self.nombre_alumno}', {self.edad_alumno})
'''
cursor = conn.execute_query(query)
conn.commit()
print(f'Se agrego alumno -> {self.nombre_alumno}')
except Exception as e:
print(f'{str(e)}')
def fetchall_salones(self):
try:
conn = Connection()
query = '''
SELECT * FROM salones;
'''
cursor = conn.execute_query(query)
rows = cursor.fetchall()
for row in rows:
print(f'ID = {row[0]}')
print(f'Seccion = {row[1]}')
print('=====================')
except Exception as e:
print(f'{str(e)}')
def getSelectStudentNum(courseId):
conn = Connection.getConnection()
cursor = conn.cursor()
sql = "select count(*) from selection where course_id = %d" % (
courseId)
cursor.execute(sql)
return cursor.fetchone()[0]
def getSelectCourseInfo(studentId):
conn = Connection.getConnection()
cursor = conn.cursor()
sql = "select * from course where course_id in (select course_id from selection where student_id = %d)" % (
studentId)
cursor.execute(sql)
return cursor.fetchall()
def fetchall_notas(self):
try:
conn = Connection()
query = '''
SELECT * FROM notas
ORDER by nota DESC;
'''
cursor = conn.execute_query(query)
rows = cursor.fetchall()
for row in rows:
print(f'ID = {row[0]}')
print(f'Notas = {row[1]}')
print('=====================')
except Exception as e:
print(f'{str(e)}')
class Disponibilidad:
def __init__(self):
self.model = Connection('disponibilidad')
def get_disponibilidades(self, order):
return self.model.get_all(order)
def get_disponibilidad(self, id_object):
return self.model.get_by_id(id_object)
# NO SE REQUIERE "INSERTAR" DISPONIBILIDAD
# PUESTO QUE TODOS SE MODIFICARÁN CON LOS COMANDOS DE LOS OTROS DOS ARCHIVO
# def insert_libros(self, libros):
# return self.model.insert(libros)
def update_disponibilidad(self, id_object, data):
return self.model.update(id_object, data)
def fetchall_alumno(self):
try:
conn = Connection()
query = '''
SELECT * FROM alumno;
'''
cursor = conn.execute_query(query)
rows = cursor.fetchall()
for row in rows:
print(f'ID = {row[0]}' )
print(f'Nombre = {row[1]}')
print(f'Edad = {row[2]}')
print('=====================')
except Exception as e:
print(f'{str(e)}')
def getSelectCourseNum(studentId):
conn = Connection.getConnection()
cursor = conn.cursor()
sql = "select count(*) from selection where student_id = %d" % (
studentId)
cursor.execute(sql)
result = cursor.fetchone()
return result[0]
def update_nota(self, id, bimestre, matematica, comunicacion, ingles):
self.id = id
self.bimestre = bimestre
self.matematica = matematica
self.comunicacion = comunicacion
self.ingles = ingles
try:
conn = Connection()
query = f'''
UPDATE nota SET matematica = {matematica}, comunicacion = {comunicacion}, ingles = {ingles}, bimestre = {bimestre} WHERE id = {id};
'''
cursor = conn.execute_query(query)
conn.commit()
print(
f'''Se ha actualizado las notas del alumno registrado con el DNI {id}:
Matematica: {matematica}
Comunicacion: {comunicacion}
Ingles: {ingles}
''')
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def insert_docente(self, ident, nombre, edad, correo, curso):
self.ident = ident
self.nombre = nombre
self.edad = edad
self.correo = correo
self.curso = curso
try:
conn = Connection()
query = f'''
INSERT INTO profesor (id, nombre_profesor, edad, correo, curso) VALUES ('{ident}','{nombre}', {edad} , '{correo}' ,'{curso}')
'''
cursor = conn.execute_query(query)
conn.commit()
print(
f"Se ha registrado correctamente el profesor con nombre {nombre}"
)
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def update_alumno(self, id, nombre, edad, correo, salon):
self.id = id
self.nombre = nombre
self.edad = edad
self.correo = correo
self.salon = salon
try:
conn = Connection()
query = f'''
UPDATE alumno SET nombre_alumno = '{nombre}', edad = {edad}, correo = '{correo}', salon = '{salon}' WHERE id = {id};
'''
cursor = conn.execute_query(query)
conn.commit()
print(f'''Se ha actualizado al alumno registrado con el id {id}:
Nombre: {nombre}
Edad: {edad}
Correo: {correo}
Salón: {salon}
''')
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def update_docente(self, id, nombre, edad, correo, curso):
self.id = id
self.nombre = nombre
self.edad = edad
self.correo = correo
self.curso = curso
try:
conn = Connection()
query = f'''
UPDATE profesor SET nombre_profesor = '{nombre}', edad = {edad}, correo = '{correo}', curso = '{curso}' WHERE id = {id};
'''
cursor = conn.execute_query(query)
conn.commit()
print(f'''Se ha actualizado al profesor registrado con el id {id}:
Nombre: {nombre}
Edad: {edad}
Correo: {correo}
Curso: {curso}
''')
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def insert_alumno(self, ident, nombre, edad, correo, salon):
self.ident = ident
self.nombre = nombre
self.edad = edad
self.correo = correo
self.salon = salon
try:
conn = Connection()
query = f'''
INSERT INTO alumno (id, nombre_alumno, edad, correo, salon) VALUES ('{ident}','{nombre}', {edad} , '{correo}' ,'{salon}')
'''
cursor = conn.execute_query(query)
conn.commit()
print(
f"Se ha registrado correctamente el alumno con nombre {nombre}"
)
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def create_table(self, nombre_tabla):
self.nombre_tabla = nombre_tabla
try:
conn = Connection()
query = f'''
def fetchall_alumnos(self):
try:
conn = Connection()
query = '''
SELECT * from alumno;
'''
cursor = conn.execute_query(query)
rows = cursor.fetchall()
for row in rows:
print(f'DNI => {row[0]}')
print(f'Nombre => {row[1]}')
print(f'Edad => {row[2]}')
print(f'Correo => {row[3]}')
print(f'Salon => {row[4]}')
print('=====================')
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def fetchall_docentes_salon(self):
try:
conn = Connection()
query = '''
SELECT * from profesor_salon_completo;
'''
cursor = conn.execute_query(query)
rows = cursor.fetchall()
for row in rows:
print('=====================')
print(f'Nombre => {row[1]}')
print(f'Edad => {row[2]}')
print(f'Correo => {row[3]}')
print(f'Curso => {row[4]}')
print(f'Salón => {row[5]}')
print('=====================')
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def selectCourse(student_id, course_id):
conn = Connection.getConnection()
cursor = conn.cursor()
sql = "select * from selection \
where student_id=%d and course_id=%d" % (student_id, course_id)
cursor.execute(sql)
if (len(cursor.fetchall()) == 0):
sql = "insert into selection values(%d, %d)" % (student_id,
course_id)
cursor.execute(sql)
conn.commit()
return ('1', u"成功选择该课程")
else:
return ('0', u"已经选过该课程")
def quitCourse(student_id, course_id):
conn = Connection.getConnection()
cursor = conn.cursor()
sql = "select * from selection \
where student_id=%d and course_id=%d" % (student_id, course_id)
cursor.execute(sql)
if (len(cursor.fetchall()) > 0):
sql = "delete from selection \
where student_id=%d and course_id=%d" % (student_id, course_id)
cursor.execute(sql)
conn.commit()
return ('1', u"成功退选该课程")
else:
return ('0', u"没有选择该课程")
def delete_nota(self, id):
try:
conn = Connection()
query = f'''
DELETE FROM nota WHERE id ={id};
'''
cursor = conn.execute_query(query)
conn.commit()
print(f'Se elimino el alumno registrado con el ID {id}')
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def create_table_notas(self):
try:
conn = Connection()
query = '''
CREATE TABLE IF NOT EXISTS notas(
id SERIAL PRIMARY KEY NOT NULL,
nota REAL
);
'''
conn.execute_query(query)
conn.commit()
except Exception as e:
raise print(e)
def create_table_nota(self):
try:
conn = Connection()
query = '''
CREATE TABLE IF NOT EXISTS nota(
id SERIAL PRIMARY KEY NOT NULL,
matematica REAL,
comunicacion REAL,
ingles REAL,
bimestre integer
);
'''
conn.execute_query(query)
conn.commit()
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def create_table_profesor(self):
try:
conn = Connection()
query = '''
CREATE TABLE IF NOT EXISTS profesor(
Id SERIAL PRIMARY KEY NOT NULL,
nombre varchar(50) NOT NULL,
edad REAL
);
'''
conn.execute_query(query)
conn.commit()
except Exception as e:
raise print(e)
def create_table_docente(self):
try:
conn = Connection()
query = '''
CREATE TABLE IF NOT EXISTS profesor(
id SERIAL PRIMARY KEY NOT NULL,
nombre_profesor varchar(50) NOT NULL,
edad REAL,
correo varchar(75) NOT NULL,
curso varchar(50) NOT NULL
);
'''
conn.execute_query(query)
conn.commit()
except Exception as e:
print(f'{str(e)}')
finally:
conn.close_connection()
def create_table_salones(self):
try:
conn = Connection()
query = '''
CREATE TABLE IF NOT EXISTS salones(
id SERIAL PRIMARY KEY NOT NULL,
Seccion varchar(50) NOT NULL
);
'''
conn.execute_query(query)
conn.commit()
except Exception as e:
print(f'{str(e)}')
