Sqlalchemy一对多关系中的记录计数

huangapple
go评论95阅读模式
英文:

Sqlalchemy count of records in the relation of one to many

问题

帮我找出我做错了什么？

我需要获取一对多表中24小时内的评论数量。

我不知道查询是否正确，甚至是否输出记录数量。

以下是请求本身：

我收到了这个错误：

英文:

Help me figure out what I'm doing wrong?

I need to get the number of comments in 24 hours in the relation one-to-many table.

  1. class Comment(Base):
  2.     __tablename__ = 'comments'
  3.     id = Column(Integer, primary_key=True)
  4.     task_id = Column(Integer(), ForeignKey('tasks.id'), nullable=False)
  5.     post_link = Column(String, nullable=False)
  6.     date = Column(DateTime, default=datetime.datetime.utcnow())
  8.     def __init__(self, task_id: int, post_link: str):
  9.         super().__init__()
  10.         self.task_id = task_id
  11.         self.post_link = post_link
  13.     def __repr__(self):
  14.         return f'id - {self.id} | task_id - {self.task_id} | date - {self.date}'
  1. class Task(Base):
  2.     __tablename__ = 'tasks'
  3.     id = Column(Integer, primary_key=True)
  4.     name = Column(String, nullable=False)
  6.     comments = relationship('Comment', backref='tasks', lazy=True)
  8.     def __init__(self, name: str):
  9.         super().__init__()
  10.         self.name = name
  12.     def __repr__(self):
  13.         return f'id - {self.id} | name - {self.name}'

I don't know if the query works correctly or if it even outputs the number of records.

Here's the request itself:

  1. async def get_comments_for_day():
  2.     start_day = datetime.utcnow() - timedelta(hours=23, minutes=50)
  3.     async with get_async_session() as session:
  4.         stmt = select(Comment.task_id, func.count(Comment.task_id).label('comments_found'))\
  5.             .where(Comment.date >= start_day).subquery()
  6.         main_stmt = select(Task, stmt.c.comments_found).outerjoin(stmt, Task.id == stmt.c.task_id)
  7.         results = await session.execute(main_stmt)
  8.         return results.scalars().all()
  10. async def main():
  11.     tasks = await get_comments_for_day()
  12.     for task, comments_found in tasks:
  13.         print(task.name, comments_found)

I get this error:

  1.     for task, comments_found in tasks:
  2. TypeError: cannot unpack non-iterable Task object

答案1

得分: 1

你正在迭代tasks，每个task，但问题是每个任务都是一个Task、count的元组，不仅仅是一个Task对象。

只需将打印语句更改为引用正确的索引

编辑：让我们尝试直接在查询中指定属性，例如id和name，并且我们将使用group_by()来对任务进行分组，然后计算每个任务的评论数。

  1. async def get_comments_for_day():
  2.     start_day = datetime.utcnow() - timedelta(hours=23, minutes=50)
  3.     async with get_async_session() as session:
  4.         stmt = (
  5.             select(Task.id, Task.name, func.count(Comment.id).label('comments_found'))
  6.             .select_from(Task)
  7.             .join(Comment, Task.id == Comment.task_id, isouter=True)
  8.             .where(Comment.date >= start_day)
  9.             .group_by(Task.id, Task.name)
  10.         )
  11.         result = await session.execute(stmt)
  12.         return result.scalars().all()
  14. async def main():
  15.     tasks = await get_comments_for_day()
  16.     for task_id, task_name, comments_found in tasks:
  17.         print(task_name, comments_found)

请注意，这是你提供的代码的翻译版本，没有其他内容。

英文:

You are iterating over tasks, each task, but the thing is each task is a tuple of Task, count, not just a Task object.

Just change the print statement to reference the correct index

edit: lets try to directly specify the attributes in the query, such as id and name and also we will group_by() to group the results by task and then count the comments for each

  1. async def get_comments_for_day():
  2.     start_day = datetime.utcnow() - timedelta(hours=23, minutes=50)
  3.     async with get_async_session() as session:
  4.         stmt = (
  5.             select(Task.id, Task.name, func.count(Comment.id).label('comments_found'))
  6.             .select_from(Task)
  7.             .join(Comment, Task.id == Comment.task_id, isouter=True)
  8.             .where(Comment.date >= start_day)
  9.             .group_by(Task.id, Task.name)
  10.         )
  11.         result = await session.execute(stmt)
  12.         return result.scalars().all()
  14. async def main():
  15.     tasks = await get_comments_for_day()
  16.     for task_id, task_name, comments_found in tasks:
  17.         print(task_name, comments_found)

答案2

得分: 0

  1. async def get_comments_for_day():
  2.     start_day = datetime.utcnow() - timedelta(hours=24)
  3.     async with get_async_session() as session:
  4.         stmt = (
  5.             select(func.count(Comment.id), Task)
  6.             .select_from(Task)
  7.             .join(Comment, Task.id == Comment.task_id, isouter=True)
  8.             .where(Comment.date >= start_day)
  9.             .group_by(Task.id, Task.name)
  10.         )
  11.         results = await session.execute(stmt)
  12.         return results.all()

If you're interested, I found a solution to the problem, which was to write return results.all() instead of return results.execute().all().

英文: 
  1. async def get_comments_for_day():
  2.     start_day = datetime.utcnow() - timedelta(hours=24)
  3.     async with get_async_session() as session:
  4.         stmt = (
  5.             select(func.count(Comment.id), Task)
  6.             .select_from(Task)
  7.             .join(Comment, Task.id == Comment.task_id, isouter=True)
  8.             .where(Comment.date >= start_day)
  9.             .group_by(Task.id, Task.name)
  10.         )
  11.         results = await session.execute(stmt)
  12.         return results.all()

If you're interested, I found a solution to the problem, which was to write return results.all() instead of return results.execute().all().

huangapple
  • 本文由 发表于 2023年6月2日 02:07:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/76384590.html
  • python
  • sqlalchemy
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定