PythonMySQL我如何循环通过一个选择？

Question

我正在尝试用MySQL建立一个足球数据库。我做了一个选择，其中包含团队，我想在另一个选择中循环它们，所以我没有写18个不同的选择。你能帮帮我吗，我哪里出错了？我总是犯错误。

k = 0

#Teams in one list
my_sq7 = "SELECT Team FROM bundesligatabella_2020_2021"
my_cursor.execute(my_sq7)
result3 = my_cursor.fetchall()

for row in result3:
    my_sq9 = "SELECT SUM(Home_scored) FROM bundesliga_2020_2021_2 WHERE Home = %s"
    my_cursor.executemany(my_sq9, result3[k][0])
    result4 = my_cursor.fetchall()
    k += 1

我还想把结果保存在另一个列表中，以更新另一列。

错误:

Traceback (most recent call last):
  File "python\database.py", line 141, in <module>
    my_cursor.executemany(my_sq9, result3[k][0])
  File "Python\Python39\lib\site-packages\mysql\connector\cursor.py", line 675, in executemany
    self.execute(operation, params)
  File "Python\Python39\lib\site-packages\mysql\connector\cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "Python\Python39\lib\site-packages\mysql\connector\connection.py", line 651, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "Python\Python39\lib\site-packages\mysql\connector\connection.py", line 538, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1

Answer 1

中执行 的元素进行索引。/li> 需要同时获得所有值。但不能将其用于SELECT语句；您将只返回最后一次选择的结果。/li> 但我认为真正的问题是: 或 的第二个参数需要是 或 （或一些可迭代的）。在 的情况下，它需要是，例如，列表的列表。这两个你似乎都没有通过。/li>

所以:

result3 = my_cursor.fetchall()
my_sq9 = "SELECT SUM(Home_scored) FROM bundesliga_2020_2021_2 WHERE Home = %s"
result4 = []
for row in result3:
    my_cursor.execute(my_sq9, (row[0],))
    result4.append(my_cursor.fetchone())

你有没有考虑过通过以下方式得到你想要的东西:

SELECT Team, SUM(Home_scored) FROM bundesligatabella_2020_2021
GROUP BY Team

更正:

SELECT Team, SUM(Home_scored) FROM
bundesligatabella_2020_2021 t1 JOIN bundesliga_2020_2021_2 t2 on t1.Team = t2.Home
GROUP BY Team