sqlalchemy: selecting columns referring to an aliased table name
<p>I'm fairly new to using sqlalchemy and having some issues generating the sql code that I am looking for.</p>

<p>Ultimately, I'm trying to join two different subsets of <strong>table2</strong> to <strong>table1</strong> by using the following SQL query:</p>

<pre><code>SELECT table1.date, a1.id AS name1_id, a2.id AS name2_id
FROM table1
LEFT JOIN table2 as a1
    ON table1.name1 = table2.label AND table2.lookup_id = 1000
LEFT JOIN table2 as a2
    ON table1.name2 = table2.label AND table2.lookup_id = 2000
</code></pre>

<p>Here's what I have so far using sqlalchemy:</p>

<pre><code>q_generate = (
select([table1.c.date,
        a1.id.label('name1_id'),
        a2.id.label('name2_id')])

.select_from(table1
    .outerjoin(table2.alias(name='a1'),
               and_(
                    table2.c.lookup_id == 1000,
                    table1.c.name1 == table2.c.label
                    ))
    .outerjoin(table2.alias(name='a2'),
               and_(
                    table2.c.lookup_id == 2000,
                    table1.c.name2== table2.c.label
                    ))
    )

)
</code></pre>

<p>which produces the following errors:</p>

<pre><code>*NameError: name 'a1' is not defined*
</code></pre>

<p>Is there a special way that aliased table names must be referenced? What am I missing here? I think the error has something to do with these lines but I can't figure out how exactly to get this to work:</p>

<pre><code>...
a1.id.label('name1_id'),
a2.id.label('name2_id')])
...
</code></pre>

<p>Thank you!</p>