title: sqlalchemy : Could not determine join condition between parent/child tables
body: <p>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.</p>
  
  <p>This is the code that I have in my data_generator.py file:</p>
  
  <pre><code># 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 &gt;= 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 &gt;= 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
  </code></pre>
  
  <p>The error that I keep encountering is shown below:</p>
  
  <pre><code>/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 &lt;module&gt;
      ed_user = User(name='ed', username='jack', password='123', emp_no=1, grade='1', roles=1)
    File "&lt;string&gt;", 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
  </code></pre>
  
  <p>Can someone assist me with this? Help would be greatly appreciated.</p>