A construction like hash(site_name + pw) will generate a secure password as long as password is high entropy and the hash has sufficient bits (e.g., 128+ bits which your standard cryptographic hash will have; just don't use non-crypto hashes like crc32). If you use something like pw = password123 that is easily guessed then a sophisticated attacker may be able to figure out your scheme from seeing your password at a site they control and then brute force your password for all other sites (e.g., if your password for example.com is md5("examplepassword123") it may be possible to reverse it by testing passwords of the form md5(site_name + pw) -- you can generate billions of md5 hashes per second per computer. It is a bad idea relying on an attacker not knowing your password generation scheme -- instead you should rely on your actual passwords having high intrinsic randomness (see Kerckhoffs's principle).
However, if you use something like bcrypt, you'll be fine with a weak password since bcrypt creates a random 128-bit salt which provides more than enough entropy to prevent even the weakest password from being reasonable (assuming the salt used differs between your hashes). Please note you'll have to store this randomly created salt to be able to login again, so at this point it probably would have just been simpler to use a completely randomly generated password for each website you visit.
To give an example with bcrypt using python (in the ipython interpreter):
In [1]: import bcrypt
In [2]: bcrypt.hashpw('amazonpassword123', bcrypt.gensalt(10))
Out[2]: '$2b$10$//rLpdWc/0hljdOf90366u1uaRch7q59AxF0qcodHvDckO1nd..ky'
In [3]: bcrypt.hashpw('amazonpassword123', bcrypt.gensalt(10))
Out[3]: '$2b$10$eUsCtvQ9mvYA2qAEShaqjOqgYogRP4mohEag5bm3Hls10JPSQxU4y'
In [4]: bcrypt.hashpw('amazonpassword123', Out[2])
Out[4]: '$2b$10$//rLpdWc/0hljdOf90366u1uaRch7q59AxF0qcodHvDckO1nd..ky'
Note that hashing the same thing twice with bcrypt generates a completely different hash as they used two different randomly created salts (with 2^10 rounds of hashing); this is why Out[2] != Out[3]. This isn't problematic when checking passwords against a bcrypt hash (as the hash contains the salt), so you can just provide the hash stored in the database to use as the salt and then check that it matches the stored hash; this is why Out[2] == Out[4].
The other issue to worry about is sites silently truncating your password. Using say a full bcrypt hash, where the first seven characters are $2b$<lg(number of rounds)>$ may severely weaken your password. For example, a VPN provided by a well-known vendor I use for work truncates my user password to 8 characters (though allows me to type in much longer passwords -- it just only checks the first 8).
