def create(tablename):
json_data = request.get_json()
if json_data is None:
return jsonify({'status_msg': 'Ajax error - no json on server'})
if tablename == 'grave':
fields = ("name", "surname", "title", "full_name_with_title", "date_of_birth",
"date_of_death", "cementary_id", "gps_lon", "gps_lat")
grave = get_db().execute('SELECT id FROM grave WHERE name=? AND surname=?', (json_data['name'], json_data['surname'])).fetchone()
print(str(grave))
if grave is not None:
return jsonify({'status_msg': 'Already exist'})
result = api_create_helper("grave", fields, json_data)
return jsonify(result)
elif tablename == 'cementary':
fields = ("full_name", "full_address", "city")
print(json_data)
cementary = get_db().execute('SELECT id FROM cementary WHERE full_name=? AND city=?', (json_data['full_name'], json_data['city'])).fetchone()
if cementary is not None:
return jsonify({'status_msg': 'Already exist'})
return jsonify(api_create_helper("cementary", fields, json_data))
elif tablename == 'user':
fields = ("username", "email", "password_hash", "admin_permit", "activated")
if "password" in json_data:
json_data['password_hash'] = generate_password_hash(json_data["password"])
return jsonify(api_create_helper('users', fields, json_data))
elif tablename == 'description':
user_id = json_data['id']
data = json_data['description']
path = os.path.join(DEFAULT_RES_PATH, str(user_id) + '.html')
with open(path, 'w') as fi:
fi.write(data)
return jsonify({'status_msg' : 'ok', 'id': user_id})
else:
return jsonify({ "status_msg" : "Unkown resource " + tablename})
def update_cache_cementaries():
cementaries = get_db().execute('SELECT * FROM cementary').fetchall()
cementary_dict = {
cementary['id']: dict(cementary)
for cementary in cementaries
}
return cementary_dict
def load_logged_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
g.admin = None
else:
g.user = get_db().execute("SELECT * FROM user WHERE id = ?", (user_id, )).fetchone()
g.admin = (g.user['admin_permit']) == 1
def remove(param):
if param not in ("cementary", "user", "grave", "description"):
return jsonify(err("Unkown param: {}".format(param)))
if param == "user":
if not g.admin:
return jsonify(err("Tylko admin może usuwać użytkowników. Jeśli chcesz usunąć konto skorzystaj z panelu Moje Konto"))
else:
sql = "DELETE FROM user WHERE id=?;"
db = get_db()
db.execute(sql, request.json.get("id"))
db.commit()
return jsonify({"status_msg" : "OK"})
elif param == "grave":
sql = "DELETE FROM grave WHERE id=?;"
db = get_db()
db.execute(sql, request.json.get("id"))
db.commit()
return jsonify({"status_msg" : "OK"})
def get_graves_from_city(city=None):
graves_in_city = """SELECT
name,
surname,
grave.id as id,
full_name_with_title,
date_of_birth,
date_of_death,
cementary_id,
cementary.full_name as full_name,
cementary.city as city FROM grave
INNER JOIN cementary ON cementary.id = grave.cementary_id"""
if city != None:
graves_in_city += """ WHERE city = ?"""
data = get_db().execute(graves_in_city, (city, )).fetchall()
else:
data = get_db().execute(graves_in_city)
return data
def remove_account():
form = RemoveAccountForm(request.form)
if request.method == "POST":
if form.validate_on_submit():
if g.user["username"] == form.username.data and check_password_hash(g.user["password_hash"], form.password.data):
sql = "DELETE FROM user WHERE id=?;"
db = get_db()
db.execute(sql, g.user["id"])
db.commit()
return redirect(url_for("login"))
else:
return render_template("auth/remove_account.html", error = "Zły login lub hasło")
return render_template("auth/remove_account.html", error = None)
def change_password():
error = None
form = ChangePasswordForm(request.form)
if request.method == "POST":
if form.validate_on_submit():
if check_password_hash(g.user["password_hash"], form.old_password.data):
sql = "UPDATE user SET password_hash=? WHERE id=?;"
db = get_db()
db.execute(sql, generate_password_hash(form.new_password.data), g.user["id"])
db.commit()
session.clear()
return redirect(url_for("auth.login"))
else:
error = "Złe hasło"
return render_template("auth/remove_account.html", error = error)
def register():
db = get_db()
error = None
form = RegisterForm(request.form)
if request.method == 'POST':
if form.validate_on_submit():
user = db.execute('SELECT * FROM user WHERE username = ?', (form.username.data,)).fetchone()
if user is not None:
error = "Użytkownik: {} istnieje".format(form.username.data)
if error is None:
db.execute('INSERT INTO user (username, password_hash, email, admin_permit, activated) VALUES (?, ?, ?, ?, ?)',
(form.username.data, generate_password_hash(form.password.data), form.email.data, False, False))
db.commit()
return redirect(url_for('auth.accept'))
return render_template('auth/register.html', form = form, error = error)
def api_update_helper(target, data, fields):
out = { k : v for k,v in data.items() if k in fields and v != None }
if 'password' in fields and 'password' in data:
out['password_hash'] = generate_password_hash(data['password'])
kv = list(out.items())
keys = list(item[0] for item in kv)
columns = ', '.join([key + ' = ?' for key in keys])
vals = list(item[1] for item in kv)
vals.append(id)
sql = 'UPDATE {} SET {} WHERE id=?'.format(target, columns)
db = get_db()
db.execute(sql, vals)
db.commit()
return {"status_msg" : "ok"}
def login():
form = LoginForm(request.form)
error = None
db = get_db()
if request.method == 'POST':
if form.validate_on_submit():
user = db.execute('SELECT * FROM user WHERE username = ?', (form.username.data, )).fetchone()
if user is None:
error = "Użytkownik {} nie istnieje".format(form.username.data)
else:
if not check_password_hash(user['password_hash'], form.password.data):
error = "Hasło niepoprawne"
if error is None:
session.clear()
session['user_id'] = user['id']
return redirect(url_for('panel.panel'))
else:
error = "Niewłaściwy email lub hasło"
return render_template('auth/login.html', form = form, error = error)
def api_create_helper(target, fields, data):
db = get_db()
# INSERT INTO grave(id, ...) VALUES (id, ...)
sql = "INSERT INTO {}({}) VALUES ({})".format(target, ', '.join(fields), ', '.join(['?']*len(fields)))
print(sql)
try:
placeholders = []
for key in fields:
if key == 'password_hash':
placeholders.append(generate_password_hash(data['password']))
placeholders.append(data[key])
except KeyError as ke:
return {'status_msg' : 'Missing field :: ' + str(ke)}
db.execute(sql, placeholders)
db.commit()
if target == 'grave':
grave = db.execute('SELECT id FROM {} WHERE name=? AND surname=?'.format(target), (data['name'], data['surname'])).fetchone()
return {'status_msg' : 'ok', 'id' : grave['id'] }
return {"status_msg": "ok"}
def get_real_image_href():
if request.method == 'GET':
userid = request.args.get('userid')
result = get_db().select('SELECT img_path FROM image WHERE userid = ?', (userid, )).fetchone()
return
def goto_route():
cities = get_db().execute("SELECT city FROM cementary").fetchall()
city_list = [row['city'] for row in cities]
return render_template("frontend/goto.html", cities=city_list)
{
"id":"tadeusz_cybulko",
"imie":"Tadeusz",
"nazwisko":"Cybulko",
"data_urodzin":"22-04-1929",
"data_smierci":"10-10-2010",
"plec":"m",
"title":"Prof. dr. hab. Tadeusz Cybulko",
"cmentarz": "solacz",
"coords":[52.438866,16.891833]
}
]
}
"""
def main(fp):
with open(fp, 'r') as json_file
content = load(json_file)
db = get_db()
query = "INSERT INTO (name, surname, )"
for person in content['osoby']:
if __name__ == "__main__":
if len(sys.argv) == 1:
print("USAGE: json_to_sql.py file.json")
else:
path = os.path.realpath(sys.argv[1])
if os.path.exists(path):
main(path)
def update_cache_users():
users = get_db().execute('SELECT * FROM user').fetchall()
user_dict = {user['id']: dict(user) for user in users}
return user_dict
def list_resource(tablename):
if tablename not in ('user', 'grave', 'cementary'):
return jsonify({'status_msg' : 'Unkown resource %s' % (tablename, )})
res = get_db().execute('SELECT * FROM {}'.format(tablename)).fetchall()
return jsonify({ row['id'] : dict(row) for row in res })
def update_cache_graves():
graves = get_db().execute('SELECT * FROM grave').fetchall()
grave_dict = {grave['id']: dict(grave) for grave in graves}
return grave_dict
def show(tablename, uid):
if request.method == 'GET':
if tablename not in ('grave', 'cementary', 'user'):
return jsonify({'status_msg' : 'Table: ' + tablename + " is unknown"})
data = get_db().execute('SELECT * FROM {} WHERE id = ?'.format(tablename), (uid, )).fetchone()
return jsonify(dict(data))
