Join two text() statements in SQLAlchemy

Question

What is the recommended way to join two text() statements in SQLAlchemy?

q1 = text('select a, b from table1')
q2 = text('select b, xxx from table2')

How do I join these two statements on column 'b', when none of them has the c (columns) attribute, so I cannot do .outerjoin(q2, q1.c.b == q2.c.b). The final query I need is following:

SELECT a,b,xxx from table1 left join table2 on table1.b = table2.b;

What is the recommended way do it? What is easiest way to "fake" the columns in c ?

Note: I gave simple example with the text statements. In reality, they are much more complicated than this.

Answer 1

Use TextClause.columns() to specify result columns, which also turns your text() construct to a TextAsFrom that has the usual features of a selectable:

# Replace with the actual types
q1 = text('select a, b from table1').columns(a=String, b=String)
q2 = text('select b, xxx from table2').columns(b=String, xxx=String)

You cannot transform the two statements to

SELECT a,b,xxx from table1 left join table2 on table1.b = table2.b

exactly, since SQLAlchemy is not an SQL parser, but you can produce an equivalent query by treating the two queries as subqueries and join them:

q1 = q1.alias()
q2 = q2.alias()

q = select([q1.c.a, q1.c.b, q2.c.xxx]).\
    select_from(q1.outerjoin(q2, q2.c.b == q1.c.b))