解决gspread结合多进程时出现的AttributeError问题

问题说明 #

使用gspread库结合multiprocessing多进程批量读取Google工作表数据时，触发错误：AttributeError: 'AuthorizedSession' object has no attribute '_auth_request'，无法正常获取目标数据列表。

原代码 #

batch_data = wks.worksheets()
batch_data.pop(-1)
batch_data.pop(-1)
def f(i):
    return i.get("C31:D90")

if __name__ == '__main__':
    p = Pool(33).map(f, batch_data)

完整报错信息 #

multiprocessing.pool.RemoteTraceback: 
"""
Traceback (most recent call last):
  File "/multiprocessing/pool.py", line 125, in worker
    result = (True, func(*args, **kwds))
                    ^^^^^^^^^^^^^^^^^^^
  File "multiprocessing/pool.py", line 48, in mapstar
    return list(map(*args))
           ^^^^^^^^^^^^^^^^
  File "Krona.py", line 12, in f
    return i.get("C31:D90")
           ^^^^^^^^^^^^^^^^
  File "gspread/worksheet.py", line 952, in get
    response = self.client.values_get(
               ^^^^^^^^^^^^^^^^^^^^^^^
  File "gspread/http_client.py", line 236, in values_get
    r = self.request("get", url, params=params)
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/gspread/http_client.py", line 114, in request
    response = self.session.request(
               ^^^^^^^^^^^^^^^^^^^^^
  File "site-packages/google/auth/transport/requests.py", line 526, in request
    self._auth_request
AttributeError: 'AuthorizedSession' object has no attribute '_auth_request'
"""

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "Krona.py", line 16, in <module>
    p = Pool(2).map(f,batch_data)
        ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "multiprocessing/pool.py", line 367, in map
    return self._map_async(func, iterable, mapstar, chunksize).get()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "multiprocessing/pool.py", line 774, in get
    raise self._value
AttributeError: 'AuthorizedSession' object has no attribute '_auth_request'

问题根源 #

multiprocessing创建子进程时会对传入的对象进行pickle序列化，但gspread的Worksheet对象绑定的AuthorizedSession包含无法被序列化的认证状态（如活跃HTTP连接、加密认证令牌），导致子进程中会话对象损坏，触发属性缺失错误。

三种可行解决方案 #

1. 子进程内重新初始化认证客户端 #

不直接传递Worksheet对象，改为传递工作表标题/ID，在每个子进程内部重新创建gspread客户端并获取工作表：

from multiprocessing import Pool
import gspread
from google.oauth2.service_account import Credentials

def init_client():
    # 替换为你的认证范围和密钥文件路径
    scopes = ["https://www.googleapis.com/auth/spreadsheets"]
    creds = Credentials.from_service_account_file("service_account_key.json", scopes=scopes)
    return gspread.authorize(creds)

def fetch_sheet_data(sheet_title):
    client = init_client()
    # 替换为你的目标表格ID
    spreadsheet = client.open_by_key("your_spreadsheet_id")
    worksheet = spreadsheet.worksheet(sheet_title)
    return worksheet.get("C31:D90")

if __name__ == '__main__':
    # 主进程中获取所有需要处理的工作表标题
    main_client = init_client()
    spreadsheet = main_client.open_by_key("your_spreadsheet_id")
    batch_titles = [ws.title for ws in spreadsheet.worksheets()]
    # 移除最后两个工作表
    batch_titles.pop(-1)
    batch_titles.pop(-1)
    
    with Pool(33) as pool:
        results = pool.map(fetch_sheet_data, batch_titles)
    print(results)

2. 用线程池替代多进程 #

如果只是IO密集型操作（如网络请求），线程池不会有pickle序列化问题，因为线程共享父进程的内存空间：

from concurrent.futures import ThreadPoolExecutor

def fetch_data(worksheet):
    return worksheet.get("C31:D90")

if __name__ == '__main__':
    # wks为Spreadsheet对象
    batch_data = wks.worksheets()
    batch_data.pop(-1)
    batch_data.pop(-1)
    
    with ThreadPoolExecutor(max_workers=33) as executor:
        results = list(executor.map(fetch_data, batch_data))
    print(results)

3. 使用gspread原生批量操作API（推荐） #

gspread自带批量获取多个范围数据的接口，无需手动实现多进程/线程，效率更高且完全避免序列化问题：

# wks为Spreadsheet对象
batch_data = wks.worksheets()
batch_data.pop(-1)
batch_data.pop(-1)

# 构造批量获取的范围列表，格式为"工作表名称!单元格范围"
request_ranges = [f"{ws.title}!C31:D90" for ws in batch_data]
# 批量获取所有数据
results = wks.batch_get(request_ranges)
print(results)

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