Price is not a factor in this decision.
I absolutely love Connecticut College. The liberal arts program there and the music program there interest me a lot. The location is amazing and I have family in both NYC and Boston so I could go during weekends. I know i’d be very happy there. I also love the internship program. My only concern is the prestige factor. Being an international student, nobody knows about Connecticut College, and mostly everybody knows St. Andrews. However, I just can’t bring myself to want to be in Scotland for the next 4 years.
My question is, is it stupid to give up a prestigious school for a lesser-known and lower ranked school? I know that this will mean my happiness for 4 years, but will I regret it in the future? For instance when I’m looking for jobs?
Will you be looking for jobs in the US or back in your home country? Back home, do people care about the name of an international college/university, or does a degree from an American school look good regardless? For instance, in China, there is a lot of pressure (for those who can afford to do so) to attend an American school: any American school! In other parts of the world, people only know Harvard and Yale and Stanford and MIT (plus a few others).
As I am sure you know, Connecticut College is an excellent school. Even here in the US, most people are not familiar with small colleges, not even the very prestigious ones (Williams, Amherst, Carleton). I always think of Connecticut College as just a step below these elite schools (in terms of perception, not actual education quality). In fact, since you “absolutely love” Connecticut College, I’m surprised that this decision is so difficult for you. Go there! I cannot tell if the “prestige factor” is a reality (you are worried that you won’t be able to find a job) or if it is more about people not recognizing the name of your school.
If it is the latter, get used to it! One chooses a small liberal arts college for the high-quality education, the rigorous education, the small class sizes, the faculty that is dedicated to teaching undergraduates, the accessibility of that faculty and the opportunity to work closely with them. Those of us who attend/attended a LAC know that the average person is not going to recognize the name of our school, and that’s okay. Those who know know. You know? 
" However, I just can’t bring myself to want to be in Scotland for the next 4 years".
Why do you feel this way? Need to know this to answer your question.
Also sounds like you value having your family relatively close. But I wonder how often you will actually visit them? Most college students don’t take weekend trips all that often - they are usually hanging with their friends in college, going to events on campus, and studying.