foreign_keys and backref are different concepts. foreign_keys is a
hint to SQLAlchemy on how to create the join condition between 2
classes. backref specifies a property that should be created on the
other end of the relationship to allow you to follow the relationship
in the other direction.
For example, if you had this:
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class RelationshipsModel(Base):
__tablename__ = "relationships"
source_node_id = sa.Column(
sa.Integer, sa.ForeignKey("nodes.id"), primary_key=True
)
target_node_id = sa.Column(
sa.Integer, sa.ForeignKey("nodes.id"), primary_key=True
)
strength = sa.Column(sa.Integer, nullable=False)
source_node = saorm.relationship(
"NodesModel", foreign_keys=[source_node_id],
backref="targets",
)
target_node = saorm.relationship(
"NodesModel", foreign_keys=[target_node_id],
backref="sources",
)
class NodesModel(Base):
__tablename__ = "nodes"
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(50), nullable=False)
if __name__ == "__main__":
engine = sa.create_engine("sqlite://", echo="debug")
Base.metadata.create_all(bind=engine)
session = saorm.Session(bind=engine)
node1 = NodesModel(name="node1")
node2 = NodesModel(name="node2")
relationship = RelationshipsModel(
source_node=node1, target_node=node2, strength=10
)
session.add_all([node1, node2, relationship])
session.flush()
print(node1.targets)
Given a node, you can access the relationships which use that node as
a source via the backref "node.targets", and the relationships that
use that node as a target via "node.sources".
Hope that helps,
Simon
On Wed, May 29, 2019 at 11:49 AM Desmond Lim <[email protected]> wrote:
>
> Hi there,
>
> Sorry, I've actually found the solution after I've posted my question again.
>
> But I have to ask.
>
> I'm doing this in my relationships model:
>
> source_node = relationship("NodesModel", foreign_keys=[source_node_id])
> target_node = relationship("NodesModel", foreign_keys=[target_node_id])
>
> And removed this from the nodes model:
>
> relationships = relationship("RelationshipsModel", backref="nodes")
>
> My questions are:
>
> The backref allows the linking of the 2 tables bidirectionally. Does the
> foreign_keys in the relationships model do the same thing?
> Why do we not use the foreign_keys method to link all the tables instead of
> backref in the parent table?
>
> Thanks.
> Desmond
>
>
> On Wed, 29 May 2019 at 18:38, Desmond Lim <[email protected]> wrote:
>>
>> Hi Simon,
>>
>> I've read and I've tried a number of what is written but I still can't solve
>> it.
>>
>> I've done this:
>>
>> class RelationshipsModel(db.Model):
>> __tablename__ = 'relationships'
>>
>> source_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
>> primary_key=True)
>> target_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
>> primary_key=True)
>> strength = db.Column(db.Integer, nullable=False)
>>
>> source_node = relationship("NodesModel")
>> target_node = relationship("NodesModel")
>>
>> class NodesModel(db.Model):
>> __tablename__ = 'nodes'
>>
>> id = db.Column(db.BigInteger, primary_key=True)
>> project_uuid = db.Column(UUID(as_uuid=True),
>> db.ForeignKey('projects.uuid'))
>> name = db.Column(db.String(50), nullable=False)
>> size = db.Column(db.Integer, nullable=False)
>>
>> posts_nodes = relationship("PostsNodesModel", backref="nodes")
>>
>> Below are all that I"ve tried.
>>
>> -----
>>
>> class RelationshipsModel(db.Model):
>> __tablename__ = 'relationships'
>>
>> source_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
>> primary_key=True)
>> target_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
>> primary_key=True)
>> strength = db.Column(db.Integer, nullable=False)
>>
>> source_node = relationship("NodesModel")
>> target_node = relationship("NodesModel")
>>
>> class NodesModel(db.Model):
>> __tablename__ = 'nodes'
>>
>> id = db.Column(db.BigInteger, primary_key=True)
>> project_uuid = db.Column(UUID(as_uuid=True),
>> db.ForeignKey('projects.uuid'))
>> name = db.Column(db.String(50), nullable=False)
>> size = db.Column(db.Integer, nullable=False)
>>
>> posts_nodes = relationship("PostsNodesModel", backref="nodes")
>> relationships_s = relationship("RelationshipsModel",
>> foreign_keys=["relationships.source_node_id"], backref="nodes")
>> relationships_t = relationship("RelationshipsModel",
>> foreign_keys=["relationships.target_node_id"], backref="nodes")
>>
>> -----
>>
>> class RelationshipsModel(db.Model):
>> __tablename__ = 'relationships'
>>
>> source_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
>> primary_key=True)
>> target_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
>> primary_key=True)
>> strength = db.Column(db.Integer, nullable=False)
>>
>> class NodesModel(db.Model):
>> __tablename__ = 'nodes'
>>
>> id = db.Column(db.BigInteger, primary_key=True)
>> project_uuid = db.Column(UUID(as_uuid=True),
>> db.ForeignKey('projects.uuid'))
>> name = db.Column(db.String(50), nullable=False)
>> size = db.Column(db.Integer, nullable=False)
>>
>> posts_nodes = relationship("PostsNodesModel", backref="nodes")
>> relationships_s = relationship("RelationshipsModel",
>> foreign_keys=["relationships.source_node_id"], backref="nodes")
>> relationships_t = relationship("RelationshipsModel",
>> foreign_keys=["relationships.target_node_id"], backref="nodes")
>>
>> -----
>>
>> class RelationshipsModel(db.Model):
>> __tablename__ = 'relationships'
>>
>> source_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
>> primary_key=True)
>> target_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
>> primary_key=True)
>> strength = db.Column(db.Integer, nullable=False)
>>
>> class NodesModel(db.Model):
>> __tablename__ = 'nodes'
>>
>> id = db.Column(db.BigInteger, primary_key=True)
>> project_uuid = db.Column(UUID(as_uuid=True),
>> db.ForeignKey('projects.uuid'))
>> name = db.Column(db.String(50), nullable=False)
>> size = db.Column(db.Integer, nullable=False)
>>
>> posts_nodes = relationship("PostsNodesModel", backref="nodes")
>> relationships_s = relationship("RelationshipsModel",
>> foreign_keys=["relationships.source_node_id"], backref="nodes")
>> relationships_t = relationship("RelationshipsModel",
>> foreign_keys=["relationships.target_node_id"], backref="nodes")
>>
>> I've also tried using
>>
>> relationships = relationship("RelationshipsModel",
>> foreign_keys="[NodesModel.source_node_id,
>> NodesModel.target_node_id]",
>> backref="nodes")
>>
>> Any other codes that I haven't tried?
>>
>> Desmond
>>
>> On Wed, 29 May 2019 at 17:30, Simon King <[email protected]> wrote:
>>>
>>> On Wed, May 29, 2019 at 10:08 AM Desmond Lim <[email protected]> wrote:
>>> >
>>> > Hi there,
>>> >
>>> > I'm been puzzling over this and still can't find answer.
>>> >
>>> > I have 2 tables:
>>> >
>>> > Nodes:
>>> >
>>> > class NodesModel(db.Model):
>>> > __tablename__ = 'nodes'
>>> >
>>> > id = db.Column(db.BigInteger, primary_key=True)
>>> > project_uuid = db.Column(UUID(as_uuid=True),
>>> > db.ForeignKey('projects.uuid'))
>>> > name = db.Column(db.String(50), nullable=False)
>>> > size = db.Column(db.Integer, nullable=False)
>>> >
>>> > posts_nodes = relationship("PostsNodesModel", backref="nodes")
>>> > relationships = relationship("RelationshipsModel", backref="nodes")
>>> >
>>> > Relationships:
>>> >
>>> > class RelationshipsModel(db.Model):
>>> > __tablename__ = 'relationships'
>>> >
>>> > source_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
>>> > primary_key=True)
>>> > target_node_id = db.Column(db.BigInteger, db.ForeignKey('nodes.id'),
>>> > primary_key=True)
>>> > strength = db.Column(db.Integer, nullable=False)
>>> >
>>> > I'm getting errors on this line:
>>> >
>>> > relationships = relationship("RelationshipsModel", backref="nodes")
>>> >
>>> > And I know it is because my Relationships table has the Nodes table as a
>>> > foreign key twice. But I have not idea how do I create 2 relationships to
>>> > the Relationships table?
>>> >
>>>
>>> I assume the error you are getting is something like "Could not
>>> determine join condition..."? In which case, you probably need this
>>> section of the docs:
>>>
>>> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#handling-multiple-join-paths
>>>
>>> Hope that helps,
>>>
>>> Simon
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>> description.
>>> ---
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an
>>> email to [email protected].
>>> To post to this group, send email to [email protected].
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/CAFHwexczSoXe-GCrfDB%2BD6tisADXkz1EBqtjhyMz2La58tL7yw%40mail.gmail.com.
>>> For more options, visit https://groups.google.com/d/optout.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAM%2BCzagzkvG2fwY3VtZNQR_6gBQsUFvmxkr7pBDJAZmgpY1Feg%40mail.gmail.com.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfuvzX3hXAu6dbKCRM%2BhQdKMt2d9c7hLecy2UOu1uUEgg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
