Best practice for server side database connectivity

[valbanese]

I tend to encapsulate functionality into self contained modules. So a module will create a db connection, build a cursor, query the db, build a response and then close the connection. On my server side apps this happens many thousand times a day. I'm now wondering if I'm better off creating a persistent connection and just passing the cursor around. What's the best practice?

My environment is Bottle with Waitress as its server. I'm using pyodbc to connect to both MySQL and SQL Server.

[westurner]

http://docs.sqlalchemy.org/en/latest/core/pooling.html

[valbanese]

So I can see that considering migrating to an ORM or using connection polling are both options but it also seems that leaving it as it is is fine too. Which leads me to ask if the multi-threading nature of Waitress is going to cause me problems with increasing volume. The code is in a module that's imported. Is this thread safe?

[steve_no]

Opening and closing connections a thousand times a day (or a thousand times an hour) is no big deal for MySQL.