时间戳

首页 > 解决方案 > Python MYSQL 如何循环选择？

python - Python MYSQL 如何循环选择？

问题描述

我正在尝试使用 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

标签： pythonmysqlfor-loop

解决方案

  1. 如果你正在做for row in result3:，那么就没有必要result3使用索引来索引元素了。
  2. executemany一次期望所有的值。但是您不能将其用于 SELECT 语句；您将只返回最后一个 SELECT 的结果。
  3. 但我相信真正的问题是： or 的第二个参数execute需要executemany是listor tuple（或一些可迭代的）。在 的情况下executemany，它需要是例如列表的列表。你似乎没有通过这些。

所以：

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

推荐阅读