SQLAlchemy one-to-one relation, primary as foreign key

Question

I am mapping classes to existed MySQL tables generated by Drupal. I need to relate to tables(one-to-one), but I've got a problem. Two tables have the column nid. Both fields are primary keys. I can not define foreign key without a primary key. And relation map is not applied to a primary key. My version is below.

class Node(Base):
    __tablename__ = 'node'
    nid = Column(Integer, primary_key=True)
    vid = Column(Integer)
    uuid = Column(String(128))
    type = Column(String)

    field_data = relationship("NodeFieldData", order_by="NodeFieldData.nid", backref="node")

    def __repr__(self):
        return "<User(nid='%s', vid='%s', uuid='%s', type='%s')>" % (self.nid, self.vid, self.uuid, self.type)

class NodeFieldData(Base):
    __tablename__ = 'node_field_data'
    nid = Column(Integer, primary_key=True)
    type = Column(String, nullable=False)
    title = Column(String, nullable=False)
    #nid = Column(Integer, ForeignKey('Node.nid'))

    def __repr__(self):
        return "<User(nid='%s', vid='%s', uuid='%s', type='%s')>" % (self.nid, self.vid, self.uuid, self.type)

Thank you.

And why cannot you give a different `name` to a column with the `ForeignKey` to `Node.nid`? Something like `node_nid = Column(Integer, ForeignKey('Node.nid'))` maybe? — van, Jun 30 '14 at 04:53
I can not give another name because I use the existing database. I can not change the structure. — Victor Shelepen, Jul 02 '14 at 03:55
name in the SA model will **not** change the name of the column on the database. Still, also on the database you cannot have a table with two columns of the same name. So my question is: what is the name of the column on `node_field_data` which is a `ForeignKey` to a table `node`. — van, Jul 02 '14 at 05:37
So your PK is also a FK. In this case simply do `nid = Column(Integer, ForeignKey('Node.nid'), primary_key=True)`. If your relationship is truly `1-to-1`, then you also should add a paremeter `uselist=False` to its definition. — van, Jul 02 '14 at 06:49

score 30 · Answer 1 · answered Jul 05 '14 at 08:29

According to the comments:

nid = Column(Integer, ForeignKey(Node.nid), primary_key=True)

The whole solution is:

Base = declarative_base()
class Node(Base):
    __tablename__ = 'node'
    nid = Column(Integer, primary_key=True)
    uuid = Column(String(128))
    vid = Column(Integer)
    type = Column(String)

    # Refferer.
    field_data = relationship('NodeFieldData', backref="node", uselist=False)

    def __repr__(self):
        return "<Node(nid='%s', uuid='%s', vid='%s', type='%s', title='%s')>" % (self.nid, self.uuid, self.vid, self.type, self.field_data.title)

class NodeFieldData(Base):
    __tablename__ = 'node_field_data'
    nid = Column(Integer, ForeignKey(Node.nid), primary_key=True)
    #vid = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)

    def __repr__(self):
        return "<Node(nid='%s', title='%s')>" % (self.nid, self.title)

SQLAlchemy one-to-one relation, primary as foreign key

1 Answers1

Linked