sqlalchemy classical mapping relationship issue : <p>I am on a mission to finally learn sqlAlchemy so that I can reap the benefits in the years to come. </p>

<p>I am neck deep in the sqlalchemy documents and have been for the past two days. I am hell bent on learning the classical mapping way, instead of the declarative, bc the db I want to hook up to exists, and does not have a unique id column in all of it's tables. <a href="http://www.blog.pythonlibrary.org/2010/09/10/sqlalchemy-connecting-to-pre-existing-databases/">According to this article classical mapping is the way to go under such circumstances</a></p>

<p>I have been following the classical examples from the sqlalchemy site, but I just cannot seem to find the correct relationship configuration to get this to work. </p>

<p>Here is all my code: </p>

<pre><code>engine = create_engine(
    "mssql+pyodbc://someaddress/test?driver=FreeTDS?TDS_version=8.0", echo=True)

metadata = MetaData(engine)

class User(object):

    def __repr__(self):
        return "&lt;User(User_id='%s', name='%s', age='%s')&gt;" % (
                            self.user_id, self.name, self.age)
class Email(object):

    def __repr__(self):
        return "&lt;User(email_id='%s', address='%s', user_id='%s')&gt;" % (
                            self.email_id, self.address, self.user_id)

users = Table('users', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('name', String(40)),
    Column('age', Integer),
    schema='test.dbo.users')

mapper(User, users, properties={'Email': relationship(Email, primaryjoin=users.c.user_id==emails.c.user_id)})

emails = Table('emails', metadata,
    Column('email_id', Integer, primary_key=True),
    Column('address', String),
    Column('user_id', Integer, ForeignKey('test.dbo.users.user_id')),
    schema='test.dbo.emails')

mapper(Email, emails)

Session = sessionmaker(bind=engine)
session = Session()

mary = session.query(User, Email).filter(User.user_id == Email.user_id)
</code></pre>

<p>The pursuing error message makes it clear that it is the mapper / relationship that is the problem.</p>

<pre><code>InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers.  Original exception was: Could not determine join condition between parent/child tables on relationship User.Email - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
</code></pre>

<p>I have tried a long list of different things to try to remedy, but I just cannot get to the bottom of it. </p>

<p>Any pointers in the right direction would be much appreciated!</p>

<p>The sqlalchemy version I am on is; </p>

<pre><code>'1.0.12'
</code></pre>
