Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I've been trying to solve this for hours now, I've read through a few stack overflows, the official SQL alchemy documentation and watched youtube tutorials and it's all the same code as mine.

I have 3 tables - Users and Roles, and user_role which joins them.

I keep getting the following error:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship 
Users.role - there are no foreign keys linking these tables via secondary table 'user_role'.  Ensure that referencing 
columns are associated with a ForeignKey or ForeignKeyConstraint, or specify 'primaryjoin' and 'secondaryjoin' 
expressions.
My code:
engine = create_engine('***',
connect_args={'options': '-csearch_path={}'.format('flaskProject')})
Base = declarative_base(engine)
Base.query = db.session.query_property()
user_role = Table('user_role', Base.metadata,
Column('userId', Integer, ForeignKey("Users.id")),
Column('roleId', Integer, ForeignKey("Roles.id")))
class Users(UserMixin, Base):
__tablename__ = 'users'
__table_args__ = {'schema': 'flaskProject'}
id = Column(Integer, primary_key=True)
username = Column(String)
email = Column(String)
password_hash = Column(String)
role = relationship("Roles", secondary = user_role)
class Roles(Base):
__table_args__ = {'schema': 'flaskProject'}
__tablename__ = 'roles'
id = Column(Integer, primary_key=True)
name = Column(String)
                Your users and roles tables live in the flaskProject schema but your user_role table does not. You might want to add schema="flaskProject" to your Table() call and define your foreign key declarations like Column("userId", Integer, ForeignKey("flaskProject.users.id")),
– Gord Thompson
                Feb 23, 2021 at 17:07
                @GordThompson I just tried your idea, but I still get the same error  user_role = Table('user_role', Base.metadata,     Column('userId', Integer, ForeignKey("flaskProject.Users.id")),     Column('roleId', Integer, ForeignKey("flaskProject.Roles.id"))     ,schema='flaskProject')
– igrilkul
                Feb 24, 2021 at 12:38
I found out the issues with my secondary table - I was missing the schema in the table definition, had to add the schema to the foreign keys, and column names are case-sensitive
user_role = Table('user_role', Base.metadata,
Column('userid', Integer, ForeignKey('flaskProject.users.id')),
Column('roleid', Integer, ForeignKey('flaskProject.roles.id'))
,schema='flaskProject')
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.