It's a great game. A great many people do play it in single player and I'm pretty sure that a number of mods are available for it (like the 7 Deadly Sins mod). I can't speak as to how long it would hold a person's interest in single player since I've played it exclusively in online multiplayer two weeks after I bought the game a year ago. It's still my top game today.
The online multiplayer game is very challenging and competitive. However, you'll also need to spend $10 on Entrenchment since just about everyone who plays the game online has it now. Also, you should be aware that the online multiplayer community has matured and that there are many great players out there and fewer and fewer noobs, which makes it increasingly difficult to "break into" the game. Regardless of how good you are at the single player game, online multplayer is very different and you can expect to suffer many butt thumpings at the hands of...guys like me. You just have to be patient and take your lumps, read the Sins Strategy forums at the game's website, watch good players in replays (like members of the [DT] clan), and learn the game. Getting better and learning to hold your own against average (who are now pretty good) players is part of the fun and the challenge. It's a game that's like chess--a couple hours to learn, a great many hours to master.
Also, note online player counts are low for a game that must have sold over 600,000 copies, so it may be difficult finding a game at times. Your best bet is European and North American prime time (say 3 p.m. to Midnight U.S. Eastern Standard Time). One issue is whether online multiplayer activity will die when Starcraft 2 is released.