Mysql Connection, one or many?

Question

I'm writing a script in python which basically queries WMI and updates the information in a mysql database. One of those "write something you need" to learn to program exercises.

In case something breaks in the middle of the script, for example, the remote computer turns off, it's separated out into functions.

Query Some WMI data
Update that to the database
Query Other WMI data
Update that to the database

Is it better to open one mysql connection at the beginning and leave it open or close the connection after each update?

It seems as though one connection would use less resources. (Although I'm just learning, so this is a complete guess.) However, opening and closing the connection with each update seems more 'neat'. Functions would be more stand alone, rather than depend on code outside that function.

Answer 1

I don't think that there is "better" solution. Its too early to think about resources. And since wmi is quite slow ( in comparison to sql connection ) the db is not an issue.

Just make it work. And then make it better.

The good thing about working with open connection here, is that the "natural" solution is to use objects and not just functions. So it will be a learning experience( In case you are learning python and not mysql).

Answer 2

Think for a moment about the following scenario:

for dataItem in dataSet:
    update(dataItem)

If you open and close your connection inside of the update function and your dataSet contains a thousand items then you will destroy the performance of your application and ruin any transactional capabilities.

A better way would be to open a connection and pass it to the update function. You could even have your update function call a connection manager of sorts. If you intend to perform single updates periodically then open and close your connection around your update function calls.

In this way you will be able to use functions to encapsulate your data operations and be able to share a connection between them.

However, this approach is not great for performing bulk inserts or updates.

Answer 3

"However, opening and closing the connection with each update seems more 'neat'. "

It's also a huge amount of overhead -- and there's no actual benefit.

Creating and disposing of connections is relatively expensive. More importantly, what's the actual reason? How does it improve, simplify, clarify?

Generally, most applications have one connection that they use from when they start to when they stop.

Answer 4

Useful clues in S.Lott's and Igal Serban's answers. I think you should first find out your actual requirements and code accordingly.

Just to mention a different strategy; some applications keep a pool of database (or whatever) connections and in case of a transaction just pull one from that pool. It seems rather obvious you just need one connection for this kind of application. But you can still keep a pool of one connection and apply following;

• Whenever database transaction is needed the connection is pulled from the pool and returned back at the end.
• (optional) The connection is expired (and of replaced by a new one) after a certain amount of time.
• (optional) The connection is expired after a certain amount of usage.
• (optional) The pool can check (by sending an inexpensive query) if the connection is alive before handing it over the program.

This is somewhat in between single connection and connection per transaction strategies.