How to set alias in the sqlalchemy for the table?

Question

I'm trying to write MySQL query in the sqlalchemy syntax, but I don't know how to represent aliasForC. Could you help me?

Query with aliasForC alias:

SELECT aliasForC.secondId
FROM A, B, C as aliasForC
WHERE B.firstId = A.firstId 
AND B.status = 'Reprep'
AND A.secondId = aliasForC.secondId
AND B.status = ALL (
   SELECT status
   FROM C
   INNER JOIN A ON A.secondId = C.secondId 
   INNER JOIN B ON A.firstId = B.firstId 
   WHERE code = aliasForC.code
)

Answer 1

You can do it in this way:

 aliasForC = aliased(C)
 # And then:
 join(aliasForC, aliasForC.firstId == A.firstId )

For All statement, you can use all_()

Answer 2

I think alias is what you're looking for.

http://docs.sqlalchemy.org/en/latest/core/selectable.html http://docs.sqlalchemy.org/en/latest/core/selectable.html#sqlalchemy.sql.expression.Alias

user_alias = aliased(User, name='user2')
q = sess.query(User, User.id, user_alias)

See: http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.column_descriptions

import sqlparse
import sqlalchemy as sa

meta = sa.MetaData()

a = sa.Table(
    'a', meta,
    sa.Column('id', sa.Integer, primary_key=True),
)

b = sa.Table(
    'b', meta,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('x', sa.Integer, sa.ForeignKey(a.c.id)),
    sa.Column('y', sa.Integer, sa.ForeignKey(a.c.id)),
)

x = b.alias('x')
y = b.alias('y')

query = (
    sa.select(['*']).
    select_from(a.join(x, a.c.id == x.c.x)).
    select_from(a.join(y, a.c.id == y.c.y))
)

print(sqlparse.format(str(query), reindent=True))

# OUTPUT:
#
#   SELECT *
#   FROM a
#   JOIN b AS x ON a.id = x.x,
#                  a
#   JOIN b AS y ON a.id = y.y

Per https://gist.github.com/sirex/04ed17b9c9d61482f98b#file-main-py-L27-L28