title: SQLAlchemy and foreign keys in reflected tables
body: <p>I'm a little bit stuck with a problem related to SQLAlchemy and reflected tables, which are connected with a foreign key.</p>
  <p>I have two tables:</p>
  <pre class="lang-sql prettyprint-override"><code>result_table:
  =============
  | id | team_home | team_visitor | result |
  | -- | --------- | ------------ | ------ |
  |  1 |         1 |            2 |    3:2 |
  |  2 |         1 |            3 |    2:0 |
  
  team_table:
  ===========
  | id | team_name |
  | -- | --------- |
  |  1 | Hawks     |
  |  2 | Sparrows  |
  |  3 | Eagles    |
  </code></pre>
  <p>Now, those tables already exist in the database. I created them using PGAdmin and filled them with sample data. <code>team_home</code>and <code>team_visitor</code> are both foreign keys pointing to <code>team_table.id</code>.</p>
  <p>With SQLAlchemy I use declaration to reflect them:</p>
  <pre class="lang-py prettyprint-override"><code>from sqlalchemy import MetaData, Table, create_engine
  from sqlalchemy.engine.url import URL
  from sqlalchemy.ext.declarative import declarative_base
  
  database = {
              ...
             }
  
  engine = create_engine(URL(**database))
  
  Base = declarative_base()
  meta = MetaData()
  
  class Results(Base):
      __table__ = Table('result_table', meta, autoload=True, autoload_with=engine)
  
  class Teams(Base):
      __table__ = Table('team_table', meta, autoload=True, autoload_with=engine)
  </code></pre>
  <p>Querying each table isn't a problem, but I'm not able to join them. A statement like <code>session.query(Results, Teams).join(Teams).all()</code> will result in a <code>AmbiguousForeignKeysError</code>.</p>
  <p>The result should be:</p>
  <pre><code>| id | team_home | team_visitor | result |
  | -- | --------- | ------------ | ------ |
  |  1 | Hawks     | Sparrows     |    3:2 |
  |  2 | Hawks     | Eagles       |    2:0 |
  </code></pre>
  <p>Viewing the metadata I cleary see that the foreign key is loaded. Now I'm stuck. I'm sure that I miss a column within my class declaring the relationship (like <code>relationship(&quot;Address&quot;, foreign_keys=[billing_address_id])</code>) but since the definition of that class is reflected, the names of the foreign keys are not avaible at calling time (or instancing time).</p>
  <p>Anyway, any hints from outside on how to get foreign keys working within reflected tables? Or should it be better to override the table definition in its class?</p>
  <p>Many thanks for your patience.</p>
  <p>Regards, Thomas</p>
  <p><strong>UPDATE</strong></p>
  <p>It's a lot easier using just the core functionality of SQLAlchemy:</p>
  <pre><code># database connection expected for this example
  
  from sqlalchemy import MetaData
  from sqlalchemy.sql import select, alias
  import pandas as pd
  
  metadata = MetaData()
  metadata.reflect(bind=engine)
  
  res_table = metadata.tables['result_table']
  tt_home = metadata.tables['team_table'].alias('team_table_home')
  tt_vis = metadata.tables['team_table'].alias('team_table_visitor')
  
  s = select([
      res_table.c.id, 
      tt_home.c.team_name, 
      tt_vis.c.team_name,
      res_table.c.result
      ]).select_from(
          res_table.join(
              tt_home, res_table.c.team_home == tt_home.c.id
          ).join(
              tt_vis, res_table.c.team_visitor == tt_vis.c.id)
      ).order_by(res_table.c.id)
  
  result_df = pd.read_sql_query(s, engine).set_index('id')
  </code></pre>
  <p>Hope this might help somebody else as well when struggeling joining two tables connected with two foreign keys.</p>
  <p>Cheers, Thomas</p>