通过字段连接两个表时，Python 不返回数据

Question

我有一个查询在 MySQL 中正确返回数据，但在 Python 中只返回部分数据。

查询是:

select sc.* from tbl030_shots_chart sc, tbl006_player_team tc where
sc.id_fiba = tc.id_player_feb and
tc.id_team_club = 5

MySQL 中的这个查询返回 1030 行，就像您在这个屏幕截图中看到的那样。

但是，如果我用 python 执行这个查询，我只有 67 行。这是我的代码:

connection = pymysql.connect(host = DDBB.DDBB_FIBA_HOST,
                      user = DDBB.DDBB_FIBA_USER,
                      password = DDBB.DDBB_FIBA_PSWD,
                      db = DDBB.DDBB_FIBA_NAME,
                      charset = DDBB.DDBB_FIBA_CHARSET,
                      cursorclass=pymysql.cursors.DictCursor)
with connection.cursor() as cursor:
    totalRows = cursor.execute("select sc.* from tbl030_shots_chart sc, tbl006_player_team tc where sc.id_fiba = tc.id_player_feb and tc.id_team_club = %s", [5])
    print("Total Rows: " + str(totalRows))

这是导出:

为什么我从 Python 得到的数据比 MySQL 少？

这些是表的定义:

tbl030_shots_chart

tbl006_player_team

编辑我:

内部连接在 python 中不起作用，但在 MySQL 中起作用

但是，使用 python，仍然返回 76 行，而不是像 MySQL 那样返回 1030 行。

connection = pymysql.connect(host = DDBB.DDBB_FIBA_HOST,
                      user = DDBB.DDBB_FIBA_USER,
                      password = DDBB.DDBB_FIBA_PSWD,
                      db = DDBB.DDBB_FIBA_NAME,
                      charset = DDBB.DDBB_FIBA_CHARSET,
                      cursorclass=pymysql.cursors.DictCursor)
with connection.cursor() as cursor:
    totalRows = cursor.execute("select sc.* from tbl030_shots_chart as sc inner join tbl006_player_team as pt on sc.id_fiba = pt.id_player_feb and pt.id_team_club = %s", [5])
    print("Total Rows: " + str(totalRows))

如果我用这段代码得到游标的总行数:

connection = pymysql.connect(host = DDBB.DDBB_FIBA_HOST,
                      user = DDBB.DDBB_FIBA_USER,
                      password = DDBB.DDBB_FIBA_PSWD,
                      db = DDBB.DDBB_FIBA_NAME,
                      charset = DDBB.DDBB_FIBA_CHARSET,
                      cursorclass=pymysql.cursors.DictCursor)
with connection.cursor() as cursor:
    cursor.execute("select sc.* from tbl030_shots_chart as sc inner join tbl006_player_team as pt on sc.id_fiba = pt.id_player_feb and pt.id_team_club = %s", [5])
    totalRows = cursor.rowcount
    print("Total Rows: " + str(totalRows))

我返回了 76 行而不是 1030 行。

Answer 1

您可以尝试创建一个 view对于这个查询。

CREATE VIEW your_view AS (

    SELECT 
    t1.id,
    t1.id_game,
    t1.line,
    ...

    t2.id_team_club,
    t2.id_player_feb,
    ...

    FROM tbl030_shots_chart t1
    LEFT JOIN
    tbl006_player_team t2
)

然后在你的 python 代码中:

sql = 'SELECT * FROM your_view WHERE id_fiba =id_player_feb AND id_team_club = %s'
with connection.cursor() as cursor:
    cursor.execute(sql, (5))