Alan Tingey Alan Tingey - 2 months ago 18
MySQL Question

Django Raw query usage

I am struggling to use the output of a raw query. My code is as follows:

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


I get the following error:

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


Any help would be appreciated, many thanks in advance, Alan.

PS

In case it helps the result of my query in MySQL is as follows:

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

Your issue is that you are using cursor.fetchone() and iterating through the result expecting it to return multiple rows.

If you want the top 2 results, you might want to use fetchmany and limit to 2 records instead.

This is what is happening under the hood:

Since you are fetching only one record, currentSelectedTeams[0] is actually returning the fixturematchday column, which it looks like is of type long and you are unable to access the attribute from it.

Another option would be to use the pretty powerful Django ORM to fetch this query result

EDIT:

If you really want to stick with cursor based implementation, try this:

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

Note that, in an edge case scenario, where only one row is returned, this implementation would fail. (You need to check for the cursor return length, etc.. )

If this were a django queryset implementation, it would look something like this:

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