I think the root of this kind of dynamic is the fact that no one wants to feel redundant in a relationship. If my wife could do everything I can, and more, where would that leave me? There would be no point in her continuing to be in a relationship with me.
I guess in the unhealthy relationship or in a manipulative woman, these exaggerated traits of helplessness and weakness simply parasitise what are supposed to be the inherent masculine strengths and the man's desire to be of use and gain fulfilment by exercising his abilities. So it's no accident that these caricatures of indecisiveness and weakness are the opposites of traits that are found attractive or inherent in men - decisiveness and physical ability.
Perhaps even if a man thinks he finds a 'helpless' woman attractive, that's not actually what's going on deep down inside him, and that he is actually just seeing the potential to be of use? Although, I guess there actually are 'dangerous' men who see in a helpless woman the potential to dominate and drain her.
I think there's also another dimension to these dynamics, I do think that in a relationship, an established one, (although not exclusively in romantic relationships) finding the strength to allow oneself to need someone, specially if one has the tendency to be super self reliant, or life has taught you to be so, is important, for you and your partner.
I do think that seeming helpless is a sure way to fish for men that women can use, and I do think that the signal is an offer for men to come and feed their ego by feeling good about themselves and their abilities, knowledge, skill, wordiness, financial status, as a "come and fulfill your destiny as a protector" type thing, and the opposite can also apply, men who can tap on to women's motherly instinct. It is a manipulation and it can happen unconsciously.
But, there's another dimension to it perhaps, and it's that the above manipulation also works on another level which is it shows vulnerability and trust, that is.. "She trusts me enough to show me her weak side" and viceversa. So if one is interested, and she shows that side of her (and viceversa) it not only offers the natural role fulfillment, but also it digs deeper into ones identity as someone who is not only attractive, worthy.. but trusted.
And being trusted is a really good feeling, I recently watched that Puss in Boots movie, and the character of Perrito kind of made me realize something I had learned with my own dog, and one of the best feelings in the world, is when the dog lays his head on your body somewhere. In the movie, Perrito used that move, laying his head on the cat, as a therapy to calm anxiety, and it struck me that it worked because the cat felt trusted again, and the anxiety dissipated.
So, I think there's more dimensions to why that manipulation works so well.
That isn't to say that vulnerability, trust and learning to need and feeling needed have no place in a real relationship, but I think it's because we recognize their place in a real relationship, that when offered it, and if portrayed with enough fidelity, it's rather difficult to resist.