使用Supabase-py批量取数时遭遇httpx.ReadTimeout问题求助

Supabase表内有375000条数据，我通过循环调用API批量获取所有数据的ref_id，单次API请求限制为20000条。但获取到约200000条数据后，总会触发httpx.ReadTimeout: The read operation timed out错误，最多仅能获取到240000条数据。已尝试调整循环后的等待时间，修改单次请求的条数（10000、30000、50000），但无论哪种设置，都会在获取到150000-200000条左右时卡住。

代码实现 #

existing_search_result = supabase.table('vehicles').select('ref_id', count='exact').order('id', desc=False).execute()
existing_items = []
range_step = len(existing_search_result.data)
total_existing_items = existing_search_result.count
print(total_existing_items)
while len(existing_items) < total_existing_items:
    try:
        existing_items += (
            supabase.table(
                'vehicles'
            ).select('ref_id')
            .order('id', desc=False)
            .range(range_start, range_start + range_step)
            .execute()
        ).data
        range_start += range_step
    except Exception as e:
        logging.exception(e)
    print(range_start, len(existing_items))
    time.sleep(0.30)

错误日志 #

2022-10-23 21:04:14,168:ERROR - The read operation timed out
Traceback (most recent call last):
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpcore/_exceptions.py", line 8, in map_exceptions
    yield
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpcore/backends/sync.py", line 26, in read
    return self._sock.recv(max_bytes)
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/ssl.py", line 1226, in recv
    return self.read(buflen)
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/ssl.py", line 1101, in read
    return self._sslobj.read(len)
socket.timeout: The read operation timed out

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpx/_transports/default.py", line 60, in map_httpcore_exceptions
    yield
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpx/_transports/default.py", line 204, in handle_request
    resp = self._pool.handle_request(req)
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpcore/_sync/connection_pool.py", line 253, in handle_request
    raise exc
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpcore/_sync/connection_pool.py", line 237, in handle_request
    response = connection.handle_request(request)
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpcore/_sync/connection.py", line 90, in handle_request
    return self._connection.handle_request(request)
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpcore/_sync/http11.py", line 102, in handle_request
    raise exc
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpcore/_sync/http11.py", line 81, in handle_request
    ) = self._receive_response_headers(**kwargs)
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpcore/_sync/http11.py", line 143, in _receive_response_headers
    event = self._receive_event(timeout=timeout)
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpcore/_sync/http11.py", line 172, in _receive_event
    data = self._network_stream.read(
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpcore/backends/sync.py", line 26, in read
    return self._sock.recv(max_bytes)
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/contextlib.py", line 135, in __exit__
    self.gen.throw(type, value, traceback)
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpcore/_exceptions.py", line 12, in map_exceptions
    raise to_exc(exc)
httpcore.ReadTimeout: The read operation timed out

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

Traceback (most recent call last):
  File "/Users/ak4zh/updater/main.py", line 278, in job
    supabase.table(
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/postgrest/_sync/request_builder.py", line 53, in execute
    r = self.session.request(
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpx/_client.py", line 802, in request
    return self.send(request, auth=auth, follow_redirects=follow_redirects)
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpx/_client.py", line 889, in send
    response = self._send_handling_auth(
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpx/_client.py", line 917, in _send_handling_auth
    response = self._send_handling_redirects(
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpx/_client.py", line 954, in _send_handling_redirects
    response = self._send_single_request(request)
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpx/_client.py", line 990, in _send_single_request
    response = transport.handle_request(request)
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpx/_transports/default.py", line 204, in handle_request
    resp = self._pool.handle_request(req)
  File "/Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/contextlib.py", line 135, in __exit__
    self.gen.throw(type, value, traceback)
  File "/Users/ak4zh/updater/venv/lib/python3.9/site-packages/httpx/_transports/default.py", line 77, in map_httpcore_exceptions
    raise mapped_exc(message) from exc
httpx.ReadTimeout: The read operation timed out

1. 调整HTTP客户端超时时间 #

Supabase的Python客户端基于httpx构建，默认超时可能不足以处理大分页请求。初始化客户端时自定义更长超时：

import httpx
from supabase import create_client, Client

supabase_url = "你的Supabase URL"
supabase_key = "你的Supabase密钥"

# 设置30秒超时，可根据实际情况调整
client_options = {
    "http_client": httpx.Client(timeout=30.0)
}

supabase: Client = create_client(supabase_url, supabase_key, options=client_options)

2. 修复分页逻辑漏洞 #

原代码中range_start未初始化，且range_step依赖首次请求返回数据量，容易出现分页偏差。建议固定步长并修正range参数（Supabase的range是左闭右闭区间）：

existing_search_result = supabase.table('vehicles').select('ref_id', count='exact').execute()
total_existing_items = existing_search_result.count
print(total_existing_items)

existing_items = []
range_step = 20000  # 固定单次请求条数
range_start = 0

while len(existing_items) < total_existing_items:
    try:
        # 计算当前批次结束位置，避免超出总条数
        range_end = min(range_start + range_step, total_existing_items)
        batch_data = (
            supabase.table('vehicles')
            .select('ref_id')
            .order('id', desc=False)
            .range(range_start, range_end - 1)  # 左闭右闭，结束位置减1
            .execute()
        ).data
        existing_items.extend(batch_data)
        range_start = range_end
        print(f"已获取 {len(existing_items)}/{total_existing_items} 条数据")
    except Exception as e:
        logging.exception(e)
        # 超时后不跳过当前批次，等待1秒后重试
        time.sleep(1)
    time.sleep(0.3)

3. 增加指数退避重试机制 #

针对超时错误，实现重试逻辑，应对临时网络波动或服务端负载高峰：

from tenacity import retry, stop_after_attempt, wait_exponential, retry_if_exception_type

# 针对httpx.ReadTimeout重试，最多3次，等待时间指数增长
@retry(stop=stop_after_attempt(3), wait=wait_exponential(multiplier=1, min=2, max=10), retry=retry_if_exception_type(httpx.ReadTimeout))
def fetch_batch(range_start, range_end):
    return (
        supabase.table('vehicles')
        .select('ref_id')
        .order('id', desc=False)
        .range(range_start, range_end - 1)
        .execute()
    ).data

# 循环调用带重试的获取函数
while len(existing_items) < total_existing_items:
    try:
        range_end = min(range_start + range_step, total_existing_items)
        batch_data = fetch_batch(range_start, range_end)
        existing_items.extend(batch_data)
        range_start = range_end
        print(f"已获取 {len(existing_items)}/{total_existing_items} 条数据")
    except Exception as e:
        logging.exception("重试3次后仍失败，终止任务")
        break
    time.sleep(0.3)

4. 优化数据库查询性能 #

• 确保id字段有索引：如果id不是主键，手动创建索引：

  CREATE INDEX idx_vehicles_id ON vehicles(id);
  

• 去掉不必要的排序：如果ref_id的顺序不影响后续处理，删除.order('id', desc=False)，减少数据库排序开销。

5. 降低单次请求数据量 #

尝试更小的批次（比如10000条），减少单请求的数据传输量，降低超时概率。

内容的提问来源于stack exchange，提问作者Rawand Ahmed Shaswar