Home > Software design >  SQLalchemy: Could not determine join condition between parent/child tables on relationship
SQLalchemy: Could not determine join condition between parent/child tables on relationship

Time:12-07

So I have these two simple classes inside the same python file which I'm trying to map with SQLAlchemy, where User column 'password_id' is a foreign key to the table Password 'password_id' column as well

from sqlalchemy.orm import relationship, declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.sql.schema import ForeignKey

Base = declarative_base()
class User(Base):
    
    __tablename__ = 'users'

    user_id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)
    password_id = Column(Integer, ForeignKey('password.password_id'))

    parent = relationship("Password", back_populates="users")

class Password(Base):
    
    __tablename__ = 'passwords'

    password_id = Column(Integer, primary_key=True)
    password = Column(String)
    last_change_date = Column(DateTime)
    valid_until = Column(DateTime)
    
    child = relationship("User", back_populates="passwords", uselist=False)

Here's the db schema for context:

db-schema

I'm following this guide from sql alchemy but for some reason I keep getting the error from the title 'Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.', which is strange because accorind to what I see in the guide, my classes have been mapped correctly so I can't understand why this error is happening.

Thank you in advance

CodePudding user response:

I think the issue is in the following line (the table name in ForeignKey)...

password_id = Column(Integer, ForeignKey('password.password_id'))

should be passwords instead of password.

  • Related