python - 联合子查询的 SqlAlchemy groupby

Question

我需要在同一张表上做 3 个查询，将它们合并，然后用 sum 做一个 groupby。

这是我的解决方案。

q1 = Contact.query.with_entities(Contact.name, Contact.surname, Contact.company, literal(1).label('weight')).filter(Contact.name.ilike(expr))
q2 = Contact.query.with_entities(Contact.name, Contact.surname, Contact.company, literal(2).label('weight')).filter(Contact.surname.ilike(expr))
q3 = Contact.query.with_entities(Contact.name, Contact.surname, Contact.company, literal(3).label('weight')).filter(Contact.company.ilike(expr))

qq = q1.union_all(q2,q3).subquery()

w = db.session.query(qq.c.name, qq.c.surname, qq.c.company, func.sum(qq.c.weight)).group_by(qq.c.name).all()

它引发以下错误

Traceback (most recent call last)

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/sqlalchemy/util/_collections.py", line 210, in __getattr__

    return self._data[key]

    During handling of the above exception, another exception occurred:
    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/flask/app.py", line 2463, in __call__

    return self.wsgi_app(environ, start_response)

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/flask/app.py", line 2449, in wsgi_app

    response = self.handle_exception(e)

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/flask/app.py", line 1866, in handle_exception

    reraise(exc_type, exc_value, tb)

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise

    raise value

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/flask/app.py", line 2446, in wsgi_app

    response = self.full_dispatch_request()

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/flask/app.py", line 1951, in full_dispatch_request

    rv = self.handle_user_exception(e)

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/flask/app.py", line 1820, in handle_user_exception

    reraise(exc_type, exc_value, tb)

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise

    raise value

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/flask/app.py", line 1949, in full_dispatch_request

    rv = self.dispatch_request()

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/flask/app.py", line 1935, in dispatch_request

    return self.view_functions[rule.endpoint](**req.view_args)

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/flask_login/utils.py", line 272, in decorated_view

    return func(*args, **kwargs)

    File "/home/user1/pyweb/profc/app/routes_contact.py", line 42, in contact

    w = db.session.query(qq.c.name, qq.c.surname, qq.c.company, func.sum(qq.c.weight)).group_by(qq.c.name).all()

    File "/home/user1/pyweb/profc/env/lib/python3.8/site-packages/sqlalchemy/util/_collections.py", line 212, in __getattr__

     [Open an interactive python shell in this frame] raise AttributeError(key)

    AttributeError: name

我哪里错了？

*编辑：已解决*

问题是联合后的名称，我解决了标记每一列

q1 = Contact.query.with_entities(Contact.name.label('uname'), Contact.surname.label('usurname'), Contact.company.label('ucompany'), literal(1).label('weight')).filter(Contact.name.ilike(expr))
q2 = Contact.query.with_entities(Contact.name.label('uname'), Contact.surname.label('usurname'), Contact.company.label('ucompany'), literal(2).label('weight')).filter(Contact.surname.ilike(expr))
q3 = Contact.query.with_entities(Contact.name.label('uname'), Contact.surname.label('usurname'), Contact.company.label('ucompany'), literal(3).label('weight')).filter(Contact.company.ilike(expr))

qq = q1.union_all(q2,q3).subquery()

w = db.session.query(qq.c.uname, qq.c.usurname, qq.c.ucompany, func.sum(qq.c.weight)).group_by(qq.c.uname).all()

标签： pythonsqlalchemyflask-sqlalchemy