Access SQLAlchemy model linked with foreign key

Question

I have a model with a foreign key to another model. I query the first model, and want to access the model related to it. In Django I can access the model from the foreign key. How do I do this in SQLAlchemy?

class Model(Base):
    field_id = Column(Integer, ForeignKey('Model2.id'))

class Model2(Base):
    id = Column(Integer)
    needed_field = Column(Integer)

models = Model.query.all()
return render to template('templ.html', models=models)

Django works like this:

models = Model.objects.all()
model.field_id.needed_field # loop in template

Answer 1

In Django, defining the foreign key defines the column as well as the relationship to the other model. In SQLAlchemy, you need to define both manually. See the docs about defining relationships.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
db = SQLAlchemy(app)

class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    group_id = db.Column(db.ForeignKey(Group.id))
    group = db.relationship(Group, backref='users')

db.create_all()
db.session.add(User(group=Group()))
db.session.commit()
u = User.query.get(1)
print(u.group)

User.group_id is the foreign key pairing a User with a Group. User.group is the relationship (and Group.users is the relationship in the other direction). Usually you set and read the relationship, not the foreign key.

Answer 2

Below example can help you

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", back_populates="children")

Now You can access parent child from parent_obj.children

For more details http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-one