CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
0xdeface
0xdeface

Reputation: 259

Where is the mistake - adjacency list?

I'm getting error when try self reference

class Sku(Base, ReprDescrIdMixin):
    __tablename__ = 'SC84'
    id = Column("ID", String, primary_key=True)
    parent_code = Column("PARENTID",String, ForeignKey('sku.id'))
    parent = relationship('Sku', foreign_keys='Sku.parent_code')


sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Sku.parent - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Wherefore? this is work http://docs.sqlalchemy.org/en/latest/orm/self_referential.html

Upvotes: 1

Views: 104

Answers (3)

SumanKalyan
SumanKalyan

Reputation: 1761

from sqlalchemy.orm import relationship    
class Sku(Base, ReprDescrIdMixin):
        __tablename__ = 'SC84'
        id = Column("ID", String, primary_key=True)
        parent_code = Column("PARENTID",String, ForeignKey('sku.id'))
        parent = relationship('Sku', lazy="joined", foreign_keys=[parent_code])

Upvotes: 0

iuridiniz
iuridiniz

Reputation: 2433

try this

class Sku(Base, ReprDescrIdMixin):
    __tablename__ = 'SC84'
    id = Column("ID", String, primary_key=True)
    parent_code = Column("PARENTID",String, ForeignKey(id))
    parent = relationship('Sku', foreign_keys=parent_code)

when using ForeignKey(...) you should pass the reference key, there's two ways to do this

  1. By passing a string with table name and key concatenated with . like ForeignKey("table_name.key")
  2. By passing a Column object like ForeignKey(key)

You can only use the method 2 if the key was already defined, in other words, if the ForeignKey(key) comes after key = Column(...)

In my example, I'm using the method 2, I prefer this way in order to code be analyzed by my IDE.

See this link

Upvotes: 1

Iron Fist
Iron Fist

Reputation: 10951

The self referencing should be with table name (__tablename__) not Object name, following is for ONE-TO-MANY relationship:

class Sku(Base, ReprDescrIdMixin):
    __tablename__ = 'SC84'
    id = Column("ID", String, primary_key=True)
    parent_code = Column("PARENTID",String, ForeignKey('SC84.id'))
    # ----------------------------------------------->  ^^^^ __tablename__ here
    children = relationship('Sku')

For MANY-TO-ONE relationship:

class Sku(Base, ReprDescrIdMixin):
    __tablename__ = 'SC84'
    id = Column("ID", String, primary_key=True)
    parent_code = Column("PARENTID",String, ForeignKey('SC84.id'))
    parent = relationship('Sku', remote_side=['id'])

Upvotes: 0

Related Questions