经常读取后，PyMySQL抛出'BrokenPipeError'(PyMySQL throws 'BrokenPipeError' after making frequent reads)

 我写了一个脚本来帮助我使用数据库。 具体来说，我正在尝试使用磁盘上的文件，并将此工作的结果添加到我的数据库。 我复制了下面的代码，但删除了与我的数据库无关的大部分逻辑，试图保持这个问题的广泛和有用。 
 
 我使用代码对文件进行操作并将结果添加到数据库中，覆盖与我正在处理的文件具有相同标识符的任何文件。 后来，我修改了脚本以忽略已经添加到数据库中的文档，现在无论何时运行它我都会收到错误： 
 
pymysql.err.OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")
 
 似乎服务器拒绝了请求，可能是因为我写的代码很差？ 我注意到错误总是发生在文件列表中的相同位置，不会改变。 如果我重新运行运行代码，用仅列出程序崩溃的文件的列表替换文件列表，它可以正常工作。 这让我觉得在发出一定数量的请求之后，数据库才刚刚结束。 
 
 我在OS X上使用Python 3和MySQL Community Edition版本14.14。 
 
 代码（剥离了与数据库无关的东西）： 
 
import pymysql

# Stars for user-specific stuff
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='*******',
                             db='*******',
                             use_unicode=True, 
                             charset="utf8mb4",
                             )
cursor = connection.cursor()

f_arr = # An array of all of my data objects

def convertF(file_):
    # General layout: Try to work with input and add it the result to DB. The work can raise an exception
    # If the record already exists in the DB, ignore it
    # Elif the work was already done and the result is on disk, put it on the database
    # Else do the work and put it on the database - this can raise exceptions
    # Except: Try another way to do the work, and put the result in the database. This can raise an error
    # Second (nested) except: Add the record to the database with indicator that the work failed

    # This worked before I added the initial check on whether or not the record already exists in the database. Now, for some reason, I get the error:
    # pymysql.err.OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")

    # I'm pretty sure that I have written code to work poorly with the database. I had hoped to finish this task quickly instead of efficiently.
    try:
        # Find record in DB, if text exists just ignore the record
        rc = cursor.execute("SELECT LENGTH(text) FROM table WHERE name = '{0}'".format(file_["name"]))
        length = cursor.fetchall()[0][0] # Gets the length
        if length != None and length > 4:
            pass
        elif ( "work already finished on disk" ): 
            # get "result_text" from disk
            cmd = "UPDATE table SET text = %s, hascontent = 1 WHERE name = %s"
            cursor.execute(cmd, ( pymysql.escape_string(result_text), file_["name"] ))
            connection.commit()
        else:
            # do work to get result_text
            cmd = "UPDATE table SET text = %s, hascontent = 1 WHERE name = %s"
            cursor.execute(cmd, ( pymysql.escape_string(result_text), file_["name"] ))
            connection.commit()
    except:
        try: 
            # Alternate method of work to get result_text
            cmd = "UPDATE table SET text = %s, hascontent = 1 WHERE name = %s"
            cursor.execute(cmd, ( pymysql.escape_string(result_text), file_["name"] ))
            connection.commit()
        except:
            # Since the job can't be done, tell the database
            cmd = "UPDATE table SET text = %s, hascontent = 0 WHERE name = %s"
            cursor.execute(cmd, ( "NO CONTENT", file_["name"]) )
            connection.commit()

for file in f_arr:
    convertF(file)

I have written a script to help me work with a database. Specifically, I am trying to work with files on disk and add the result of this work to my database. I have copied the code below, but removed most of the logic which isn't related to my database to try to keep this question broad and helpful.
 
I used the code to operate on the files and add the result to the database, overwriting any files with the same identifier as the one I was working on. Later, I modified the script to ignore documents which have already been added to the database, and now whenever I run it I get an error:
 
pymysql.err.OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")
 
It seems like the server is rejecting the requests, possibly because I have written my code poorly? I have noticed that the error always occurs at the same place in the list of files, which doesn't change. If I re-run run the code, replacing the file list with a list of only the file on which the program crashes, it works fine. This makes me think that after making a certain number of requests, the database just bottoms out.
 
I'm using Python 3 and MySQL Community Edition Version 14.14 on OS X. 
 
Code (stripped of stuff that doesn't have to do with the database):
 
import pymysql

# Stars for user-specific stuff
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='*******',
                             db='*******',
                             use_unicode=True, 
                             charset="utf8mb4",
                             )
cursor = connection.cursor()

f_arr = # An array of all of my data objects

def convertF(file_):
    # General layout: Try to work with input and add it the result to DB. The work can raise an exception
    # If the record already exists in the DB, ignore it
    # Elif the work was already done and the result is on disk, put it on the database
    # Else do the work and put it on the database - this can raise exceptions
    # Except: Try another way to do the work, and put the result in the database. This can raise an error
    # Second (nested) except: Add the record to the database with indicator that the work failed

    # This worked before I added the initial check on whether or not the record already exists in the database. Now, for some reason, I get the error:
    # pymysql.err.OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")

    # I'm pretty sure that I have written code to work poorly with the database. I had hoped to finish this task quickly instead of efficiently.
    try:
        # Find record in DB, if text exists just ignore the record
        rc = cursor.execute("SELECT LENGTH(text) FROM table WHERE name = '{0}'".format(file_["name"]))
        length = cursor.fetchall()[0][0] # Gets the length
        if length != None and length > 4:
            pass
        elif ( "work already finished on disk" ): 
            # get "result_text" from disk
            cmd = "UPDATE table SET text = %s, hascontent = 1 WHERE name = %s"
            cursor.execute(cmd, ( pymysql.escape_string(result_text), file_["name"] ))
            connection.commit()
        else:
            # do work to get result_text
            cmd = "UPDATE table SET text = %s, hascontent = 1 WHERE name = %s"
            cursor.execute(cmd, ( pymysql.escape_string(result_text), file_["name"] ))
            connection.commit()
    except:
        try: 
            # Alternate method of work to get result_text
            cmd = "UPDATE table SET text = %s, hascontent = 1 WHERE name = %s"
            cursor.execute(cmd, ( pymysql.escape_string(result_text), file_["name"] ))
            connection.commit()
        except:
            # Since the job can't be done, tell the database
            cmd = "UPDATE table SET text = %s, hascontent = 0 WHERE name = %s"
            cursor.execute(cmd, ( "NO CONTENT", file_["name"]) )
            connection.commit()

for file in f_arr:
    convertF(file)

原文：https://stackoverflow.com/questions/41645823