While this hasn't solved my problem, I found this question which suggests adding SET ANSI_WARNINGS OFF might be a solution. In my case, although my query is returning the data I want in MSSMS, it is also returning the below warning in the messages tab:

Warning: Null value is eliminated by an aggregate or other SET operation.

As I understand it, when calling a query in python it takes the first thing returned by that query. In this case, that is the warning message, hence the object does not return rows error. Adding the SET ANSI_WARNINGS OFF to the start of the query should suppress the warnings and allow python to read in the data.

I got different errors using this, so it didn't work for me but I've posted this answer in case it might help anyone else with this issue. In my case I think I will have to rework my query so that I don't get any error messages.