I've never stayed at any of the airport hotels but I do tend to stay in the Silicon Valley when I accompany DH for work. I normally commute into the downtown area from there.
Using the BART from the airport, it will take you about 20 - 30 minutes to get to one of the stops along Market Street (downtown area). I think tickets are about $5.50 one way or you could get a BART card/pass and top-up. I think it might be cheaper using the card; but don't quote me on that. From Market to get to Fisherman's Wharf, you'd catch one of the Muni buses or the trolley or the F-line tram. It could take an additional 30 minutes or so to get to Fisherman's Wharf depending on which transport you catch.
I have used the BART to get myself to Millbrae and then changed to the CalTrain and vice versa depending on where I want to get to in the city. Millbrae is the transit station where you can catch the BART or the CalTrain. So if you are looking for accommodation near the airport, it might be worth checking out a hotel close enough to walk to the Millbrae station. Millbrae might be your best station to find accommodation near so that you have the choice of the BART or the CalTrain.
I normally take the CalTrain into the city because that's usually where my normal accommodation is close to. From memory, Millbrae is in Zone 2; so a return ticket will cost about $10 per day.
If you found somewhere in San Bruno close to the CalTrain, I think the return ticket is $6. San Bruno is the next station closer towards San Francisco from Millbrae and is in Zone 1, hence the cheaper price. But you won't have the BART as a transport option from there.
Millbrae/San Bruno on the Caltrain will take about 30 minutes to get to San Francisco.
The CalTrain depot is at 4th and King Street. It's on the southern end of the city and from there you'll need to catch MUNI transport in...either the light rail or the MUNI bus. There are buses from the 4th and King Street station that will take you to all areas within San Francisco. I will either catch the bus from here to Fisherman's Wharf; or the bus to Downtown. Again, you'll need to factor about 20 - 30 minutes travel.
Hope this helps.