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)