python - Django 原始查询用法

Question

我正在努力使用原始查询的输出。我的代码如下:

cursor.execute("select f.fixturematchday, u.teamselection1or2, u.teamselectionid,u.user_id from straightred_fixture f, straightred_userselection u where u.user_id = 349 and f.fixtureid = u.fixtureid and f.fixturematchday=6 order by u.teamselection1or2")
currentSelectedTeams = cursor.fetchone()

if not currentSelectedTeams:
    currentSelectedTeam1 = 0
    currentSelectedTeam2 = 0
else:
    currentSelectedTeam1 = currentSelectedTeams[0].teamselectionid
    currentSelectedTeam2 = currentSelectedTeams[1].teamselectionid

我收到以下错误:

currentSelectedTeam1 = currentSelectedTeams[0].teamselectionid
AttributeError: 'long' object has no attribute 'teamselectionid'

任何帮助将不胜感激，非常感谢，艾伦。

附注

如果它有帮助，我在 MySQL 中的查询结果如下:

mysql> select f.fixturematchday, u.teamselection1or2, u.teamselectionid,u.user_id from straightred_fixture f, straightred_userselection u where u.user_id = 349 and f.fixtureid = u.fixtureid and f.fixturematchday=6 order by u.teamselection1or2;
+-----------------+-------------------+-----------------+---------+
| fixturematchday | teamselection1or2 | teamselectionid | user_id |
+-----------------+-------------------+-----------------+---------+
|               6 |                 1 |              31 |     349 |
|               6 |                 2 |              21 |     349 |
+-----------------+-------------------+-----------------+---------+
2 rows in set (0.00 sec)

Answer 1

你的问题是你是using cursor.fetchone()并迭代结果，期望它返回多行。

如果您想要前 2 个结果，您可能需要使用 fetchmany and limit to 2 records instead 。

这就是幕后发生的事情:

由于您只获取一条记录，currentSelectedTeams[0] 实际上返回 fixturematchday 列，该列看起来是 long 类型> 并且您无法从中访问该属性。

另一个选择是使用 pretty powerful Django ORM获取此查询结果

编辑:

如果您确实想坚持使用基于 cursor 的实现，请尝试以下操作:

cursor.execute("select f.fixturematchday, u.teamselection1or2, u.teamselectionid,u.user_id from straightred_fixture f, straightred_userselection u where u.user_id = 349 and f.fixtureid = u.fixtureid and f.fixturematchday=6 order by u.teamselection1or2 LIMIT 2")
#Note the LIMIT 2

currentSelectedTeams = cursor.fetchall()

if not currentSelectedTeams:
    currentSelectedTeam1 = 0
    currentSelectedTeam2 = 0
else:
    currentSelectedTeam1 = currentSelectedTeams[0].teamselectionid
    currentSelectedTeam2 = currentSelectedTeams[1].teamselectionid

请注意，在边缘情况下，仅返回一行，此实现将失败。 (需要检查光标返回长度等。)

如果这是一个 django 查询集实现，它看起来像这样:

qs = Fixture.objects.filter(..).values("fixturematchday", "userselection__ teamselection1or2", "userselection__teamselectionid", "userselection__userid")[:2]