The trouble is that the random factor overwhelms the AI's built in personalities. Eg. Aggressiveness of AI leaders ranges from 3 to 8, but at the start of every game it gets a random modifier added to it that can skew +2 or -2 either way. The vast majority of leaders have personality attributes no more extreme than 4 or 6, so the random modifier can cause them to switch positions. On top of that, I don't think there's a Civ out there that won't backstab once they start their UU spam, no matter how good neighbours you've been.
Anyway, i did actually have a game where Elizabeth was actually friendly - possibly because there was a powerful and belligerent Ethiopian empire between us, but also perhaps due to the "personality randomisation". She actually asked me if i wanted to be friends, and did so repeatedly.
I also just had a game where Atilla the Hun was friendly. OK, we weren't immediate neighbours, but i was still waiting for the backstab/denunciation/threats that never came. In between us, lay Queen Isabella, who was an absolute jihad mongering headcase. She'd wiped out the Egyptians, the Greeks and had taken Atilla's Court by 1600AD. Naturally, she attacked me on a fairly regular basis once she got her Conquistadors. Hint for you lady - check the terrain first. On a hill over a river with a mountain behind and a fort to one side does not an easy city make!