MySQL, should I stay connected or connect when needed?

Question

I have been logging temperatures at home to a MySQL database (read 10 sensors in total every 5 minutes), and have been using Python, but I am wondering something...

Currently when I first run my program, I run the normal connect to MySQL, which is only run once.

db = MySQLdb.connect(mysql_server, mysql_username, mysql_passwd, mysql_db)
cursor = db.cursor()

Then I collect the data and publish it to the database successfully. The script then sleeps for 5 minutes, then starts again and collects and publishes the data again and so on. However, I only connect once, and I don't ever disconnect; it just keeps going in a loop. I only disconnect if I terminate the program.

Is this the best practice? That is, keeping the connection open all the time to the MySQL server, or should I disconnect after I have done a insert/commit?

The reason I ask: every now and then, I have to restart the script because maybe my MySQL server has gone offline or some other issue. Should I:

• Keep doing what I am doing and just handle any MySQL database disconnections with a reconnect,
• Put it in the crontab to collect data every five minutes and have no loop and no sleep, or
• Something else?

Answer 1

MySQL servers are configured to handle a fixed limited number of connections. It's not a good practice to tie up a connection that you are not using constantly. So typically you should close the connection as soon as you are done with it, and reconnect only when you need it again. MySQLdb's connections are context mangagers, so you could use the with-statement syntax to make closing the connection automatic.

connection = MySQLdb.connect(
    host=config.HOST, user=config.USER,
    passwd=config.PASS, db=config.MYDB, )
with connection as cursor:
    print(cursor)
    # the connection is closed for you automatically 
    # when Python leaves the `with-suite`.

For robustness, you might want to use try..except to handle the case when (even on the first run) connect fails to make a connection.

Having said that, I would just put it in a crontab entry and dispense with sleeping.