Was it really a beautiful story for most of them, or were they just used for diplomatic leverage?
Very much one of diplomatic leverage.
A prime example is Queen Isabella I of Castile, the Isabella of Christopher Columbus' story.
She had a strict upbringing despite being 3rd in line for the throne. When the 2nd person (her brother) died, she was caught in a war against her own brother Henry (who was King) between nobles who wanted her named as next in the line of succession to the throne instead of Henry's daughter. Eventually a compromise was made and Isabella was named heir to the throne but could not marry without Henry's consent and without her approval as well.
At the age of SIX, she was betrothed to Ferdinand, son of the King of Navarre. However, when the King of Navarre (John II) inherited the Kingdom of Aragon, Henry wanted to betroth Isabella to Charles, John II's elder son. John II was outraged (he clashed with his son Charles) at the secret arrangement and had Charles thrown in prison.
Attempts were made to marry Isabella off to various nobility in Portugal and even England in order to secure an inheritance but Isabella only agreed to marry her first betrothed, Ferdinand, who was also her second cousin and thus required Papal approval..
Confused enough? Now imagine her children, two who became Queens of Portugal, one who became Queen of England, and one who became Queen of Castile (whos son was Charles V who became Emperor of the Holy Roman Empire AND the first king to rule Castile, Leon, and Aragon outright and thus the first King of Spain)
Yeah, it was no fairy tale