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 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 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 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 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 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 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 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 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 export():
for i in Projects.objects.all():
if not i.game:
continue
db_name, game_alias = i.code, i.game.code
database_proxy.initialize(get_db(db_name='db_' + db_name))
try:
database_proxy.connection()
except:
continue
for table in all_table:
tb_name = table._meta.table_name
print('export {}.{}'.format(db_name, tb_name))
pk_id = faster_export(db_name, game_alias, table)
if pk_id:
write_ok_file(db_name, tb_name, pk_id)
database_proxy.close()
#!/usr/bin/python
#coding:utf-8
from flask import Flask, url_for, request, make_response, jsonify, render_template
import os
import logging
import json
from werkzeug.security import generate_password_hash, check_password_hash
import db_utils
db = db_utils.get_db()
app = Flask(__name__)
def after_request(response):
response.headers['Access-Control-Allow-Origin'] = '*'
response.headers['Access-Control-Allow-Methods'] = 'PUT,GET,POST,DELETE'
response.headers[
'Access-Control-Allow-Headers'] = 'x-requested-with,content-type'
return response
app.after_request(after_request)
@app.route('/')
def index():
# return "Hello, index"
return render_template('index.html')
import os
from db_utils import database_proxy, get_db, all_table
os.environ['DJANGO_SETTINGS_MODULE'] = 'settings'
try:
from models.projects import Projects
except ImportError:
from models.projects import Projects
large_table = 1000000
large_db = []
for i in Projects.objects.all():
database_proxy.initialize(get_db(db_name='db_' + i.code))
try:
database_proxy.connection()
except Exception as e:
# print(e)
continue # 是有的库已经不存在了，但是记录还在
for table in all_table:
count = table.select().count()
if count > large_table:
# print i.code + ' : ' + table._meta.table_name + ' : ' + str(count)
large_db.append(i.code)
break
database_proxy.close()
