Python 将数据发送到 MySQL 数据库

Question

我有一个脚本正在运行，每秒更新一次数据库中的某些值。在脚本的开头，我首先连接到数据库:

conn = pymysql.connect(host= "server",
               user="user",
               passwd="pw",
               db="db",
               charset='utf8')
x = conn.cursor()

我在脚本运行期间(大约 30 分钟)保持连接打开

使用这段代码，我每秒更新一次某些值:

query = "UPDATE missionFilesDelDro SET landLat = '%s', landLon='%s',landHea='%s' WHERE displayName='%s'" % (lat, lon, heading, mission)
x.execute(query)
conn.ping(True)

但是现在，当我的 Internet 连接中断时，脚本也会崩溃，因为它无法更新变量。我的连接通常会在一分钟内重新建立。 (脚本在移动的车辆上运行。通过 GSM 调制解调器建立互联网连接)

在更新变量之前每次连接到服务器时重新打开是否更好，以便我可以查看连接是否已建立或者是否有更好的方法？

Answer 1

您可以先 ping 连接，而不是在查询之后，因为如果需要，应该重新连接。

设置:

conn = pymysql.connect(host= "server",
                       user="user",
                       passwd="pw",
                       db="db",
                       charset='utf8')

每一秒:

query = "UPDATE missionFilesDelDro SET landLat = '%s', landLon='%s',landHea='%s' WHERE displayName='%s'" % (lat, lon, heading, mission)
conn.ping()
x = conn.cursor()
x.execute(query)

引用 https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/connections.py#L872

连接仍然有可能在 ping() 之后但在 execute() 之前断开，然后会失败。为了处理你需要捕获错误，类似于

from time import sleep

MAX_ATTEMPTS = 10

# every second:
query = "UPDATE missionFilesDelDro SET landLat = '%s', landLon='%s',landHea='%s' WHERE displayName='%s'" % (lat, lon, heading, mission)
inserted = False
attempts = 0

while (not inserted) and attempts < MAX_ATTEMPTS:
    attempts += 1
    try:
        conn.ping()
        x = conn.cursor()
        x.execute(query)
        inserted = True
    except StandardError: # it would be better to use the specific error, not StandardError
        sleep(10) # however long is appropriate between tries
        # you could also do a whole re-connection here if you wanted

if not inserted:
     # do something
     #raise RuntimeError("Couldn't insert the record after {} attempts.".format(MAX_ATTEMPTS))
     pass