Reznov Reznov - 3 months ago 48
Python Question

Relationship between two tables, SQLAlchemy

I want to make a relationship between AuthorComments and Reply to his comments.

Here is my models.py:

class AuthorComments(Base):

id = db.Column(db.Integer, primary_key=True)
author_id = db.Column(db.Integer, db.ForeignKey('author.id'))
name = db.Column(db.String(50))
email = db.Column(db.String(50), unique=True)
comment = db.Column(db.Text)
live = db.Column(db.Boolean)

comments = db.relationship('Reply', backref='reply', lazy='joined')

def __init__(self,author, name, email, comment, live=True):

self.author_id = author.id
self.name = name
self.email = email
self.comment = comment
self.live = live

class Reply(Base):

id = db.Column(db.Integer, primary_key=True)
reply_id = db.Column(db.Integer, db.ForeignKey('author.id'))
name = db.Column(db.String(50))
email = db.Column(db.String(50), unique=True)
comment = db.Column(db.Text)
live = db.Column(db.Boolean)

def __init__(self,author, name, email, comment, live=True):

self.reply_id = author.id
self.name = name
self.email = email
self.comment = comment
self.live = live


Why am I getting this error:
sqlalchemy.exc.InvalidRequestError

InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Could not determine join condition between parent/child tables on relationship AuthorComments.comments - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Answer

Your trouble is that SQLAlchemy doesn't know, for a given row of the child table (Reply), which row of the parent table (AuthorComments) to select! You need to define a foreign-key column in Reply that references a column of its parent AuthorComments.

Here is the documentation on defining one-to-many relationships in SQLAlchemy.

Something like this:

class AuthorComments(Base):
    __tablename__ = 'author_comment'
    ...

class Reply(Base):
    ...
    author_comment_id = db.Column(db.Integer, db.ForeignKey('author_comment.id'))
    ...
    author_comment = db.relationship(
        'AuthorComment',
        backref='replies',
        lazy='joined'
        )

will result in each reply acquiring a relationship to an author_comment such that some_reply.author_comment_id == some_author_comment.id, or None if no such equality exists.

The backref allows each author_comment to, reciprocally, have a relationship to a collection of replies called replies, satisfying the above condition.