Let us imagine you are creating a dynamic web site in your programming language of choice.
When a user comes to visit your site, they make a request to http://name-of-your-site.com and this is passed to your server.
When the request arrives at port 80, it is collected by your HTTP server, which is probably Apache, but might be LightHttpd or any other HTTP server. That will recieve the request and decide what to do with it.
Now imagining that your site is written in python, it will be stored as a bunch of .py files somewhere and so the request needs to be passed on to the python runtime with instructions for what file to run and where to return the output from that file. This is the role of mod_python - taking requests from the server and handing them to the runtime. Most mods also handle thread pooling - suppose you have twenty requests over a minute, if each gets handed to the python runtime in a simple fashion then you will have twenty python threads all starting up, running together and then dying off as the process is complete. Typically Apache mods will keep a few threads up and running, to save on startup time and just pass new requests to one of the existing threads so that when it has finished one request it gets passed another one by the interface. CGI containers do the same job in a slighly different way, the reason you might choose one over the other is likely to be related to what HTTP server you are using ( mod_python is designed for Apache, for example, something like FastCGI is used more with LightHttpd ) and to performance considerations. If you are using something like FastCGI you would then potentially need a second layer of interface between the CGI Container and the programming language runtime.
So the layers you are working with look a bit like this:
HTTP Server-> CGI Layer -> Programming Language Runtime -> your code
Apache -> mod_python -> Python Runtime -> index.py
LightHttpd -> FastCGI+python_cgi -> Python Runtime -> index.py
Obviously, I have used Python as an example here, but you can find equivalent mods and cgi containers for most mainstream languages ( and a lot of esoteric ones ) and the Http stack you are working with will be broadly similar in most cases.