carambo carambo - 1 year ago 51
Python Question

Query instances related to other instance with SQLAlchemy

I have

models for my application. I was originally using the Peewee ORM, but am switching to SQLAlchemy. I want to select all properties listed by the current user and display them in a template. In Peewee, iterating over the following query worked, but I get a
in SQLAlchemy. How do I get the instances related to another instance in SQLAlchemy?

my_properties = Property.query.filter_by(listed_by=current_user)

{% for property in my_properties %}
{{ property.listing_no }}

class Property(db.Model):
id = db.Column(db.Integer, primary_key=True)
listed_by = db.Column(db.Integer, db.ForeignKey(''))

Answer Source

In Peewee, a foreign key field does not separate the actual key from the thing it represents. In SQLAlchemy, a foreign key column is only the key, a relationship is used to represent the thing it points to.

listed_by is an integer. current_user is a proxy to a User object. SQLAlchemy doesn't know what integer == User means.

Create a relationship to the User object and filter on that.

class Property(db.Model):
    id = db.Column(db.Integer, primary_key=True
    listed_by_id = db.Column(db.ForeignKey(
    listed_by = db.relationship(User, foreign_keys=[listed_by_id])

Technically, you can also filter on the id field by passing the user's id, although it's usually more useful to define the relationship.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download