Supergirl Pregnant

Correct me if I'm wrong. (I'm really not sure) But didn't they postpone the season 6 premiere until she would be through her pregnancy?

If so then maybe the cancellation is for another reason

Sex is usually unnecessary in film and television, yeah. Usually they're just throwing in gratuitous nudity to titillate viewers (who I guess have no recourse to such things in real life). It doesn't give us new character information or plot points. It's the same with action scenes that just drag on and on without meaning anything or doing anything other than just "look at the flashies!" (*cough* Anakin vs. Obi-Wan *cough* MCU *cough, cough*)

One of the better sex scenes I've seen was in A Very Secret Service - a French Netflix comedy about spies. It actually uses a couple sex scenes to advance plot and character development instead of just "we couldn't think of a way to show these two in love, so here they are in bed".

As to the specific trope you're talking about, the alien or bizarre pregnancy, I think I understand it in terms of its creep factor. Pregnancy and birth is something that remains as unsettling as it is beautiful, so jacking up the strangeness and the allure (that "glow") into these tropes gives us something strange, attractive, and uncanny. It's very unsettling. One of the reasons Alien works is because it takes our fears surrounding birth and pregnancy and jacks it up. I don't like the idea of unwanted pregnancies, though, and it's not something that arouses me or interests me in an erotic or romantic fashion, but in an eerie way; it's good horror, to me anyway.