Single database connection init for all threads

[Voltron]

What would be the correct way to create a global db connection that is
usable in my forms, page handlers e.t.c

I am doing this at the moment

def connect_db(thread_index):
# create db connection
cherrypy.thread_data.db = db

cherrypy.engine.subscribe('start_thread', connect_db)

I can then use "cherrypy.thread_data.db" wherever the db is needed.
This creates a connection for every started thread, 10 being the
default, is there a better way to create one db connection for all the
threads?


Thanks

[Gloria W]

Hi there,

I am not clear about what "db" contains. Does it contain the DSN (or
connect string)? If so, this is fine, because you subsequently call
something like:
dbconn = some_database_engine.open(DSN)

and DSN could come from a common config file used by all modules.

If the db contains the contents of "dbconn" as shown above, there is a
danger in this method:

- the db connection may expire, and close, due to idle time or some
other event.
- since it is global, you now hold a reference which points to nothing.
- your next database operation will fail.
- you have no built-in way to close and reopen this glabl db connection.

Assuming the latter, I'll expound. If the former applies, please skip
this part.

I've discovered that it is best to simply open and close the connection
as you need it. Most ORMs do their own connection caching, and will
reuse connections internally, making it unnecessary for you to try to do
so. if you have a huge amount of connections coming in regularly, you
may want to consider an external tool to do connection pooling (such as
pgpool for Postgresql). In either case, you generally do not have to
worry about connection pooling or efficiency in your code. It is done
elsewhere, making your open() and close() requests more efficient.

Gloria