They are not sexy at all. Only equally demented person can find them sexy.
Or masochist person.
I bet many from media are masochists.
Or story exploitng greedy vultures.
On a side-note: That's the impression
I often get as well.
I must say from what I've percieved I also believe that most woman are still kind of passive and wish to have a man on their side that is a born leader, and whether the media exploits that desire by hyping psychopaths/sociopaths (since they are confrontal and often get their way) or they simply play along with that preference of women is up to debate. I think it's a mix of both.
But someone who is as selfish, manipulative and dangerous as a psychopath/narcissist, isn't a loyal partner you can or should put your trust in. Since most of them don't bond like non-disorderd people do, they don't think twice to target you or make you their puppet when they feel like it. And when psychopaths or narcissists turn on you, they turn. (Same goes for at least a half of the borderline personality disorderd.)
There's one series character that has been quite popular in the last years, starring: a so called high-functioning sociopath, Sherlock Holmes of the BBC series.
From my perspective and knowledge he isn't though. I think I searched the whole internet but couldn't find this one very professional article written by an Aspergers who diagnosed Sherlock, as seen in the BBC series (not the character in the books, they differ), as someone with a mild form of Aspergers.
The main points where, following: Sherlock hasn't had all that many friends or a family that pushed him to develop a better understanding of how the human moral frameworks function. Especially throughout the first series, Sherlock lets some very biting remarks about everyone around him slip and does not care and obviously so, but as the show goes on, Watson grows onto him and we get a glimpse into Sherlocks heart when he is challenged by "The Woman" (Irene Adler), who is beaten by Sherlocks power in deduction in the end but he keeps her phone as a memory. He does not tell but it's obvious he did fall for her at least a little bit. Sociopaths, high-functioning or not, are not sentimental that way. It's extremely unlikely.
There's a scene where Molly Hooper visits Sherlock and Watson in their appartement to celebrate christmas and Sherlocks power in deduction strikes once again and he, totally ignorant of the truth, reveals that Molly must be in love with someone and is serious about this man - because she is all dressed up and used red gift wrap paper on one of her gifts; and not only is it the only gift that's not wrapped slovenly but it's the exact same tone of color as her lipstick - "an unintentional association probably", he concludes. As the truth, that the only ostensibly wraped gift belongs to him, is revealed, Molly asks, sobbing a little, "why he always does things like this". He then apologizes sincerely and even gives her a kiss on the cheeks to make up,
after he could see in everyone's face around him that he exposed her feelings and how messed up this action was.
It is not very likely he would've understood he did something wrong if Molly and the other guests hadn't shown him.
And that's a big clue to differentate if someone's a sociopath or suffers from Aspergers/Autism (or is just socially retarded): A sociopath always thinks of himself, a sociopath has only his agendas in mind. But seen in the previous cases: Sherlock doesn't have an ulterior agenda behind his actions (and if he does, he calculates his manipulations, so noone gets hurt - which is something a sociopath doesn't give two shits about). He's rather socially awkward and needs someone, especially throughout the first seasons, to show him what's right and wrong because he's simply oblivious to it. But the more it's shown to him, the better he excels at not being an a-hole, which is easy to see in the series. This is completely the opposite effect to the one we know through studies with sociopaths - telling them how to differentate between right and wrong backfires, because sociopaths/psychopathes mostly use that new knowledge to mimick fake emotions even better. They simply don't have the empathy in them to care about morals or social appropriate behaviour, while Sherlock's capable of falling in our out of love and feeling sorry when he hurt someone and learning what not to do so he doesn't hurt someone.
Don't forget it's a ficitional character though, so that makes him actually impossible to diagnose with all the details matching up.
This is probably full of typos; sorry, I wasted too much time searching for that article.