setup relationship one-to-one in Flask + SQLAlchemy

Question

I'm trying to set up a relationship between two tables which allows me to reach obj1.obj2.name where obj1 is one table, and obj2 is another table. Relationship is one-to-one (one person to one geographical region)

# Table one (Person)
class Person(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(100))
  region = db.Column(db.Integer, db.ForeignKey('region.id'))

# Table two (Region)
class Region(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(50))

If I use Person.region (where Person is an object of Person class) I get the int of the primary key of the region of the user, but I would like to get the 'name' field associated with it.

I've figured out that this would work:

region = models.Region.query.filter_by(id=REGION_ID).first().name

but it's not applicable in my case since I need to access the 'name' field from a Flask template.

Any thoughts?

Answer 1

Here I basically use your model, but: 1) changed the name of the FK column 1) added a relationship (please read Relationship Configuration part of the documentation)

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    # @note: renamed the column, so that can use the name 'region' for
    # relationship
    region_id = db.Column(db.Integer, db.ForeignKey('region.id'))

    # define relationship
    region = db.relationship('Region', backref='people')


class Region(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

With this you are able to get the name of the region as below:

region_name = my_person.region.name  # navigate a 'relationship' and get its 'name' attribute

In order to make sure that the region is loaded from the database at the same time as the person is, you can use joinedload option:

p = (db.session.query(Person)
     .options(db.eagerload(Person.region))
     .get(1)
     )

print(p)
# below will not trigger any more SQL, because `p.region` is already loaded
print(p.region.name)