def delete(table, id): # надо как то брать параметр id не из url
if session.get('logined') and session.get(
'role') == 'Administrator': # если зашел как админ
model = mdl.get_class_by_tablename(table)
if request.method == 'GET':
try:
db.session.query(model).filter_by(
id=id).delete() # удаляем по id
db.session.commit()
db.session.remove()
flash('Row deleted')
return redirect(url_for(
'table', tname=table)) # переходим на страницу с таблицой
except sqlalchemy.exc.IntegrityError:
flash('Foreign key constraint violation (ON DELETE RESTRICT)')
return redirect(url_for('table', tname=table))
except Exception as e:
print(e)
abort(500)
elif session.get('logined') and session.get(
'role') == 'User': # если зашел как юзер
flash('You don\'t have permission for this') # юзеру нельзя удалять
return redirect(url_for(
'table', tname=table)) # переходим на страницу с таблицой
else:
flash('You are not logged in') # если не залогирован
return redirect(url_for('login'))
def add(table):
if session.get('logined') and session.get('role') == 'Administrator':
model = mdl.get_class_by_tablename(table)
columns = model.__table__.columns.keys()
try:
if request.method == 'POST':
row = [request.form[str(c)] for c in columns]
obj = model(*row)
db.session.add(obj)
db.session.commit()
db.session.remove()
flash('Row inserted', 'info')
except sqlalchemy.exc.IntegrityError:
flash('Duplicate value violates unique constraint', 'error')
return render_template('add.html',
tname=table,
columns=columns,
role=session['role'])
except Exception as e:
print(e)
flash('Error occured. Check the correctness of input data',
'error')
return render_template('add.html',
tname=table,
columns=columns,
role=session['role'])
elif session.get('logined') and session.get('role') == 'User':
flash('You don\'t have permission for this')
return redirect(url_for('table'), tname=table, role=session['role'])
else:
flash('You are not logged in')
return redirect(url_for('login'))
def query(table_name):
print("verb: %s, table: %s" % (request.method, table_name))
if request.method == "FETCH":
try:
data = request.get_json(force=True)
data = json.loads(data)
print("data: ", data)
print("data-type: ", type(data))
TableClass = models.get_class_by_tablename(table_name)
if TableClass == None:
raise Exception("Table not found: %s" % table_name)
object = dbsession.query(TableClass).filter_by(
**data['where']).all()
data = [object_as_dict(t) for t in object]
return jsonify({
"status": "success",
"verb": request.method,
"data": data
})
except Exception as e:
return jsonify({
"status": "error",
"verb": request.method,
"error": str(e),
})
def modify(table, id):
if session.get('logined') and session.get('role') == 'Administrator':
model = mdl.get_class_by_tablename(
table) # берем нужную модель по имени таблицы
columns = model.__table__.columns.keys() # столбцы
sess = db.session # сессия
exists = db.session.query(
db.session.query(model).filter_by(id=id).exists()).scalar(
) # проверка на существование поля с определенным id
if exists: # если поле существует
obj = sess.query(model).filter_by(
id=id).first() # строка которую мы будем обновлять
if request.method == 'POST':
try:
rows = dict(
(c, request.form[c])
for c in columns) # берем все значения полей с формы
upd = dict() # словарь для обновления
for k in obj.__dict__.keys():
if k != '_sa_instance_state' and k != 'id': # instance и id не берем, потому не нужны
upd[k] = rows[
k] # в словарь для обновления записываем значения с полей
if k == 'status': # особый случай для BOOLEAN
upd[k] = bool(int(rows[k]))
elif k == 'password':
upd[k] = hashlib.sha256(
rows[k].encode('utf8')).hexdigest()
sess.query(model).filter_by(id=id).update(
upd) # обновляем по строку id поля
sess.remove()
flash('Row updated', 'info')
return render_template('edit.html',
tname=table,
columns=columns,
id=obj.id,
row=obj,
role=session['role'])
except Exception as e:
print(e)
flash('Error occured. Check the correctness of input data',
'error')
else: # поле не существует
abort(500)
return render_template('edit.html',
tname=table,
columns=columns,
id=obj.id,
row=obj,
role=session['role'])
elif session.get('logined') and session.get(
'role') == 'User': # если зашел как юзер
flash('You don\'t permission for this'
) # юзеру нельзя редактировать информацию
return redirect(url_for('table'), tname=table)
else:
flash('You are not logged') # если не залогирован
return redirect(url_for('login'))
def render_html(obj): # для сохранения в pdf
try:
data = db.session.execute(queries_[int(obj) - 1])
columns = data.keys()
return render_template('pdf_template.html',
entity=description_[int(obj) - 1],
data=data,
columns=columns)
except ValueError:
model = mdl.get_class_by_tablename(obj)
columns = model.__table__.columns.keys()
data = model.query.order_by(model.id).all()
db.session.commit()
return render_template('pdf_template.html',
entity=obj,
data=data,
columns=columns)
def get_view(view):
if session.get('logined'):
try:
model = mdl.get_class_by_tablename(view)
columns = model.__table__.columns.keys()
data = model.query.order_by(model.id).all()
db.session.remove()
return render_template('views.html',
view=view,
views=mdl.views,
columns=columns,
data=data,
role=session['role'],
view_selected=True)
except Exception as e:
print(e)
abort(500)
else:
flash('You are not logged in')
return redirect(url_for('login'))
def table(tname):
if session.get('logined'):
try:
model = mdl.get_class_by_tablename(tname)
columns = model.__table__.columns.keys()
data = model.query.order_by(model.id).all()
db.session.remove()
return render_template('tables.html',
tname=tname,
tables=sorted(mdl.tables),
columns=columns,
data=data,
role=session['role'],
table_selected=True)
except Exception as e:
print(e)
abort(500)
elif session['role'] == 'User' and (tname == 'Admins' or tname == 'Users'):
flash('You don\'t have permission for this')
return redirect(url_for('tables'))
else:
flash('You are not logged in')
return redirect(url_for('login'))
def fetch(table_name, id):
print("verb: %s, table: %s, id: %s" % (request.method, table_name, id))
if request.method == "GET":
try:
TableClass = models.get_class_by_tablename(table_name)
if TableClass == None:
raise Exception("Table not found: %s" % table_name)
if id == None: #all data
object = dbsession.query(TableClass).all()
data = [object_as_dict(t) for t in object]
else:
object = dbsession.query(TableClass).filter_by(**{
"id": id
}).first()
if object == None: raise Exception("No data found.")
data = object_as_dict(object)
return jsonify({
"status": "success",
"verb": request.method,
"data": data
})
except Exception as e:
return jsonify({
"status": "error",
"verb": request.method,
"error": str(e),
})
elif request.method == "POST" or request.method == "PUT":
data = request.get_json(force=True)
print("data:", data)
try:
TableClass = models.get_class_by_tablename(table_name)
if TableClass == None:
raise Exception("Table not found: %s" % table_name)
if request.method == "POST": #insert data
object = TableClass(**data)
dbsession.add(object)
dbsession.commit()
else: #update data
object = dbsession.query(TableClass).filter_by(**{
"id": id
}).first()
if object == None: raise Exception("No data found.")
#object.update(**data)
for key in data.keys():
setattr(object, key, data[key])
#dbsession.add(object)
dbsession.commit()
return jsonify({
"status": "success",
"verb": request.method,
"id": object.id,
})
except Exception as e:
return jsonify({
"status": "error",
"verb": request.method,
"error": str(e),
})
elif request.method == "DELETE":
try:
TableClass = models.get_class_by_tablename(table_name)
if TableClass == None:
raise Exception("Table not found: %s" % table_name)
object = dbsession.query(TableClass).filter_by(**{
"id": id
}).first()
if object == None: raise Exception("No data found.")
dbsession.delete(object)
dbsession.commit()
return jsonify({
"status": "success",
"verb": request.method,
"id": object.id,
})
except Exception as e:
return jsonify({
"status": "error",
"verb": request.method,
"error": str(e),
})
else:
return jsonify({
"status": "error",
"error": "Unrecognized verb.",
})
def fetch(table_name):
print("verb: %s, tablename: %s" % (request.method, table_name))
if config['auth'] and not is_login_valid():
print("Unauthorized Access.")
return jsonify({
"status": "error",
"error": "Unauthorized Access."
})
if request.method == "GET":
try:
TableClass = models.get_class_by_tablename(table_name)
if TableClass == None: raise Exception("Table not found: %s" % table_name)
if id == None: #all data
object = dbsession.query(TableClass).all()
data = [object_as_dict(t) for t in object]
else:
object = dbsession.query(TableClass).filter_by(**{"id":id}).first()
if object == None: raise Exception("No data found.")
data = object_as_dict(object)
return jsonify({
"status": "success",
"data": data
})
except Exception as e:
return jsonify({
"status": "error",
"error": str(e),
})
elif request.method == "POST" or request.method == "PUT":
data = request.get_json(force=True)
print("data:", data)
try:
TableClass = models.get_class_by_tablename(table_name)
if TableClass == None: raise Exception("Table not found: %s" % table_name)
if request.method == "POST": #insert data
object = TableClass(**data)
dbsession.add(object)
dbsession.commit()
else: #update data
object = dbsession.query(TableClass).filter_by(**{"id":id}).first()
if object == None: raise Exception("No data found.")
#object.update(**data)
for key in data.keys():
setattr(object, key, data[key])
#dbsession.add(object)
dbsession.commit()
return jsonify({
"status": "success",
"id": object.id,
})
except Exception as e:
return jsonify({
"status": "error",
"error": str(e),
})
elif request.method == "DELETE":
try:
TableClass = models.get_class_by_tablename(table_name)
if TableClass == None: raise Exception("Table not found: %s" % table_name)
object = dbsession.query(TableClass).filter_by(**{"id":id}).first()
if object == None: raise Exception("No data found.")
dbsession.delete(object)
dbsession.commit()
return jsonify({
"status": "success",
"id": object.id,
})
except Exception as e:
return jsonify({
"status": "error",
"error": str(e),
})
elif request.method == "FETCH":
try:
data = request.get_json(force=True)
data = json.loads(data)
print("data: ", data)
print("data-type: ", type(data))
TableClass = models.get_class_by_tablename(table_name)
if TableClass == None: raise Exception("Table not found: %s" % table_name)
query = dbsession.query(TableClass).filter_by(**data['where'])
if 'orderby' in data:
for cname in data['orderby'].split(','):
reverse = False
if cname.endswith(' desc'):
reverse = True
cname = cname[:-5]
elif cname.endswith(' asc'):
cname = cname[:-4]
print("cname: ", cname)
column = getattr(TableClass, cname)
if reverse: column = desc(column)
query = query.order_by(column)
if 'limit' in data:
query = query.limit(data['limit'])
query = query.offset(data['offset'])
object = query.all()
data = [object_as_dict(t) for t in object]
return jsonify({
"status": "success",
"data": data
})
except Exception as e:
return jsonify({
"status": "error",
"error": str(e),
})
else:
return jsonify({
"status": "error", "error": "Unrecognized verb.",
})