SqlAlchemy Python。从mysql到sqlAlchemy

Question

如何将下一个mysql代码转换为sqlAlchemy：

       SELECT
                u.id AS writer_id,
                u.user_login AS user_login,
                m.message_id AS message_id,
                m.message,
                m.time
            FROM
                (SELECT
                    message_id, tm.writer_id, tm.receiver_id, message, time
                FROM
                    test_messages tm
                WHERE
                    (tm.writer_id = 1 AND tm.receiver_id = 2)
                        OR (tm.writer_id = 2 AND tm.receiver_id = 1)
                ORDER BY tm.time DESC
                LIMIT 10 OFFSET 0) AS m
                    LEFT JOIN
                users u ON u.id = m.writer_id
            ORDER BY (m.time);  

我下一次尝试：

s = select([
    users.c.id,
    users.c.user_login,
    messages.c.message_id,
    messages.c.message,
    messages.c.time
]).select_from(
    select([
        messages.c.message_id,
        messages.c.writer_id,
        messages.c.receiver_id,
        messages.c.message,
        messages.c.time
    ]).where(
        or_(
            and_(
                messages.c.writer_id == writer_id,
                messages.c.receiver_id == receiver_id
            ),
            and_(
                messages.c.writer_id == receiver_id,
                messages.c.receiver_id == writer_id
            )
        )
    ).select_from(
        users.outerjoin(messages, users.c.id == messages.c.writer_id)
    ).order_by(messages.c.time.desc())
).order_by(messages.c.time)

response = await conn.execute(s)

headers = response.keys()
rows = await response.fetchall()    

化名有问题。

pymysql.err.InternalError: (1248, 'Every derived table must have its own alias')

我已经编写了整个子查询，但是别名有问题。我是应该显式设置别名，还是应该做什么？请帮帮忙。

Answer 1

如果使用核心和SQL表达式语言，则可以使用以下方法形成等效的查询：

In [25]: subq = messages.select().\
    ...:     where(or_(
    ...:         and_(
    ...:             messages.c.writer_id == writer_id,
    ...:             messages.c.receiver_id == receiver_id
    ...:         ),
    ...:         and_(
    ...:             messages.c.writer_id == receiver_id,
    ...:             messages.c.receiver_id == writer_id
    ...:         )
    ...:     )).\
    ...:     order_by(messages.c.time.desc()).\
    ...:     limit(10).\
    ...:     alias()

In [26]: s = select([users.c.id,
    ...:             users.c.user_login,
    ...:             subq.c.message_id,
    ...:             subq.c.message,
    ...:             subq.c.time]).\
    ...:     select_from(
    ...:         subq.
    ...:         outerjoin(users, users.c.id == subq.c.writer_id)).\
    ...:     order_by(subq.c.time)

注意alias()的使用。与查询构造尝试相比，原始SQL具有相反的联接方式，因此我遵循了原始SQL。