Even though Petraeus resigned from his position with the CIA, he still deserves to keep it, because his personal affairs have nothing to do with his professional abilities. He did his job well, and the things that happened in his marriage didn't interfere with his obligations to the country. While his actions may have tarnished his personal reputation, this should be separate from the way the public perceives him professionally.
Yes I think David Patraeus should get his job at the CIA back because his affair had not impact on how he did his job. It did not affect his performance in any way that I heard of. The affair is something him and his wife should work out in the privacy of their own home, not in the media spotlight.
I think having an affair is wrong, but it is the person's own business. So long as it is not interfering with their work or national security, I don't see a problem with it. General Petraeus was a decorated veteran and was a very successful person who is now marred by some indiscretions.
Although the affair probably did not affect his work, the reality is that now he would be under a total microscope, and all people would focus on is his infidelity rather than his work as a leader of the organization. It is a shame, but he has lost the standing to hold such a position.