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))