我需要在同一个表上执行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()
目前没有回答
相关问题 更多 >
编程相关推荐