python - 通过字段连接两个表时,Python不返回数据
问题描述
我有一个查询可以在 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 少?
这些是表的定义:
编辑我:
内部连接在 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 行。
解决方案
您可以尝试为此查询创建视图。
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))
推荐阅读
- html - 没有在 asp.net mvc 项目的 _layout 文件中获取下拉列表
- xml - android studio中的androidMainfest.xml文件错误
- macos - SwiftUI macOS [Toolbar -> ToolbarItem -> Button -> Action] 包含@State 变量引用时的拖动降级
- javascript - 在网络调用 -> 文档 -> 响应中隐藏/删除 Next.js runtimeConfig
- php - Nginx:在 0.0.0.0:8443 上冲突的服务器名称“域”,被忽略
- python - 消除 Tkinter 中不稳定的滚动
- salesforce - 如何在 Salesforce 中找到沙盒和生产之间的区别
- laravel - 如何更改 Laravel8、livewire2 应用程序中的默认布局?
- django-models - 使用 create() 添加 M2M 字段:Django
- python - OSError:使用 text_to_font 模块时无法打开资源