SQLAlchemy hybrid property not working properly

Question

I have a geometry point column for a model, so I want to define the setter to receive lat/lng instead of the created WKTElement. Here is what my model currently looks like:

class Poi(db.Model):
    __tablename__ = 'api_pois'

    id = db.Column(db.Integer, primary_key=True, unique=True)
    name = db.Column(db.String(80), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('api_users.id'))
    creation_time = db.Column(db.DateTime, default=func.now())
    category_id = db.Column(db.Integer(), db.ForeignKey('api_poi_categories.id'))
    category = db.relationship('PoiCategory')
    geom = db.Column(Geometry('POINT', srid=4326), nullable=False)
    metadata_poi = db.Column('metadata_poi')

    def __init__(self, name, lat, lng, user_id=None, category_id=None, metadata_poi=None):
        geom_str = 'POINT({} {})'.format(lng, lat)
        self.name = name
        self.user_id = user_id
        self.category_id = category_id
        self._geom = WKTElement(geom_str, srid=4326)
        self.metadata_poi = metadata_poi

    def __repr__(self):
        return '<Poi %r %r>' % (self.id, self.name)

    def as_dict(self, route_id=None):
        sequence = None

        if route_id:
            from sintrafico.models.route import routes_pois

            result = db.session.query(routes_pois).filter_by(route_id=route_id, poi_id=self.id). \
                order_by(routes_pois.c._sequence).one_or_none()

            if result:
                sequence = result[2]

        geom = db.session.query(Poi, func.ST_X(self.geom), func.ST_Y(self.geom)).filter(Poi.id == self.id).one_or_none()

        return {
            'id': self.id,
            'name': self.name if not sequence else '%s - %s' % (sequence, self.name),
            'lat': geom[2],
            'lon': geom[1],
            'city': str(City.city_for_coord(geom[2], geom[1])),
            'sequence': sequence if sequence else None,
            'category_id': self.category_id,
            'category': self.category.name,
            'metadata': self.metadata_poi
        }

    @property
    def location(self):
        geom = db.session.query(Poi, func.ST_X(self.geom), func.ST_Y(self.geom)).filter(Poi.id == self.id).one_or_none()
        return {'lat': geom[2], 'lon': geom[1]}

    @hybrid_property
    def geom(self):
        return self._geom

    @geom.setter
    def geom(self, (lat, lng)):
        geom_str = 'POINT({} {})'.format(lng, lat)
        self._geom = WKTElement(geom_str, srid=4326)

This code fails on the line geom = ... inside as_dict() which calls the hybrid getter because Poi object doesn't have attribute _geom. Now if I change the line to

geom = db.session.query(Poi, func.ST_X(self._geom), func.ST_Y(self._geom)).filter(Poi.id == self.id).one_or_none()

the error persists, it is just moved to that line instead of the setter. If I change the declaration to

_geom = db.Column(Geometry('POINT', srid=4326), nullable=False)

Then the error I get is a malformed query:

HINT:  Perhaps you meant to reference the column "api_pois.geom".
 [SQL: 'SELECT api_pois.id AS api_pois_id, api_pois.name AS api_pois_name, api_pois.user_id AS api_pois_user_id, api_pois.creation_time AS api_pois_creation_time, api_pois.category_id AS api_pois_category_id, ST_AsBinary(api_pois._geom) AS api_pois__geom, api_pois.metadata_poi AS api_pois_metadata_poi \nFROM api_pois \nWHERE api_pois.user_id = %(user_id_1)s ORDER BY api_pois.creation_time DESC'] [parameters: {'user_id_1': 1}]

In the last line of this code:

if args['q']:
    pois = pois.filter(func.lower(Poi.name).like('%{}%'.format(args['q'].lower())))

if args['categories'] is not None and len(args['categories']) > 0:
    pois = [x for x in pois if x.category_id in args['categories']]

How can I get it to work?

Answer 1

Name your geom column explicitly instead of letting declarative use the attribute name:

class Poi(db.Model):
    ...
    _geom = db.Column('geom', Geometry('POINT', srid=4326), nullable=False)