Python Session.query方法代码示例

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def delFavFeed(user_id, feed_id, id):

    session = Session()

    uid = int(current_user.get_id()) if app.config.get('LOGIN_DISABLED') is False else user_id

    u_f = session.query(association_table_user_feed).filter_by(user_id=uid).filter_by(feed_id=feed_id).first()
    if u_f is None:
        session.close()
        raise ServerError("User or feed doesn't exist.")

    uf_fav = session.query(TrUserFeedFav).filter_by(uf_id=u_f.id).filter_by(feed_news_id=id).first()

    if uf_fav is not None:

        try:
            session.delete(uf_fav)
            session.commit()
        except:
            session.rollback()
            raise ServerError("Can't delete item from favorites.")
        finally:
            session.close()
    else:
        session.close()

    return True

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def upload(target):

    session = Session()

    tbl = {'profile': (TrUser, app.config.get('PROFILES_IMG_PATH')),
           'vehicle': (TrVehicle, app.config.get('VEHICLES_IMG_PATH')),
           'group': (TrGroup, app.config.get('GROUPS_IMG_PATH'))}

    if tbl.get(target) is None:
        session.close()
        return ("Error: Incorrect target.")

    obj = session.query(tbl[target][0]).get(int(request.headers.get('id')))
    if obj is None:
        session.close()
        return ("Error: Object doesn't exist.")

    path = tbl[target][1]

    file = request.files['file']

    app.logger.debug('Object id: %d' % int(request.headers.get('id')))
    app.logger.debug('Filename: %s' % file.filename)

    if file and allowed_file(file.filename):

        if obj.pic is not None:
            app.logger.debug('Field pic is not empty.')
            try:
                app.logger.debug('Trying to delete old file.')
                os.remove(path + obj.pic)
            except:
                pass

        engine.execute(" \
            UPDATE " + obj.__tablename__ + " SET pic=concat( \
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(" + str(obj.id) + ")*4294967296))*62+1, 1), \
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*62+1, 1), \
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*62+1, 1), \
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*62+1, 1), \
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*62+1, 1), \
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*62+1, 1), \
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', rand(@seed:=round(rand(@seed)*4294967296))*62+1, 1), \
                  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', rand(@seed)*62+1, 1), \
                  '.jpg' \
                 ) \
            WHERE id="+ str(obj.id) + "; \
        ")

        session.close()

        s = Session()
        ob = session.query(tbl[target][0]).get(int(request.headers.get('id')))
        file.save(os.path.join(path, ob.pic))
        s.close()

        return "Ok"
    else:
        session.close()
        return "Error: incorrect file."

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def getDGeos(user_id, device_id):

    session = Session()

    dev = session.query(TrDevice).filter(TrDevice.device_userID == user_id).all()

    if dev is None:
        raise ServerError("Device doesn't exist.")

    t = session.query(TrDevice.device_ID).filter(TrDevice.device_ID == device_id).\
                                            filter(TrDevice.device_userID == user_id).subquery('t')
    geos = session.query(TrDGeozone).filter(TrDGeozone.device_id == t.c.device_ID)

    lst = []

    for geo in geos:
        ret = {
            "id":       geo.id,
            "name":     geo.name,
            "shape":     geo.shape,
            "radius":   geo.radius,
            "center":   geo.center,
            "color":    geo.color,
            "state":     bool(geo.state)}
        lst.append(ret)

    session.close()

    return lst

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def getFGeos(user_id, follower_id):

    session = Session()

    if session.query(TrUser).get(user_id) is None:
        raise ServerError("User does not exist")

    follower = session.query(TrFollower).filter(TrFollower.id == follower_id).first()

    if follower is None:
        raise ServerError('Follower does not exist')

    lst = []

    for geo in follower.geos:
        lst.append({
            "id": geo.id,
            "name": geo.name,
            "state": geo.state,
            "shape": geo.shape,
            "center": geo.center,
            "radius": geo.radius,
            "color": geo.color
        })

    session.close()

    return lst

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def updateDGeo(user_id=null, id=null, **kwargs):

    session = Session()

    t = session.query(TrDevice.device_ID).filter(TrDevice.device_userID == user_id).subquery('t')
    geo = session.query(TrDGeozone).filter(TrDGeozone.device_id == t.c.device_ID).filter(TrDGeozone.id == id).first()

    if geo is None:
        session.close()
        raise ServerError("Geozone doesn't exist.")

    """check all params"""
    if not isAllIncluded(kwargs.keys(), geo_mandatory_params + geo_option_params):
        session.close()
        raise InvalidParamsError("Incorrect parameters.")

    # for k,v in kwargs.iteritems():
    #     if k not in geo_params:
    #         session.close()
    #         raise ServerError("Incorrect arg: %s" % k)

    for k,v in kwargs.iteritems():
        setattr(geo, k, v)

    try:
        session.merge(geo)
        session.commit()
    except:
        session.rollback()
        raise ServerError("Can't update")
    finally:
        session.close()

    return True

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def getDLocation(user_id, device_id, since):

    session = Session()

    if session.query(TrUser).get(user_id) is None:
        raise ServerError("User does not exist")

    lst = []

    locs = session.query(TrDLocation).join(TrDevice, TrDLocation.device_id == TrDevice.device_ID).\
                            filter(TrDLocation.device_id == device_id).\
                            filter(TrDLocation.creation_time > datetime.datetime.fromtimestamp(since)).\
                            filter(TrDevice.device_userID == user_id).all()

    for loc in locs:
        lst.append({
                    "lat_log": loc.lat_log,
                    "creation_date": int(loc.creation_time.strftime("%s")),
                    "speed": loc.speed,
                    "sensors": {
                        "battery": 10,
                        "accel": True,
                        "temp": -19}
        })

    session.close()

    return lst

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def getDealers(user_id, vehicle_id, location, radius):

    session = Session()

    uid = int(current_user.get_id()) if app.config.get('LOGIN_DISABLED') is False else user_id

    v = session.query(TrVehicle).filter(TrVehicle.user_id == uid).\
        filter(TrVehicle.id == vehicle_id).first()

    if v is None:
        session.close()
        raise ServerError("Vehicle doesn't exist.")

    lat_lon = location.split(',')
    base_point = GeoLocation.from_degrees(float(lat_lon[0]), float(lat_lon[1]))
    sw, ne = base_point.bounding_locations(radius)

    print sw.deg_lat
    print sw.deg_lon
    print ne.deg_lat
    print ne.deg_lon

    ds = session.query(TrDealer).filter(TrDealer.maker == v.car_model.marka_name).\
                                filter(TrDealer.latitude > sw.deg_lat).filter(TrDealer.longitude > sw.deg_lon).\
                                filter(TrDealer.latitude < ne.deg_lat).filter(TrDealer.longitude < ne.deg_lon).all()

    ret = [fillDealerResponse(d) for d in ds]

    session.close()

    return ret

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def delComment(user_id, group_id, id):

    session = Session()

    uid = int(current_user.get_id()) if app.config.get('LOGIN_DISABLED') is False else user_id

    t = session.query(association_table_user_group).filter(association_table_user_group.user_id == uid).\
                                                    filter(association_table_user_group.group_id == group_id).subquery('t')
    m = session.query(TrGroupComment).filter(TrGroupComment.user_group_id == t.c.id).\
                                        filter(TrGroupComment.id == id).first()

    if m is None:
        session.close()
        raise ServerError("Message doesn't exist.")

    try:
        session.delete(m)
        session.commit()
    except:
        session.rollback()
        raise ServerError("Can't delete message.")
    finally:
        session.close()

    return True

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def getFollowers(user_id):
    '''Test user_id: 998'''

    session = Session()

    if session.query(TrUser).get(user_id) is None:
        raise ServerError("User does not exist")

    followers = []

# SELECT *
# FROM tr_follower AS f
#     LEFT JOIN b_user AS u ON u.ID = f.slave_id
# WHERE f.master_id = 998

    t = session.query(TrFollower.slave_id).filter(TrFollower.master_id == user_id).subquery('t')
    query = session.query(TrUser).filter(TrUser.ID == t.c.slave_id)

    for user in query:

        follower = next((x for x in user.masters if x.master_id == user_id), None)
        if follower is None:
            raise ServerError('Internal error')

        followers.append({
                            "id": follower.id,
                            "alias": follower.alias if follower.alias is not None else user.LOGIN,
                            "type": auth_type(user),
                            "login": user.LOGIN,
                            "first_name": "" if user.NAME is None else user.NAME,
                            "last_name": "" if user.LAST_NAME is None else user.LAST_NAME})

    session.close()

    return followers

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def getDevices(user_id):

    session = Session()

    uid = int(current_user.get_id()) if app.config.get('LOGIN_DISABLED') is False else user_id

    t = session.query(TrVehicle.id).filter(TrVehicle.user_id == uid).subquery('t')
    devs = session.query(TrDevice).filter(TrDevice.vehicle_id == t.c.id).all()

    lst = [fillDeviceResponse(dev) for dev in devs]

    session.close()

    return lst

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def getComments(user_id, group_id):

    session = Session()

    uid = int(current_user.get_id()) if app.config.get('LOGIN_DISABLED') is False else user_id

    t = session.query(association_table_user_group).filter(association_table_user_group.user_id == uid).\
                                                    filter(association_table_user_group.group_id == group_id).subquery('t')
    ms = session.query(TrGroupComment).filter(TrGroupComment.user_group_id == t.c.id).all()

    lst = [fillGroupMessageResponse(m, current_user) for m in ms]

    session.close()

    return lst

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def addLocation(user_id, points):

    session = Session()

    if session.query(TrUser).get(user_id) is None:
        raise ServerError("User does not exist")

    if len(points) <= 0:
        raise ServerError("Points is empty.")

    location = []

    for point in points:
        location.append(TrULocation(lat_log=point['location'], accuracy=point['accuracy'],
                creation_time=datetime.datetime.fromtimestamp(int(point['timestamp'])).strftime('%Y-%m-%d %H:%M:%S'),
                battery=point['properties'].get('battery'),
                speed=point['properties'].get('speed'),
                user_id=user_id))

    try:
        session.add_all(location)
        session.commit()
    except:
        session.rollback()
        raise ServerError("Can't add new locations")
    finally:
        session.close()

    return True

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def getCode(number):

    session = Session()

    sms = SmsEpochta()
    code = ''.join(random.choice(string.digits) for _ in range(app.config.get('SMS_CODE_LEN')))

    u = session.query(TrUser).filter(TrUser.login == number).first()

    if u is None:
        u = TrUser(login=number, auth_code=code, active='N')
        session.add(u)
    else:
        setattr(u, 'auth_code', code)
        setattr(u, 'active', 'N')
        session.merge(u)

    try:
        session.commit()
        session.refresh(u)
    except:
        session.rollback()
        raise ServerError("Can't generate new code.")
    finally:
        session.close()

    sms.send(number, code)

    return u.id

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def updatePlace(user_id, place_id, **kwargs):

    session = Session()
    
    uid = int(current_user.get_id()) if app.config.get('LOGIN_DISABLED') is False else user_id

    p = session.query(TrPlace).filter(TrPlace.user_id == uid).filter(TrPlace.id == place_id).first()

    if p is None:
        session.close()
        raise ServerError("Place doesn't exist.")

    """check all params"""
    if not isAllIncluded(kwargs.keys(), place_mandatory_params + place_option_params):
        session.close()
        raise InvalidParamsError("Incorrect parameters.")

    for k,v in kwargs.iteritems():
        setattr(p, k, v)

    try:
        session.merge(p)
        session.commit()
    except:
        session.rollback()
        raise ServerError("Can't update place.")
    finally:
        session.close()

    return True

# 需要导入模块: from TrackerRestApi import Session [as 别名]
# 或者: from TrackerRestApi.Session import query [as 别名]
def getGroups(user_id):

    session = Session()

    uid = int(current_user.get_id()) if app.config.get('LOGIN_DISABLED') is False else user_id

    t = session.query(association_table_user_group).filter_by(user_id=uid).subquery('t')
    gs = session.query(TrGroup).filter(TrGroup.id == t.c.group_id).all()

    lst = [fillGroupResponse(g) for g in gs]

    gsa = session.query(TrGroup).filter(TrGroup.user_id == uid).all()

    lst_admin = [fillGroupResponse(ga, True) for ga in gsa]

    return lst + lst_admin