MySQL，我应该保持连接状态还是需要时连接? [英] MySQL, should I stay connected or connect when needed?

问题描述

我一直在将温度记录在一个MySQL数据库中(每5分钟总共读取10个传感器)，并且一直在使用Python，但是我想知道一些事情...

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...

当前，当我第一次运行程序时，我会运行到MySQL的普通连接，该连接仅运行一次.

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()

然后，我收集数据并将其成功发布到数据库.然后，脚本休眠5分钟，然后再次启动，然后再次收集和发布数据，依此类推.但是，我只连接一次，而从不断开连接.它只是不断循环.我只有在终止程序后才断开连接.

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.

这是最佳做法吗?也就是说，保持与MySQL服务器的连接一直保持打开状态，还是在执行插入/提交操作后断开连接?

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?

我问的原因:有时我必须重新启动脚本，因为也许我的MySQL服务器已脱机或出现其他问题.我应该:

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:

• 继续做我正在做的事情，并通过重新连接来处理所有MySQL数据库断开连接，
• 将其放在crontab中以每五分钟收集一次数据，并且没有循环且没有睡眠，或者
• 还有别的吗?

推荐答案

MySQL服务器配置为处理固定数量的固定连接.绑扎您不经常使用的连接不是一个好习惯.因此，通常您应该在完成连接后立即关闭它，并仅在再次需要它时才重新连接. MySQLdb的连接是上下文管理器，因此您可以使用with-statement语法自动关闭连接.

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`.

出于鲁棒性考虑，您可能希望使用try..except来处理connect(即使在第一次运行时)无法建立连接的情况.

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.

话虽如此，我只是将其放在crontab条目中，省去了睡眠.

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

这篇关于MySQL，我应该保持连接状态还是需要时连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！