def groups():
groups_schema = GroupsSchema(many=True)
res = {}
if request.method == "GET":
groups_res = query_db('select * from groups ')
groups_sch = groups_schema.load(groups_res)
res = format_groups(groups_sch.data)
elif request.method == "POST":
groups_data = groups_schema.loads(request.data)
if not groups_data.errors:
for new_group in groups_data.data:
query_db(
'INSERT INTO groups (name, departament_id) VALUES (?,?)',
(new_group['name'], new_group['departament_id']))
res = 'Ok'
get_db().commit()
else:
res = groups_data.errors
elif request.method == "DELETE":
id_schema = IdListSchema().loads(request.data)
if not id_schema.errors:
res = 'Ok'
question_marks = ['?'] * (len(id_schema.data['id_list']))
query_db(
'delete from groups where id in ({question_marks})'.format(
question_marks=','.join(question_marks)),
id_schema.data['id_list'])
get_db().commit()
else:
res = id_schema.errors
return jsonify(res)
def courses():
courses_sch = CoursesSchema(many=True)
res = {}
if request.method == 'GET':
courses_data = query_db('SELECT * FROM course ')
res = courses_sch.load(courses_data).data
elif request.method == 'POST':
courses_list = courses_sch.loads(request.data)
if not courses_list.errors:
for new_course in courses_list.data:
query_db('INSERT INTO course (id, name, teacher_id, group_id, semester, duration) '
'VALUES (?,?,?,?,?,?)', (new_course.get('id'), new_course.get('name'),
new_course.get('teacher_id'), new_course.get('group_id'),
new_course.get('semester'), new_course.get('duration')))
get_db().commit()
res = 'Ok'
else:
res = courses_list.errors
elif request.method == 'DELETE':
id_list = IdListSchema().loads(request.data)
if not id_list.errors:
question_marks = ['?'] * len(id_list.data)
query_db('delete from course '
'where id in ({question_marks}) '
.format(question_marks=','.join(question_marks)),
id_list.data['id_list'])
get_db().commit()
res = 'Ok'
else:
res = id_list.errors
return jsonify(res)
def student(student_id):
if request.method == 'GET':
res = query_db(
'SELECT s.* FROM students AS s '
'JOIN groups AS g ON s.group_id = g.id '
'JOIN departaments AS d ON g.departament_id = d.id '
'WHERE s.id=?', (student_id, ))
return jsonify(res)
elif request.method == 'PUT':
student_schema = StudentsSchema(field_requirement=False).loads(
request.data)
res = 'Ok'
if not student_schema.errors:
for field in student_schema.data:
query_db(
'update students set {field_name}=? where id=?'.format(
field_name=field),
(student_schema.data[field], student_id))
get_db().commit()
else:
res = student_schema.errors
return jsonify(res)
elif request.method == 'DELETE':
query_db("DELETE FROM students WHERE id=?;", (student_id, ))
get_db().commit()
return jsonify('Ok')
def teacher_courses(teacher_id):
courses_data = query_db(
'SELECT * FROM course AS c '
'JOIN teachers AS t ON c.teacher_id = t.id '
'JOIN departaments AS d ON t.departament_id = d.id '
'WHERE c.teacher_id=? ', (teacher_id, ))
return jsonify(CoursesSchema().load(courses_data))
def main():
conn = db_utils.connect('localhost', 'root', 'boletin_oficial')
g = Graph()
# ['articulo_id', 'articulo_texto', 'dependencia_id', 'dependencia_nombre', 'per_apellido', 'per_boletines', 'per_cuit', 'per_dni', 'per_domicilio_especial', 'per_estado_civil', 'per_id', 'per_nombre', 'per_nya', 'per_prefijo', 'per_sufijo', 'per_titulo', 'puesto_id', 'puesto_nombre']
for r in db_utils.query_db(conn.cursor(), QUERY):
articulo = g.articulos.create(id=r['articulo_id'],
texto=r['articulo_texto'].decode('utf-8'))
try:
dependencia = list(g.dependencias.index.lookup(id=r['dependencia_id']))[0]
except IndexError:
dependencia = g.dependencias.create(id=r['dependencia_id'],
nombre=r['dependencia_nombre'].decode('utf-8'))
try:
puesto = list(g.puestos.index.lookup(id=r['puesto_id']))[0]
except IndexError:
puesto = g.puestos.create(id=r['puesto_id'], nombre=r['puesto_nombre'].decode('utf-8'))
try:
persona = list(g.personas.index.lookup(id=r['per_id']))[0]
except IndexError:
persona = g.personas.create(dni=r['per_dni'],
nombre_y_apellido=r['per_nya'].decode('utf-8'),
id=r['per_id'])
g.nombramientos.create(persona, dependencia, { 'puesto_id': r['puesto_id'] })
g.plantel.create(dependencia, puesto, { 'persona_id': r['per_id'] })
LOG.info('%r - %r - %r %r' % (articulo, dependencia, puesto, persona,))
def main():
conn = db_utils.connect('localhost', 'root', 'boletin_oficial')
g = Graph()
try:
fuente = list(
g.fuentes_de_informacion.index.lookup(
nombre="DineroYPolitica.org"))[0]
except IndexError:
fuente = g.fuentes_de_informacion.create(nombre="DineroYPolitica.org")
for r in db_utils.query_db(conn.cursor(), QUERY):
# XXX TODO
# partidos politicos
# personas (fisicas, juridicas) ver que onda con los cuits/dnis. tabla providers
# donaciones
# listas?
LOG.info('%r - %r - %r %r' % (
articulo,
dependencia,
puesto,
persona,
))
def group(group_id):
if request.method == 'GET':
group_data = query_db('select * from groups ' 'and id=?', (group_id, ))
res = format_groups(GroupsSchema().load(group_data).data)
elif request.method == 'PUT':
group_schema = GroupsSchema().loads(request.data)
if not group_schema.errors:
for g in group_schema.data:
query_db(
'update groups set {field_name}=? where id=?'.format(
field_name=g), (
group_schema.data[g],
group_id,
))
get_db().commit()
res = 'Ok'
else:
res = group_schema.errors
return jsonify(res)
def courses_view():
courses_data = query_db('SELECT c.id, c.name, g.name AS group_name, '
'd.name AS departament_name, d.institute, '
'g.course, c.semester, t.name AS teacher_name, '
'c.duration FROM course AS c '
'JOIN groups AS g ON c.group_id = g.id '
'JOIN teachers AS t ON c.teacher_id = t.id '
'JOIN departaments AS d ON g.departament_id = d.id ')
course_sch = CoursesSchema(many=True).load(courses_data).data
return jsonify(course_sch)
def course(course_id):
res = {}
if request.method == 'GET':
course_data = query_db('select * from course where id={course_id}'.format(course_id=course_id))
res = CoursesSchema(many=True).load(course_data).data
elif request.method == 'PUT':
fields = CoursesSchema().loads(request.data)
if not fields.errors:
for field in fields.data:
query_db('update course set {field_name}=?'
.format(field_name=field),
(fields.data[field],))
get_db().commit()
res = 'Ok'
else:
res = fields.errors
elif request.method == 'DELETE':
query_db('DELETE FROM course WHERE id=?', (course_id,))
get_db().commit()
res = 'Ok'
return jsonify(res)
def teachers_view():
teachers_data = query_db(
'select t.id, d.name as departament_name, '
'd.institute, t.name, t.gender, t.birth_date, '
't.phone_number from teachers as t '
'join departaments as d on t.departament_id = d.id ')
teachers_sch = TeachersSchema(many=True).load(teachers_data).data
for t in teachers_sch:
birth_date = datetime.strptime(t.pop('birth_date'), '%Y-%m-%d')
t['age'] = calculate_age(birth_date)
return jsonify(teachers_sch)
def teacher(teacher_id):
res = {}
if request.method == 'GET':
courses_rel = query_db(
'SELECT t.id, c.id AS course_id FROM teachers AS t '
'JOIN course AS c ON c.teacher_id = t.id '
'WHERE t.id=? ', (teacher_id, ))
teacher_data = query_db('SELECT * FROM teachers WHERE id=? ',
(teacher_id, ))
res = format_teachers(
TeachersSchema(many=True).load(teacher_data).data, courses_rel)
elif request.method == 'PUT':
teacher_fields = TeachersSchema().loads(request.data)
if not teacher_fields.errors:
for field in teacher_fields.data:
query_db(
'update teachers set {field_name}=? '
'where id=?'.format(field_name=field),
(teacher_fields.data[field], teacher_id))
get_db().commit()
res = 'Ok'
else:
res = teacher_fields.errors
elif request.method == 'DELETE':
query_db('DELETE FROM teachers ' 'WHERE id=? ', (teacher_id, ))
res = 'Ok'
return jsonify(res)
def students():
student_schema = StudentsSchema(many=True)
if request.method == 'GET':
query_res = query_db(
'SELECT s.* FROM students AS s '
'JOIN groups AS g ON s.group_id = g.id '
'JOIN departaments AS d ON g.departament_id = d.id')
res = student_schema.load(query_res).data
return jsonify(res)
elif request.method == 'POST':
# TODO валидация
student_schema = StudentsSchema(many=True).loads(request.data)
if not student_schema.errors:
student_data = student_schema.data
res = 'Ok'
for s in student_data:
query_db(
"INSERT INTO students (name, gender, birth_date, phone_number, group_id) "
"VALUES (?,?,?,?,?)",
(s['name'], s['gender'], s.get('birth_date'),
s.get('phone_number'), s.get('group_id')))
get_db().commit()
else:
res = student_schema.errors
return jsonify(res)
elif request.method == 'DELETE':
# TODO добавить детальную обработку запроса (вывод информации о удалении, либо об отсутствии студентов
# TODO с таким id)
id_schema = IdListSchema().loads(request.data)
if not id_schema.errors:
question_marks = ['?'] * (len(id_schema.data['id_list']))
res = query_db(
"delete from students where id in ({question_marks});".format(
question_marks=','.join(question_marks)),
id_schema.data['id_list'])
get_db().commit()
else:
res = id_schema.errors
return jsonify(res)
def students_view():
students_data = query_db(
'select s.id, s.name, g.name as group_name, g.course, '
's.gender, s.birth_date, s.phone_number, d.name as departament_name '
'from students as s '
'join groups as g on s.group_id = g.id '
'join departaments as d on d.id = g.departament_id')
students_sch = StudentsSchema(many=True).load(students_data).data
for s in students_sch:
birth_date_string = s.pop('birth_date', None)
if birth_date_string:
birth_date = datetime.strptime(birth_date_string, '%Y-%m-%d')
s['age'] = calculate_age(birth_date)
else:
s['age'] = None
return jsonify(students_sch)
def departament(departament_id):
res = {}
status_code = 200
if request.method == 'GET':
departament_data = query_db('SELECT * FROM departaments '
'WHERE id=?', (departament_id, ))
departament_groups = query_db(
'SELECT d.id FROM departaments AS d '
'WHERE d.id=? AND exists (SELECT * FROM groups AS g '
'WHERE g.departament_id=d.id)', (departament_id, ))
departament_teachers = query_db(
'SELECT d.id FROM departaments AS d '
'WHERE d.id=? AND exists(SELECT * FROM teachers AS t '
'WHERE d.id=t.departament_id)', (departament_id, ))
res = format_departaments(
DepartamentsSchema(many=True).load(departament_data).data,
departament_groups, departament_teachers)
elif request.method == 'PUT':
fields = DepartamentsSchema().loads(request.method)
if not fields.errors:
for field in fields.data:
query_db(
'update departaments set {field_name}=?'.format(
field_name=field), (fields.data[field], ))
get_db().commit()
res = 'Ok'
else:
res = fields.errors
elif request.method == 'DELETE':
check = query_db('select count(*) from departaments where id=?',
(departament_id, ), True)
if check['count(*)'] > 0:
query_db('delete from departaments where id=?', (departament_id, ))
get_db().commit()
res = 'Ok'
else:
res = 'Resource has been gone'
status_code = 410 # Gone
response = jsonify(res)
response.status_code = status_code
return response
def main():
conn = db_utils.connect('localhost', 'root', 'boletin_oficial')
g = Graph()
try:
fuente = list(g.fuentes_de_informacion.index.lookup(nombre="DineroYPolitica.org"))[0]
except IndexError:
fuente = g.fuentes_de_informacion.create(nombre="DineroYPolitica.org")
for r in db_utils.query_db(conn.cursor(), QUERY):
# XXX TODO
# partidos politicos
# personas (fisicas, juridicas) ver que onda con los cuits/dnis. tabla providers
# donaciones
# listas?
LOG.info('%r - %r - %r %r' % (articulo, dependencia, puesto, persona,))
def main():
conn = db_utils.connect('localhost', 'root', 'boletin_oficial')
g = Graph()
# ['articulo_id', 'articulo_texto', 'dependencia_id', 'dependencia_nombre', 'per_apellido', 'per_boletines', 'per_cuit', 'per_dni', 'per_domicilio_especial', 'per_estado_civil', 'per_id', 'per_nombre', 'per_nya', 'per_prefijo', 'per_sufijo', 'per_titulo', 'puesto_id', 'puesto_nombre']
for r in db_utils.query_db(conn.cursor(), QUERY):
articulo = g.articulos.create(
id=r['articulo_id'], texto=r['articulo_texto'].decode('utf-8'))
try:
dependencia = list(
g.dependencias.index.lookup(id=r['dependencia_id']))[0]
except IndexError:
dependencia = g.dependencias.create(
id=r['dependencia_id'],
nombre=r['dependencia_nombre'].decode('utf-8'))
try:
puesto = list(g.puestos.index.lookup(id=r['puesto_id']))[0]
except IndexError:
puesto = g.puestos.create(
id=r['puesto_id'], nombre=r['puesto_nombre'].decode('utf-8'))
try:
persona = list(g.personas.index.lookup(id=r['per_id']))[0]
except IndexError:
persona = g.personas.create(
dni=r['per_dni'],
nombre_y_apellido=r['per_nya'].decode('utf-8'),
id=r['per_id'])
g.nombramientos.create(persona, dependencia,
{'puesto_id': r['puesto_id']})
g.plantel.create(dependencia, puesto, {'persona_id': r['per_id']})
LOG.info('%r - %r - %r %r' % (
articulo,
dependencia,
puesto,
persona,
))
def departaments():
departaments_sch = DepartamentsSchema(many=True)
res = {}
if request.method == 'GET':
departaments_data = query_db('SELECT * FROM departaments')
departaments_groups = query_db(
'SELECT d.id FROM departaments AS d '
'WHERE exists (SELECT * FROM groups AS g '
'WHERE g.departament_id=d.id)')
departaments_teachers = query_db(
'SELECT d.id FROM departaments AS d '
'WHERE exists(SELECT * FROM teachers AS t '
'WHERE d.id=t.departament_id)')
res = format_departaments(
departaments_sch.load(departaments_data).data, departaments_groups,
departaments_teachers)
elif request.method == 'POST':
new_departaments = departaments_sch.loads(request.data)
if not new_departaments.errors:
for d in new_departaments.data:
query_db(
'INSERT INTO departaments (name, institute) '
'VALUES (?,?) ', (d.get('name'), d.get('institute')))
get_db().commit()
res = 'Ok'
else:
res = new_departaments.errors
elif request.method == 'DELETE':
id_list = IdListSchema().loads(request.data)
if not id_list.errors:
question_marks = ['?'] * len(id_list.data['id_list'])
query_db(
'delete from departaments '
'where id in ({question_marks})'.format(
question_marks=','.join(question_marks)),
id_list.data['id_list'])
get_db().commit()
res = 'Ok'
else:
res = id_list.errors
return jsonify(res)
def student_report_one(student_id):
student_courses_data = query_db(
'select s.name, g.name as group_name, '
'c.name as course_name, t.name as teacher_name, '
'c.duration as duration '
'from students as s '
'join groups as g on s.group_id = g.id '
'join course as c on g.id = c.group_id '
'join teachers as t on c.teacher_id = t.id '
'join departaments as d on g.departament_id = d.id '
'where s.id=?', (student_id, ))
if len(student_courses_data) > 0:
ReportWriter(report_type=STUDENT_REPORT).make_report({
'headings':
list(student_courses_data[0].keys()),
'report_data':
student_courses_data
})
return send_from_directory(os.path.join(os.path.curdir, 'reports'),
'report_1.xlsx',
as_attachment=True)
else:
return jsonify('Something wrong happened', status=403)
def teachers():
teachers_schema = TeachersSchema(many=True)
res = {}
if request.method == 'GET':
teachers_courses = query_db(
'SELECT t.id, c.id AS course_id FROM teachers AS t '
'JOIN course AS c ON c.teacher_id = t.id ')
teachers_data = query_db('SELECT * FROM teachers ')
teachers_sch = teachers_schema.load(teachers_data)
res = format_teachers(teachers_sch.data, teachers_courses)
elif request.method == 'POST':
teachers_sch = teachers_schema.loads(request.data)
if not teachers_sch.errors:
for t in teachers_sch.data:
query_db(
'INSERT INTO teachers (name, gender, birth_date, phone_number, departament_id) '
'VALUES (?,?,?,?,?)',
(t.get('name'), t.get('gender'), t.get('birth_date'),
t.get('phone_number'), t.get('departament_id')))
get_db().commit()
res = 'Ok'
else:
res = teachers_sch.errors
elif request.method == 'DELETE':
id_sch = IdListSchema().loads(request.data)
if not id_sch.errors:
question_marks = ['?'] * len(id_sch.data['id_list'])
query_db(
'delete from teachers where id in ({question_marks})'.format(
question_marks=','.join(question_marks)),
id_sch.data['id_list'])
get_db().commit()
res = 'Ok'
else:
res = id_sch.errors
return jsonify(res)
def group_courses(group_id):
courses = query_db('select * from course '
'where group_id={group_id}'.format(group_id=group_id))
return jsonify(courses)
def get_ratings(user_id):
ratings = query_db("select * from ratings WHERE user_id = ?", [user_id])
return jsonify(ratings)
