How should I establish and manage database connections in a multi-module Python app?
MySQL connections are relatively fast, so this might not be a problem (i.e. you should measure). Most other databases take much more resources to create a connection.
Creating a new connection when you need one is always the safest, and is a good first choice.  Some db libraries, e.g. SqlAlchemy, have connection pools built in that transparently will re-use connections for you correctly.
If you decide you want to keep a connection alive so that you can re-use it, there are a few points to be aware of:

Connections that are only used for reading are easier to re-use than connections that that you've used to modify database data.
When you start a transaction on a connection, be careful that nothing else can use that connection for something else while you're using it.
Connections that sit around for a long time get stale and can be closed from underneath you, so if you're re-using a connection you'll need to check if it is still "alive", e.g. by sending "select 1" and verifying that you get a result.

I would personally recommend against implementing your own connection pooling algorithm.  It's really hard to debug when things go wrong.  Instead choose a db library that does it for you.
We have a Python application with over twenty modules, most of which are shared by several web and console applications.
I've never had a clear understanding of the best practice for establishing and managing database connection in multi module Python apps.  Consider this example:
I have a module defining an object class for Users. It has many defs for creating/deleting/updating users in the database.  The users.py module is imported into a) a console based utility, 2) a web.py based web application and 3) a constantly running daemon process.
Each of these three application have different life cycles.  The daemon can open a connection and keep it open.  The console utility connects, does work, then dies.  Of course the http requests are atomic, however the web server is a daemon.
I am currently opening, using then closing a connection inside each function in the Users class.  This seems the most inefficient, but it works in all examples.  An alternative used as a test is to declare and open a global connection for the entire module.  Another option would be to create the connection at the top application layer and pass references when instantiating classes, but this seems the worst idea to me.
I know every application architecture is different.  I'm just wondering if there's a best practice, and what it would be?