Why does this work?

Air molecules are constantly pushing into things, in every direction imaginable. In other words, air has pressure. If you look at your thumbnail, you have the equivalent of around 1 kg of weight pushing down on that body part alone!

Inside the cup there was no air, so the weight inside the cup was coming only from the water. Depending on the size of your cup, the weight of the water may have been around 250g. The air below the card was pushing up into the card. The upwards air pressure was much greater than that of the pressure of water pushing towards the ground - keeping the water in the cup.

Air locks are used by deep sea divers leave their submersible vehicles. The air inside the air lock is compressed at the same pressure as that of the water outside the air lock.