sqlalchemy : Could not determine join condition between parent/child tables

Question

I am very new to SQLAlchemy. I am having some difficulty setting up a one to many relationship between two models in my application. I have two models User `Photo'. A user has only one role associated with it and a role has many users associated with it.

This is the code that I have in my data_generator.py file:

# coding=utf-8
from sqlalchemy import Column, Integer, String, BigInteger,Date, Enum, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
import time
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, relationship
import datetime

Base = declarative_base()


class User(Base):

    __tablename__ = 'users'

    id = Column(Integer(), primary_key=True)
    username = Column(String(30), unique=True, nullable=False)  
    password = Column(String, default='123456', nullable=False) 
    name = Column(String(30), nullable=False) 
    grade = Column(String(30))  
    emp_no = Column(BigInteger, unique=True, nullable=False) 
    roles = relationship('Role', back_populates='users')

class Scene(Base):

    __tablename__ = 'scenes'

    id = Column(Integer, primary_key=True)
    scene_name = Column(String(30), nullable=False) 
    life_time = Column(Date, nullable=False,
                       default=datetime.datetime.strptime(
                       time.strftime("%Y-%m-%d", time.localtime(time.time() + (12 * 30 * 24 * 3600))),'%Y-%m-%d').date())  
    scene_description = Column(String(150), default="")  


class Gateway(Base):

    __tablename__ = 'gateways'

    id = Column(Integer, primary_key=True)
    gateway_name = Column(String(30), nullable=False) 
    gateway_api_key = Column(String(100), nullable=False, unique=True) 
    gateway_type = Column(Enum('up', 'down', 'soft', name="gateway_type"), nullable=False)


class Role(Base):

    __tablename__ = 'roles'

    id = Column(Integer, primary_key=True)

    role_name = Column(String(30), unique=True, nullable=False)


    users = relationship('User', back_populates='roles')

    def __repr__(self):
        return self.role_name







engine = create_engine('sqlite:///memory:')
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
Base.metadata.create_all(engine)

ed_user = User(name='ed', username='jack', password='123', emp_no=1, grade='1', roles=1)
example_scene = Scene(scene_name='example_1', scene_description='example_description')
example_gateway = Gateway(gateway_name='example_1',gateway_api_key='11111',gateway_type='up')

# session.add(example_gateway)
# session.commit()


def init_user(flag, number):
    while number >= 1:
        if flag == 1:
            ed_user = User(name='ed', username='jack', password='123', emp_no=1, grade='1')
            pass
        if flag == 2:
            # TODO admin
            pass
        if flag == 3:
            # TODO teacher
            pass
        number -= 1


def init_scene(number):
    while number >= 1:
        number -= 1
        # TODO scene

def init_gateway(api_key, number):
    # TODO gateway
    pass

if __name__ == '__main__':
    with session.no_autoflush:
        c = session.query(Gateway).all()
    print c[0].id

The error that I keep encountering is shown below:

/usr/bin/python2.7 /home/pajamas/PycharmProjects/untitled5/data_generator.py
Traceback (most recent call last):
  File "/home/pajamas/PycharmProjects/untitled5/data_generator.py", line 73, in <module>
    ed_user = User(name='ed', username='jack', password='123', emp_no=1, grade='1', roles=1)
  File "<string>", line 2, in __init__
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py", line 347, in _new_state_if_none
    state = self._state_constructor(instance, self)
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 764, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py", line 177, in _state_constructor
    self.dispatch.first_init(self, self.class_)
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 256, in __call__
    fn(*args, **kw)
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 3088, in _event_on_first_init
    configure_mappers()
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2984, in configure_mappers
    mapper._post_configure_properties()
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1810, in _post_configure_properties
    prop.init()
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 184, in init
    self.do_init()
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1658, in do_init
    self._setup_join_conditions()
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1733, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1991, in __init__
    self._determine_joins()
  File "/home/pajamas/.local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 2096, in _determine_joins
    "specify a 'primaryjoin' expression." % self.prop)
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship User.roles - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Process finished with exit code 1

Can someone assist me with this? Help would be greatly appreciated.

Answer 1

There might be three relationships between User and Role:

• One to One(One user has only one Role)
• Many to One(One user has many roles)
• Many to Many(Many user has many roles)

For One to One:

class Role(Base):

    id = Column(Integer, primary_key=True)

    # ...

    user_id = Column(Integer, ForeignKey("user.id"))

class User(Base):

    id = Column(Integer, primary_key=True)

    # ...

    role = relationship("Role", back_populates="user", uselist=False)

For Many to One:

class Role(Base):

    id = Column(Integer, primary_key=True)

    # ...

    user_id = Column(Integer, ForeignKey("user.id"))

class User(Base):

    id = Column(Integer, primary_key=True)

    # ...

    roles = relationship("Role", back_populates="user")

For Many to Many:(In this relation, we need a associate table)

roles_users = Table("roles_users", 
Column("role_id", Integer, ForeignKey("role.id")), 
Column("user_id", Integer, ForeignKey("user.id")))

class Role(Base):

    id = Column(Integer, primary_key=True)

    # ...

class User(Base):

    id = Column(Integer, primary_key=True)

    # ...

    roles = relationship("Role", back_populates="users", secondary=roles_users)