* Tomahawk is a desktop app. It integrates various music sources so you can play from whatever source is available. I can search for whatever and it will look on my local harddisk, on my Synology NAS, on Soundcloud, youtube, etc. It will play from the "best" source, determined by search match accuracy and bitrate. What's pretty cool is that it is integrated with Echonest (www.echonest.com) so you can define "Radio Stations" based on criteria like artist similarity, song mood, energy, etc. And those songs will be automatically resolved. My experience has been very good, everything works out of the box. Only the youtube resolver needs to be enabled manually (for copyright reasons).
* Mopidy, as far as I know, aims to do this on the server side. I've run an early version, I think 0.18 or something, on my Raspberry Pi. It involves some configuration to get it to work. Also, I don't think it can resolve songs in the way that Tomahawk does it: because MPD seems to have a static concept of the "Library". So you can import your Soundcloud "Liked songs", and they will be available in your library, but you cannot search for arbitrary songs. At least I didn't find that option.
A hybrid of both solutions would be awesome.
Tomahawk is a UI that has a standard "music player" behaviour. The sources are plugged in (they're called resolvers[0]), so that the core remains more or less the same.
AFAIU, mopidy acts a middle-man between sources (installed as plugins) and uis. It's more or less what playdar [1] (the ancestor of tomahawk) was, at least functionnally.
[0] https://github.com/tomahawk-player/tomahawk-resolvers [1] http://www.playdar.org/