Sniping in slower-paced single player games is nothing like sniping in a fast-paced online multiplayer game. I think you've discovered that already.
Some maps are better suited to the Sniper than others. For instance, 2Fort makings sniping very difficult. You wouldn't think so given the setup, but it's easy for Scouts and Soldiers to double jump and rocket jump, not to mention an opposing Sniper shooting you.
One of the best maps for the Sniper is Doublecross. You do need to watch out for opposing Snipers and Spies, but it also gives you a GREAT place to shoot from.
Something else to consider is that you need to move in and out when sniping. Charge your rifle up, quickly dodge out from cover, shoot and then take cover again. That sounds difficult, especially since you need to aim, but that's why you charge your rifle ahead of time. Even if you don't get a head shot, you'll still severely injure them (and might kill a Scout regardless).
DON'T use the bow if you're new to playing a Sniper. It's difficult to aim and hit enemies properly. It also does LESS damage overall. It's possible to survive multiple arrow shots (mostly for the Heavy), but a fully charged rifle shot will ALWAYS kill if you hit them in the head and as said before, it'll drop someone's health to nearly zero if you get a body shot instead.
You'll probably get a Razorback (if you don't already have one) at some point. Now some players will say it's useless and you should use your SMG or jarate instead. While those are good to have (the latter is really good for revealing spies), the Razorback prevents the Spy from making ONE back stab.
To clarify, if you're using a Razorback and the Spy attempts a back stab, he won't hurt you at all. You'll know the Spy has tried to back stab you because you'll hear your Razorback break apart. After that happens, you can get back stabbed as normal.
Believe it or not, the protection the Razorback gives, even if it's only for one back stab, is very useful. Spies love back stabbing Snipers since when aiming, Snipers have tunnel vision (i.e. they only see their scope and are blind to what's going on around them).
Just so you know, you'll get a new razorback when respawning or simply using a dispenser.
I always use a Razorback. If I need to do close-quarters combat, I'll use my knife. I think I gave you a knife called the "Tribalman's Shiv." It does a little less damage than the regular knife ("Kukri"), but it causes the enemy to bleed, thereby causing damage even after you've cut them. Even better, it helps reveal Spies, since they'll still visibly bleed when cloaked.
Playing a Sniper can be awesome, but you need patience, steady aiming skills and the ability to move quickly. I've played the Sniper a lot and there are nights I simply "don't have it in me" to play it well whereas some nights I kick ass.
It's all very situational. As I've said before, the map can make a HUGE difference.
Keep trying and you'll probably get the hang of it sooner or later.
