SQLAlchemy & pandas: How to query columns with a different label?
<p>When using pandas <code>read_sql</code> to query my database using SQLAlchemy, I get the following warning:</p>

<blockquote>
  <p>SAWarning: Column 'id' on table  being replaced by Column('id', Integer(), table=, primary_key=True, nullable=False), which has the same key.  Consider use_labels for select() statements.
        (key, getattr(existing, 'table', None), value))</p>
</blockquote>

<p>Right, so each of my <code>League</code>, <code>Season</code>, <code>Round</code>, <code>Match</code>, and <code>Team</code> tables have a column named <code>id</code>. Of course. </p>

<p>I ignored this at first, but this came to bite me in the buttocks when I wanted to delete one of the columns with the <code>id</code> label/name, using <code>pd.drop()</code>. Even <code>pd.rename</code> referencing the column by its index (!) renamed all columns with the same name:</p>

<pre><code>pandoc.rename(
    columns={pandoc.columns[1]: 'match_id'}, 
    inplace=True)
# This replaced all columns with the label `id` to `match_id`
</code></pre>

<p>SQLAlchemy advises that I use consider use_labels for select() statements, and while I managed to do with with regular queries, I can't really figure out where to stick <code>.label('new_column_name')</code> in the following query:</p>

<pre><code>pandoc = pd.read_sql(
Match.query.options(
        joinedload(Match.home_team),
        joinedload(Match.away_team)).statement, 
    db.session.bind,
    parse_dates=['date_time'])
</code></pre>

<p>One option is to change all <code>id</code> columns in my tables to <code>tablename_id</code>, but that seems like an ugly workaround for a problem that should have a fairly simple solution.</p>

<p>Sample output from <code>print(pandoc.head())</code>:</p>

<pre><code>   total_goals  id               round_id  \
0          1.0  somestring here  s12786-0   
1          0.0  somestring here  s12786-0   
2          5.0  somestring here  s12786-0   
3          3.0  somestring here  s12786-0   
4          0.0  somestring here  s12786-0   

   home_team_id  away_team_id   id   id  
0           667           664  667  664  
1           669           691  669  691  
2           672           677  672  677  
3           707           686  707  686  
4           699           703  699  703
</code></pre>

<p>Notice the 3 <code>id</code> columns, one is the match ID, the other two are the home team ID and the away team ID.</p>