def api_sensor(): if request.method == 'GET': # Read sensor # GET /api/sensor sensors = g.session.query(db.models.sensor).order_by(db.models.sensor.id).all() return json.dumps([utils.row2dict(sensor) for sensor in sensors]) elif request.method == 'POST': # Create sensor # POST /api/sensor # {name:<string>, df_name:<string>, alias:<string>, unit:<string>, # icon:<string>, bg_color:<string>} df_name = request.json.get('df_name') if not df_name: return 'No df_name' if not request.json.get('name'): return 'No name' new_sensor = db.models.sensor(df_name=df_name, name=request.json.get('name'), alias=request.json.get('alias'), unit=request.json.get('unit'), icon=request.json.get('icon'), bg_color=request.json.get('bg_color')) g.session.add(new_sensor) g.session.commit() db.inject_new_model(df_name.replace('-O', '')) return json.dumps(utils.row2dict(new_sensor)) elif request.method == 'PUT': # Update sensor # PUT /api/sensor # {id:<int>, name:<string>, df_name:<string>, alias:<string>, # unit:<string>, icon:<string>, bg_color:<string>} id_ = request.json.get('id') (g.session .query(db.models.sensor) .filter(db.models.sensor.id == id_) .update(request.json)) g.session.commit() return 'ok' elif request.method == 'DELETE': # Delete sensor # DELETE /api/sensor?id=<id> id_ = request.args.get('id') (g.session .query(db.models.sensor) .filter(db.models.sensor.id == id_) .delete()) g.session.commit() return 'ok' abort(404)
def join_sites_localisation(): """ Réalise une jointure entre la table sites et la table localisation """ # datasets to join basias_sites_prepared = Dataset("etl", "basias_sites_prepared") basias_localisation_with_cadastre = Dataset( "etl", "basias_localisation_with_cadastre") # output dataset basias_sites_localisation_joined = Dataset( "etl", "basias_sites_localisation_joined") # transform types output_dtype = [ *basias_sites_prepared.read_dtype(), *basias_localisation_with_cadastre.read_dtype() ] output_dtype = merge_dtype(output_dtype) basias_sites_localisation_joined.write_dtype(output_dtype) # transform data BasiasSitesPrepared = basias_sites_prepared.reflect() BasiasLocalisation = basias_localisation_with_cadastre.reflect() session = basias_sites_prepared.get_session() join_query = session \ .query(BasiasSitesPrepared, BasiasLocalisation) \ .join(BasiasLocalisation, BasiasSitesPrepared.indice_departemental == BasiasLocalisation.indice_departemental, isouter=True) \ .all() with basias_sites_localisation_joined.get_writer() as writer: for (site, localisation) in join_query: output_row = {c.name: None for c in output_dtype} output_row = {**output_row, **row2dict(site)} if localisation: output_row = {**output_row, **row2dict(localisation)} del output_row["id"] writer.write_row_dict(output_row) session.close()
def login(): """ verifies user payment_id and logs them in for editing """ real = db.session.query( User.id).filter_by(payment_id=request.form["payment_id"]).scalar() paid = db.session.query( Payment.id).filter_by(payment_id=request.form["payment_id"]).scalar() if real is not None and paid is not None: resp = redirect(url_for("edit")) resp.set_cookie('sessiontoken', request.form["payment_id"]) return json.dumps({ 'status': 'Success', 'data': 'Login succeeded' }), 200 if real is None: return json.dumps({ 'status': 'Fail', 'reason': "Payment ID doesn't exist!" }), 500 if paid is None: user = row2dict( User.query.filter( payment_id == request.form["payment_id"]).first()) return json.dumps({ 'status': 'Fail', 'reason': "Payment ID hasn't been paid", 'price': user["price"] }), 500
def read_rate(CID=None, GID=None, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') if CID == None and GID == None: stmt = sql.select([rate]).\ select_from(rate).\ order_by(rate.c.CID, rate.c.GID) elif CID != None and GID == None: stmt = sql.select([rate]).\ select_from(rate).\ where(rate.c.CID == CID).\ order_by(rate.c.CID, rate.c.GID) elif CID == None and GID != None: stmt = sql.select([rate]).\ select_from(rate).\ where(rate.c.GID == GID).\ order_by(rate.c.CID, rate.c.GID) rets = [] for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) rets.append(row_data) if conn_ is None: _conn.close() return rets
def add_or_modify_vote(): try: data = request.get_json() param = { 'post_id': data['post_id'], 'user_id': data['user_id'], 'value': data['value'] } except BadRequestKeyError: return make_response(jsonify({"code": 400, "msg": "Request is missing fields"}), 400) vote = Vote.query.filter_by(post_id=param['post_id'], user_id=param['user_id']).first() if vote: try: value = int(param['value']) vote.value = value except TypeError: return make_response(jsonify({'code': 400, 'msg': 'Value provided is not an integer'})) else: vote = Vote(**param) db.session.add(vote) try: db.session.commit() except sqlalchemy.exc.SQLAlchemyError as e: return make_response(jsonify({"code": 404, "msg": e}), 404) return jsonify(row2dict(vote))
def add_a_good(rows_, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') old_rows = [] dd = rows_diff_by_seq(seq(old_rows), seq(rows_), old_rows, rows_, incl=['NAME'], ind=['NAME'], rplc=False, fillback=False) rows = db_utils.db_operate_dict(_conn, goods, dd, pk=['ID']) # get good id stmt = sql.select([sqlite_sequence.c.seq]).\ select_from(sqlite_sequence).\ where(sqlite_sequence.c.name == 'GOODS') row_data = None for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) if conn_ is None: _conn.close() return row_data['seq'] if conn_ is None: _conn.close() return goods_dict
def render_demo_template(*args, **argv): target_field = argv.get('field') if not target_field: abort(404) field_record = (g.session .query(db.models.field) .filter(db.models.field.name == target_field) .first()) if not field_record: abort(404) sensors = [] sensor_records = (g.session .query(db.models.field_sensor) .filter(db.models.field_sensor.field == field_record.id) .order_by(db.models.field_sensor.id) .all()) for sensor in sensor_records: sensors.append(utils.row2dict(sensor)) return flast_render_template(*args, fieldname=target_field, sensors=sensors, **argv)
def join_cadastre(): """ Join the table basol_cadastre with cadastre table """ # Input datasets basol_cadastre = Dataset("etl", "basol_cadastre") cadastre = Dataset("etl", "cadastre") # Output datasets basol_cadastre_joined = Dataset("etl", "basol_cadastre_joined") basol_cadastre_dtype = basol_cadastre.read_dtype() dtype = [*basol_cadastre_dtype, Column("geog", Geometry(srid=4326))] basol_cadastre_joined.write_dtype(dtype) BasolCadastre = basol_cadastre.reflect() Cadastre = cadastre.reflect() session = basol_cadastre.get_session() cond = (BasolCadastre.commune == Cadastre.commune) & \ (BasolCadastre.section == Cadastre.section) & \ (BasolCadastre.numero == Cadastre.numero) q = session.query(BasolCadastre, Cadastre.geog) \ .join(Cadastre, cond) \ .yield_per(500) with basol_cadastre_joined.get_writer() as writer: for (basol, geog) in q: output_row = {**row2dict(basol), "geog": geog} output_row.pop("id") writer.write_row_dict(output_row) session.close()
def add_parcels(): """ join table basol_intersected with basol_cadastre_merged """ # input datasets basol_intersected = Dataset("etl", "basol_intersected") basol_cadastre_merged = Dataset("etl", "basol_cadastre_merged") # output datasets basol_with_parcels = Dataset("etl", "basol_with_parcels") BasolIntersected = basol_intersected.reflect() BasolCadastreMerged = basol_cadastre_merged.reflect() dtype = basol_intersected.read_dtype() basol_with_parcels.write_dtype(dtype) session = basol_intersected.get_session() cond = (BasolIntersected.numerobasol == BasolCadastreMerged.numerobasol) q = session.query(BasolIntersected, BasolCadastreMerged.geog) \ .join(BasolCadastreMerged, cond, isouter=True) \ .all() with basol_with_parcels.get_writer() as writer: for (row, geog) in q: if geog is not None: row.geog_precision = precisions.PARCEL row.geog_source = "cadastre" row.geog = geog writer.write_row_dict(row2dict(row)) session.close()
def put_new_post(): data = request.get_json() try: params = { 'author_id': data['user_id'], 'url_one': data['url_one'], 'url_two': data['url_two'], 'title': data['title'] } except: return make_response( jsonify({ 'code': 400, 'msg': 'Badly formed request, parameters are missing' }), 400) p = Post(**params) db.session.add(p) try: db.session.commit() except exc.IntegrityError: error = 'wrong parameters sent\n' return make_response(jsonify({"code": 400, "error": error}), 400) return make_response(jsonify({"code": 200, "post": row2dict(p)}), 200)
def merge_geog(): """ Choose best precision between initial coordinates or geocoded coordinates if geog is not set from cadastre information """ # Input dataset basol_geocoded = Dataset("etl", "basol_normalized") # Output dataset basol_geog_merged = Dataset("etl", "basol_geog_merged") basol_geog_merged.write_dtype([ *basol_geocoded.read_dtype(), Column("geog", Geometry(srid=4326)), Column("geog_precision", String), Column("geog_source", String) ]) BasolGeocoded = basol_geocoded.reflect() session = basol_geocoded.get_session() point_lambert2 = func.ST_Transform( func.ST_setSRID( func.ST_MakePoint(BasolGeocoded.coordxlambertii, BasolGeocoded.coordylambertii), LAMBERT2), WGS84) point_geocoded = func.ST_setSRID( func.ST_MakePoint(BasolGeocoded.geocoded_longitude, BasolGeocoded.geocoded_latitude), WGS84) q = session.query(BasolGeocoded, point_lambert2, point_geocoded).all() with basol_geog_merged.get_writer() as writer: for (row, point_lambert2, point_geocoded) in q: output_row = { **row2dict(row), "geog": None, "geog_precision": None, "geog_source": None } if row.l2e_precision == precisions.HOUSENUMBER: output_row["geog"] = point_lambert2 output_row["geog_precision"] = row.l2e_precision output_row["geog_source"] = "lambert2" elif (row.geocoded_result_type == precisions.HOUSENUMBER) and \ (row.geocoded_result_score >= 0.6): output_row["geog"] = point_geocoded output_row["geog_precision"] = row.geocoded_result_type output_row["geog_source"] = "geocodage" writer.write_row_dict(output_row) session.close()
def userpage(user_url): """ renders the shareable URL, checks if the page has been paid for """ exists = db.session.query(User.id).filter_by(url=user_url).scalar() if exists is not None: user = row2dict(User.query.filter(User.url == user_url).first()) paid = db.session.query( Payment.id).filter_by(payment_id=user["payment_id"]).scalar() user.pop('payment_id', None) if paid is not None: return render_template("user.html", user=user) return render_template("waiting.html", user=user) return abort(404)
def edit_page(user_url): """ renders the user edit page """ pay_id = request.cookies.get('sessiontoken') if pay_id is not None: exists = User.query.filter_by(payment_id=pay_id).exists().scalar() if exists is not None: user = row2dict(User.query.filter(payment_id == pay_id).first()) return render_template("edit.html", user=user) return json.dumps({ 'status': 'Fail', 'reason': 'Invalid user authentication' }), 500 return render_template("login.html")
def read_goods(conn_=None): _conn = db_utils.getconnection(conn_, db='rs') stmt = sql.select([goods]).\ select_from(goods) rets = [] for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) rets.append(row_data) if conn_ is None: _conn.close() return rets
def add_geog(): """ Cette recette réalise une jointure avec la table cadastre pour nettoyer les parcelles invalide. On utilise la table cadastre du schéma kelrisks et non la table du schéma etl car il n'y a pas assez de stockage sur le serveur pour avoir 4 tables cadastre (preprod `etl`, preprod `kelrisks`, prod `etl`, prod `kelrisks`). On est donc obligé de supprimer les tables cadastres du schéma `etl` après les avoir copié dans le schéma `kelrisks`. La table `etl.cadastre` n'existe donc pas forcément au moment où ce DAG est executé. """ # Input dataset basias_cadastre_parsed = Dataset("etl", "basias_cadastre_parsed") cadastre = Dataset("kelrisks", "cadastre") # Output dataset basias_cadastre_with_geog = Dataset("etl", "basias_cadastre_with_geog") BasiasCadastreParsed = basias_cadastre_parsed.reflect() Cadastre = cadastre.reflect() basias_cadastre_with_geog.write_dtype([ *basias_cadastre_parsed.read_dtype(), Column("geog", Geometry(srid=4326)) ]) session = basias_cadastre_parsed.get_session() cond = (BasiasCadastreParsed.commune == Cadastre.commune) & \ (BasiasCadastreParsed.section == Cadastre.section) & \ (BasiasCadastreParsed.numero == Cadastre.numero) q = session.query(BasiasCadastreParsed, Cadastre.geog) \ .join(Cadastre, cond) \ .yield_per(500) with basias_cadastre_with_geog.get_writer() as writer: for (row, geog) in q: output_row = {**row2dict(row), "geog": None} del output_row["id"] if geog is not None: output_row["geog"] = geog writer.write_row_dict(output_row) session.close()
def GET(self): user = self.valid() if not user: self.redirect('/login') #words = web.ctx.orm.query(WordsBook).filter(WordsBook.userid == user.userid).order_by(desc(WordsBook.date)).offerset((page-1)*15).limit(15).all() query = web.ctx.query args = url_query_parser(query) page = int(args['page']) per_page_count = 10 words_count = web.ctx.orm.query(func.count('*')).filter(WordsBook.userid == user.userid).scalar() all_page = words_count //per_page_count if words_count % per_page_count > 0: all_page = all_page +1 def has_prev_page(curr_page): #using int() if int(curr_page)>1: return True else: return False def has_next_page(curr_page,all_page): if int(curr_page) < all_page : return True else: return False pageval = get_pageval(page,per_page_count) words = web.ctx.orm.query(WordsBook).filter(WordsBook.userid == user.userid).order_by(desc(WordsBook.date)).limit(per_page_count).offset(pageval).all() word_list = [] for word in words: word = row2dict(word) word['date'] = time.mktime(word['date'].timetuple()) word_list.append(word) pager = { 'current_page': page, 'all_page':all_page, 'has_prev_page': has_prev_page(page), 'has_next_page': has_next_page(page, all_page) } return json.dumps({ 'pager':pager, 'words':word_list })
def get_post_by_id(post_id: int): post = Post.query.filter_by(id=post_id).first() json_post = row2dict(post) user_id = convert_value_or_none(request.args.get('user_id')) if user_id is not None: add_votes_to_post_json_list([json_post], user_id) if post: return jsonify({'code': 200, 'post': json_post}) else: return make_response( jsonify({ 'code': 404, 'msg': 'Cannot find this post.' }), 404)
def update_vote(post_id, user_id): vote = Vote.query.filter_by(post_id=post_id, user_id=user_id).first() if vote: if 'value' in request.form: vote.value = request.form['value'] try: db.session.commit() except sqlalchemy.exc.SQLAlchemyError as e: error = "Cannot update vote.\n" print(app.config.get("DEBUG")) if app.config.get("DEBUG"): error += str(e) return make_response(jsonify({"code": 400, "msg": error}), 404) return jsonify(row2dict(vote)) else: return make_response(jsonify({"code": 404, "msg": "Cannot find this vote."}), 404)
def add_commune(): """ Ajoute le contour des communes comme nouvelle valeur pour geog dans le cas où la précision est MUNICIPALITY La reqête SqlAlchemy est équivalente à SELECT * FROM etl.basias_sites_localisation_joined A LEFT JOIN etl.commune B ON A.numero_insee == B.insee """ # input dataset basias_sites_localisation_joined = Dataset( "etl", "basias_sites_localisation_joined") communes = Dataset("etl", "commune") # output dataset basias_sites_with_commune = Dataset("etl", "basias_sites_with_commune") dtype = basias_sites_localisation_joined.read_dtype() basias_sites_with_commune.write_dtype(dtype) BasiasSites = basias_sites_localisation_joined.reflect() Commune = communes.reflect() session = basias_sites_localisation_joined.get_session() q = session.query(BasiasSites, Commune.geog) \ .join(Commune, BasiasSites.numero_insee == Commune.insee, isouter=True) \ .all() with basias_sites_with_commune.get_writer() as writer: for (row, commune) in q: if row.geog is None: row.geog = commune row.geog_precision = precisions.MUNICIPALITY row.geog_source = "numero_insee" writer.write_row_dict(row2dict(row)) session.close()
def add_commune(): """ Ajoute le contour des communes comme nouvelle valeur pour geog dans le cas où la précision est MUNICIPALITY La reqête SqlAlchemy est équivalente à SELECT * FROM etl.s3ic_with_parcelle A LEFT JOIN etl.commune B ON A.cd_insee == B.insee """ # input dataset s3ic_with_parcelle = Dataset("etl", "s3ic_with_parcelle") communes = Dataset("etl", "commune") # output dataset s3ic_with_commune = Dataset("etl", "s3ic_with_commune") dtype = s3ic_with_parcelle.read_dtype() s3ic_with_commune.write_dtype(dtype) S3icWithParcelle = s3ic_with_parcelle.reflect() Commune = communes.reflect() session = s3ic_with_parcelle.get_session() q = session.query(S3icWithParcelle, Commune.geog) \ .join(Commune, S3icWithParcelle.code_insee == Commune.insee, isouter=True) \ .all() with s3ic_with_commune.get_writer() as writer: for (row, commune) in q: if row.geog_precision == precisions.MUNICIPALITY: row.geog = commune row.precision = precisions.MUNICIPALITY row.geog_source = "code_insee" writer.write_row_dict(row2dict(row)) session.close()
def read_customer(cus_id=None, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') if not cus_id: stmt = sql.select([customer]).\ select_from(customer) else: stmt = sql.select([customer]).\ select_from(customer).\ where(customer.c.ID == cus_id) rets = [] for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) rets.append(row_data) if conn_ is None: _conn.close() return rets
def render_template(*args, **argv): fields = [] query = (g.session .query(db.models.field.id, db.models.field.name, db.models.field.alias, db.models.field.iframe, db.models.user_access.is_active) .select_from(db.models.field) .join(db.models.user_access) .join(db.models.user) .filter(db.models.user.username == session.get('username')) .order_by(db.models.user_access.id) .all()) for id, name, alias, iframe, is_active in query: field_data = { 'id': id, 'name': name, 'alias': alias, 'iframe': iframe.replace('{username}', session.get('username')), 'is_active': 1 & is_active, 'sensors': [] } for sensor in (g.session .query(db.models.field_sensor) .filter(db.models.field_sensor.field == id) .order_by(db.models.field_sensor.id) .all()): field_data['sensors'].append(utils.row2dict(sensor)) fields.append(field_data) user = (g.session .query(db.models.user) .filter(db.models.user.username == session.get('username')) .first()) return flast_render_template(*args, fields=fields, username=session.get('username'), is_superuser=session.get('is_superuser'), memo=user.memo, **argv)
def update_all_pred_rate(rows_, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') stmt = sql.select([rate]).\ select_from(rate).\ where(rate.c.REAL == 0) old_rows = [] for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) old_rows.append(row_data) dd = rows_diff_by_seq(seq(old_rows), seq(rows_), old_rows, rows_, incl=['CID','GID','RATE', 'REAL'], ind=['CID','GID'], rplc=False, fillback=False) rows = db_utils.db_operate_dict(_conn, rate, dd, pk=['CID','GID']) if conn_ is None: _conn.close() return rows
def intersect(): """ Find the closest parcelle to the point and set it as new geography """ # Input dataset basol_geog_merged = Dataset("etl", "basol_geog_merged") cadastre = Dataset("etl", "cadastre") # Output dataset basol_intersected = Dataset("etl", "basol_intersected") dtype = basol_geog_merged.read_dtype() basol_intersected.write_dtype(dtype) Cadastre = cadastre.reflect() BasolGeogMerged = basol_geog_merged.reflect() session = basol_geog_merged.get_session() stmt = session.query(Cadastre.geog) \ .filter(func.st_dwithin( Cadastre.geog, BasolGeogMerged.geog, 0.0001)) \ .order_by(func.st_distance( Cadastre.geog, BasolGeogMerged.geog)) \ .limit(1) \ .label("nearest") q = session.query(BasolGeogMerged, stmt).all() with basol_intersected.get_writer() as writer: for (row, cadastre_geog) in q: if cadastre_geog is not None: row.geog = cadastre_geog writer.write_row_dict(row2dict(row)) session.close()
def join_localisation_cadastre(): """ Réalise une jointure entre la table localisation et la table cadastre """ # Input datasets basias_localisation_intersected = Dataset( "etl", "basias_localisation_intersected") basias_cadastre_merged = Dataset("etl", "basias_cadastre_merged") # Output dataset basias_localisation_with_cadastre = Dataset( "etl", "basias_localisation_with_cadastre") basias_localisation_with_cadastre.write_dtype( basias_localisation_intersected.read_dtype()) BasiasLocalisationIntersected = basias_localisation_intersected.reflect() BasiasCadastreMerged = basias_cadastre_merged.reflect() session = basias_localisation_intersected.get_session() cond = BasiasCadastreMerged.indice_departemental \ == BasiasLocalisationIntersected.indice_departemental q = session.query( BasiasLocalisationIntersected, BasiasCadastreMerged.geog) \ .join(BasiasCadastreMerged, cond, isouter=True) \ .all() with basias_localisation_with_cadastre.get_writer() as writer: for (row, cadastre_geog) in q: if cadastre_geog is not None: # replace geog with cadastre geog row.geog = cadastre_geog row.geog_precision = precisions.PARCEL row.geog_source = "cadastre" writer.write_row_dict(row2dict(row))
def add_communes(): """ set commune geog for all records where geog is not set with a better precision """ # input dataset basol_with_parcels = Dataset("etl", "basol_with_parcels") communes = Dataset("etl", "commune") # output dataset basol_with_communes = Dataset("etl", "basol_with_commune") dtype = basol_with_parcels.read_dtype() basol_with_communes.write_dtype(dtype) BasolWithParcels = basol_with_parcels.reflect() Commune = communes.reflect() session = basol_with_parcels.get_session() q = session.query(BasolWithParcels, Commune.geog) \ .join(Commune, BasolWithParcels.code_insee == Commune.insee, isouter=True) \ .all() with basol_with_communes.get_writer() as writer: for (row, commune) in q: if row.geog is None: row.geog = commune row.geog_precision = precisions.MUNICIPALITY row.geog_source = "code_insee" writer.write_row_dict(row2dict(row)) session.close()
def post_payment(): """ processes form input for url registration """ content = request.form exists = db.session.query( User.id).filter_by(url=content["url"]).scalar() is not None if exists: row = User.query.filter(User.url == content["url"]).first() result = row2dict(row) return json.dumps({ 'status': 'Fail', 'reason': 'This URL has already been registered', 'data': result['address'] }), 500 else: pay_id = get_payment_id() try: user = User(address=content["address"], payment_id=pay_id, url=content["url"], message=content["message"], turtlehash=content["hash"], price=get_price(content["url"])) db.session.add(user) db.session.commit() except Exception as e: app.logger.info(traceback.format_exc()) return json.dumps({ 'status': 'Fail', 'reason': 'Your transaction could not be processed' }), 500 return json.dumps({ 'status': 'Success', 'payment_id': pay_id, 'price': get_price(content["url"]) }), 200
def get_all_post(): page_size = convert_value_or_none(request.args.get('post_per_page')) page = convert_value_or_none(request.args.get('page')) user_id = convert_value_or_none(request.args.get('user_id')) full_json_posts = [row2dict(p) for p in Post.query.order_by(desc(Post.id))] if page is None or page_size is None: add_votes_to_post_json_list(full_json_posts, user_id) return jsonify({'code': 200, 'posts': full_json_posts}) try: """ In this line we do the pagination. range(0, posts.count() will produce a list from 0 to posts.count() by increment of page_size. We then use those numbers to select the page of posts we want """ posts = [ full_json_posts[i:i + page_size] for i in range(0, len(full_json_posts), page_size) ] posts = posts[page] add_votes_to_post_json_list(posts, user_id) return jsonify({'code': 200, 'posts': posts}) except IndexError: return jsonify({'code': 200, 'end': True})
def add_a_customer(rows_, cus_id=None, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') if cus_id: old_rows = read_customer(cus_id=cus_id,conn_=_conn) else: old_rows = [] dd = rows_diff_by_seq(seq(old_rows), seq(rows_), old_rows, rows_, incl=['ID','NAME'], ind=['ID'], rplc=False, fillback=False) rows = db_utils.db_operate_dict(_conn, customer, dd, pk=['ID']) # get cus id stmt = sql.select([sqlite_sequence.c.seq]).\ select_from(sqlite_sequence).\ where(sqlite_sequence.c.name == 'CUSTOMER') row_data = None for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) if conn_ is None: _conn.close() return row_data['seq'] if cus_id == None else cus_id
def stmt_to_dct(stmt_, conn_): rets = [] for row in conn_.execute(stmt_): row_data = db_utils.row2dict(row, has_none=True) rets.append(row_data) return rets
def api_field(): if request.method == 'GET': fields = [] query_fields = (g.session .query(db.models.field) .order_by(db.models.field.id) .all()) for field in query_fields: temp_field = utils.row2dict(field) query_field_sensor = (g.session .query(db.models.sensor.name, db.models.field_sensor.sensor, db.models.field_sensor.df_name, db.models.field_sensor.alias, db.models.field_sensor.unit, db.models.field_sensor.icon, db.models.field_sensor.bg_color, db.models.field_sensor.alert_min, db.models.field_sensor.alert_max) .select_from(db.models.field_sensor) .join(db.models.sensor) .filter(db.models.field_sensor.field == field.id) .order_by(db.models.field_sensor.id) .all()) temp_field['sensors'] = [] for sensor in query_field_sensor: temp_sensor = { 'name': sensor.name, 'sensor': sensor.sensor, 'df_name': sensor.df_name, 'alias': sensor.alias, 'unit': sensor.unit, 'icon': sensor.icon, 'bg_color': sensor.bg_color, 'alert_min': sensor.alert_min, 'alert_max': sensor.alert_max, } temp_field['sensors'].append(temp_sensor) fields.append(temp_field) return json.dumps(fields) elif request.method == 'POST': # Create field # POST /api/field # {name:<string>, alias:<string>, sensors: [<sensor>, ...]} if not request.json.get('name'): return 'No field name' new_field = db.models.field(name=request.json.get('name'), alias=request.json.get('alias'), iframe=request.json.get('iframe', '')) g.session.add(new_field) g.session.commit() for sensor in request.json.get('sensors', []): new_sensor = db.models.field_sensor( field=new_field.id, sensor=sensor.get('sensor'), df_name=sensor.get('df_name'), alias=sensor.get('alias'), unit=sensor.get('unit'), icon=sensor.get('icon'), bg_color=sensor.get('bg_color'), alert_min=sensor.get('alert_min'), alert_max=sensor.get('alert_max')) g.session.add(new_sensor) g.session.commit() return json.dumps(utils.row2dict(new_field)) elif request.method == 'PUT': # Update field # PUT /api/field # {name:<string>, alias:<string>, sensors: [<sensor>, ...]} print(request.json) id_ = request.json.get('id') name = request.json.get('name') alias = request.json.get('alias') iframe = request.json.get('iframe', '') sensors = request.json.get('sensors', []) (g.session .query(db.models.field) .filter(db.models.field.id == id_) .update({'name': name, 'alias': alias, 'iframe': iframe})) (g.session .query(db.models.field_sensor) .filter(db.models.field_sensor.field == id_) .delete()) for sensor in sensors: df_name = sensor.get('df_name') db.inject_new_model(df_name.replace('-O', '')) new_sensor = db.models.field_sensor( field=id_, sensor=sensor.get('sensor'), df_name=sensor.get('df_name'), alias=sensor.get('alias'), unit=sensor.get('unit'), icon=sensor.get('icon'), bg_color=sensor.get('bg_color'), alert_min=sensor.get('alert_min'), alert_max=sensor.get('alert_max')) new_sensor.field = id_ new_sensor.id = None g.session.add(new_sensor) g.session.commit() g.session.commit() return 'ok' elif request.method == 'DELETE': # Delete field # DELETE /api/field?id=<id> id_ = request.args.get('id') for attr in db.models.__dict__.values(): if inspect.isclass(attr) and hasattr(attr, 'timestamp'): (g.session .query(attr) .filter(attr.field == id_) .delete()) (g.session .query(db.models.field_sensor) .filter(db.models.field_sensor.field == id_) .delete()) (g.session .query(db.models.user_access) .filter(db.models.user_access.field == id_) .delete()) (g.session .query(db.models.field) .filter(db.models.field.id == id_) .delete()) g.session.commit() return 'ok' abort(404)
def api_user(): if request.method == 'GET': # Read user # GET /api/user[?id=<id>&username=<username>] users = [] query = g.session.query(db.models.user) for key, value in request.args.items(): attr = getattr(db.models.user, key, None) if attr: query = query.filter(attr == value) query = query.order_by(db.models.user.id).all() for user in query: query_access = (g.session .query(db.models.field, db.models.user_access) .select_from(db.models.user_access) .join(db.models.field) .filter(db.models.user_access.user == user.id) .order_by(db.models.user_access.id) .all()) access = [] active = None for field, acc in query_access: access.append(utils.row2dict(field)) if acc.is_active: active = acc.field users.append({ 'id': user.id, 'username': user.username, 'is_superuser': user.is_superuser, 'access': access, 'active': active }) return json.dumps(users) elif request.method == 'POST': # Create user # POST /api/user # {username:<username>, password:<password>, is_superuser:<is_superuser>} username = request.json.get('username') password = generate_password_hash(request.json.get('password')) is_superuser = request.json.get('is_superuser') access = request.json.get('access', []) active = request.json.get('active') new_user = db.models.user(username=username, password=password, is_superuser=is_superuser) g.session.add(new_user) g.session.commit() for field in access: new_access = db.models.user_access(user=new_user.id, field=field.get('id')) if field.get('id') == active: new_access.is_active = True g.session.add(new_access) g.session.commit() return json.dumps(utils.row2dict(new_user)) elif request.method == 'PUT': # Update user # PUT /api/user # {id:<id>, username:<username>, is_superuser:<is_superuser>} id_ = request.json.get('id') username = request.json.get('username') is_superuser = request.json.get('is_superuser') access = request.json.get('access', []) active = request.json.get('active') (g.session .query(db.models.user) .filter(db.models.user.id == id_) .update({'username': username, 'is_superuser': is_superuser})) (g.session .query(db.models.user_access) .filter(db.models.user_access.user == id_) .delete()) for field in access: new_access = db.models.user_access(user=id_, field=field.get('id')) if field.get('id') == active: new_access.is_active = True g.session.add(new_access) g.session.commit() return 'ok' elif request.method == 'DELETE': # Delete user # DELETE /api/user?id=<id> id_ = request.args.get('id') (g.session .query(db.models.user_access) .filter(db.models.user_access.user == id_) .delete()) (g.session .query(db.models.user) .filter(db.models.user.id == id_) .delete()) g.session.commit() return 'ok' abort(404)