如何在sqlalchemy python中实现此解决方案?
cbjzeqam1#
给定以下orm类:
class Entry(Base): __tablename__ = ... ID = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True) TYPE = sqlalchemy.Column(sqlalchemy.String, nullable=False) ITEM = sqlalchemy.Column(sqlalchemy.String, nullable=False)
您可以通过以下查询实现这一点:
COUNT = sqlalchemy.func.count(Entry.ITEM).label("COUNT") query = session.query(Entry.ITEM, COUNT) # SELECT ITEM, count(ITEM) as COUNT FROM TableName query = query.filter(Entry.TYPE=='food') # WHERE Type='food' query = query.group_by(Entry.ITEM) # GROUP BY ITEM query = query.order_by(COUNT.desc()) # ORDER BY COUNT DESC
这个 query 然后可以在以下对象上迭代对象:
query
for item, amount in query: print(f"{item}: {amount}")
下面是一个完整的工作示例:
import os import sqlalchemy, sqlalchemy.ext.declarative, sqlalchemy.orm SQLITE_FILE = "example.sqlite" if os.path.exists(SQLITE_FILE): os.remove(SQLITE_FILE) engine = sqlalchemy.create_engine(f"sqlite:///{SQLITE_FILE}") Base = sqlalchemy.ext.declarative.declarative_base(bind=engine) class Entry(Base): __tablename__ = "entries" ID = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True) TYPE = sqlalchemy.Column(sqlalchemy.String, nullable=False) ITEM = sqlalchemy.Column(sqlalchemy.String, nullable=False) Base.metadata.create_all() create_session = sqlalchemy.orm.session.sessionmaker(bind=engine) session:sqlalchemy.orm.Session = create_session() example_types = (['food']*6) + (['drink']*2) + (['food']*2) example_items = [ 'apple', 'apple', 'orange', 'apple', 'banana', 'banana', 'cola', 'water', 'banana', 'apple' ] example_entries = [ Entry( TYPE=example_type, ITEM=example_item ) for example_type, example_item in zip( example_types, example_items ) ] for example in example_entries: session.add(example) session.commit() # https://stackoverflow.com/questions/22479759 """ select Item, count(Item) as COUNT FROM TableName WHERE Type='food' GROUP BY Item ORDER BY COUNT DESC """ COUNT = sqlalchemy.func.count(Entry.ITEM).label("COUNT") query = session.query(Entry.ITEM, COUNT) # select ITEM, count(ITEM) as COUNT FROM TableName query = query.filter(Entry.TYPE=='food') # WHERE Type='food' query = query.group_by(Entry.ITEM) # GROUP BY ITEM query = query.order_by(COUNT.desc()) # ORDER BY COUNT DESC for item, amount in query: print(f"{item}: {amount}")
1条答案
按热度按时间cbjzeqam1#
给定以下orm类:
您可以通过以下查询实现这一点:
这个
query
然后可以在以下对象上迭代对象:下面是一个完整的工作示例: