使用Pandas ExcelWriter添加记录后同函数无法返回新增记录的问题

问题场景 #

使用Pandas ExcelWriter向Excel文件新增会员记录，写入完成后立即查询新增记录，返回的却是新增前的最后一条记录；怀疑文件未关闭手动添加writer.close()后，触发ValueError: I/O operation on closed file错误。单独调用查询函数却能正常找到新增记录。

初始测试代码：

def add_mem(self, fn, ln, stadd, city, zip):
    __new_index = int(self.find_memend()) + 3
    new_mem_dict = pd.DataFrame({"Member_ID" : [int(__new_index)],
                                      "Fname" : [fn],
                                      "Lname" : [ln],
                                      "Street_Add" : [stadd],
                                      "City" : [city],
                                      "Zip" : [zip]})
    new_mem_dict = new_mem_dict.set_index("Member_ID")
    new_mem_dict['Zip'] = new_mem_dict['Zip'].str.pad(width = 5, fillchar='0')
    with pd.ExcelWriter(self.excel_file, mode = 'a', if_sheet_exists='overlay') as writer:
        new_mem_dict.to_excel(writer, sheet_name='Members', startrow=writer.sheets['Members'].max_row, header=False)
    _members_added = (self.find_memend())
    _newmem = self.search_memID(_members_added)
    return print('{}\nwas added to database'.format(_newmem))

添加writer.close()后的报错代码：

def add_mem(self, fn, ln, stadd, city, zip):
    __new_index = int(self.find_memend()) + 3
    new_mem_dict = pd.DataFrame({"Member_ID" : [int(__new_index)],
                                      "Fname" : [fn],
                                      "Lname" : [ln],
                                      "Street_Add" : [stadd],
                                      "City" : [city],
                                      "Zip" : [zip]})
    new_mem_dict = new_mem_dict.set_index("Member_ID")
    new_mem_dict['Zip'] = new_mem_dict['Zip'].str.pad(width = 5, fillchar='0')
    with pd.ExcelWriter(self.excel_file, mode = 'a', if_sheet_exists='overlay') as writer:
        new_mem_dict.to_excel(writer, sheet_name='Members', startrow=writer.sheets['Members'].max_row, header=False)
        writer.close()
    _members_added = (self.find_memend())
    _newmem = self.search_memID(_members_added)
    return print('{}\nwas added to database'.format(_newmem))

报错栈：

Traceback (most recent call last):
  File "c:\Users\Mike\OneDrive\Documents\Apprenticeship Files\Python project\Library_Mods.py", line 156, in <module>
    Lib_Members().add_mem("Alexis", "Griffin", "470 Washington Avenue",
  File "c:\Users\Mike\OneDrive\Documents\Apprenticeship Files\Python project\Library_Mods.py", line 133, in add_mem
    with pd.ExcelWriter(self.excel_file, mode = 'a', if_sheet_exists='overlay') as writer:
  File "C:\Users\Mike\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\excel\_base.py", line 1322, in __exit__
    self.close()
  File "C:\Users\Mike\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\excel\_base.py", line 1326, in close
    self._save()
  File "C:\Users\Mike\AppData\Local\Programs\Python\Python311\Lib\site-packages\pandas\io\excel\_openpyxl.py", line 109, in _save
    self.book.save(self._handles.handle)
  File "C:\Users\Mike\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\workbook\workbook.py", line 386, in save
    save_workbook(self, filename)
  File "C:\Users\Mike\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\writer\excel.py", line 291, in save_workbook
    archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\Mike\AppData\Local\Programs\Python\Python311\Lib\zipfile.py", line 1305, in __init__
    self.start_dir = self.fp.tell()
                     ^^^^^^^^^^^^^^
ValueError: I/O operation on closed file

错误原因分析 #

1. 手动调用writer.close()导致重复关闭：with上下文管理器会在代码块执行完毕后自动调用writer.close()完成文件保存与关闭，手动提前调用后，上下文管理器退出时会再次执行关闭操作，触发文件已关闭的I/O错误。
2. 查询时读取的是缓存数据：find_memend()和search_memID()函数内部可能是在类初始化时读取了Excel文件并缓存了数据，写入新记录后未重新读取文件，导致查询到的是旧数据；而单独调用时重新读取了文件，所以能找到新增记录。

解决办法 #

1. 移除手动的writer.close()调用 #

保留with语句的自动管理逻辑，无需手动关闭，代码恢复到初始版本即可。

2. 确保查询函数读取最新文件数据 #

修改find_memend()和search_memID()函数，让它们每次调用时都重新从Excel文件读取数据，而不是使用缓存。示例如下：

修改find_memend()：

def find_memend(self):
    # 每次调用都重新读取Excel文件
    df = pd.read_excel(self.excel_file, sheet_name='Members', index_col='Member_ID')
    return df.index.max()

修改search_memID()：

def search_memID(self, mem_id):
    df = pd.read_excel(self.excel_file, sheet_name='Members', index_col='Member_ID')
    return df.loc[mem_id]

修改后的完整add_mem函数 #

def add_mem(self, fn, ln, stadd, city, zip):
    __new_index = int(self.find_memend()) + 3
    new_mem_dict = pd.DataFrame({"Member_ID" : [int(__new_index)],
                                      "Fname" : [fn],
                                      "Lname" : [ln],
                                      "Street_Add" : [stadd],
                                      "City" : [city],
                                      "Zip" : [zip]})
    new_mem_dict = new_mem_dict.set_index("Member_ID")
    new_mem_dict['Zip'] = new_mem_dict['Zip'].str.pad(width = 5, fillchar='0')
    # 保留with语句，无需手动close
    with pd.ExcelWriter(self.excel_file, mode = 'a', if_sheet_exists='overlay') as writer:
        new_mem_dict.to_excel(writer, sheet_name='Members', startrow=writer.sheets['Members'].max_row, header=False)
    # 此时find_memend会读取最新文件数据
    _members_added = (self.find_memend())
    _newmem = self.search_memID(_members_added)
    return print('{}\nwas added to database'.format(_newmem))

内容的提问来源于stack exchange，提问作者Absolem TheGreat