Love Cabo! I was there in September of 2011 and I don't recall it being a party area at all. Someone famous was in town doing something but if you don't want to be a part of that scene you can definitely avoid it. We spent a lot of time hanging out in the resort pool (RIU Santa Fe), eating some amazing food, and sun bathing. It was very relaxing and fun. We did go into town once to do some shopping and then hit up The Corner Bar for a Mamarita, but since you're pregnant you'll probably want to skip that part. :)
If you plan to stay at one of those large all-inclusive resorts my recommendation is to find one that has room service 24/7. RIU Santa Fe did not and all of their food places shut down around 10. If you wanted to eat after that you were limited to the fridge in the sport's club that consisted of hot dogs, deli meat sandwiches, nachos, and a small selection of fruit. Not exactly what I want to munch on late at night when I've got the munchies, especially while pregnant.
As for water, if you are at a large resort don't sweat it. You can get ice and drink whatever you want as most have their own water filtration systems. You can ask to see what they use, but I've never been to a resort that didn't have a sophisticated system in place. I've been to Mexico a dozen times and never once got sick from drinking something with ice at the resort. Drinking tap water in town is not advised, however!