Pandas读取Google Drive受限CSV报错及权限临时调整方案咨询

报错根因说明 #

权限受限的时候，Google Drive返回的是HTML格式的权限拦截页面，而非CSV文件本身，pandas误将HTML内容作为CSV解析才会触发字段数不匹配的ParserError，你加error_bad_lines=False后读取到的实际是权限提示页面的内容，并非你的CSV数据。

方案一：全程不修改权限，带身份认证直接读取（更安全） #

无需调整文件共享状态，通过Google Drive官方API完成身份验证后直接读取文件内容，全程不会暴露文件给公网。

• 前置准备：安装依赖包

pip install pandas google-api-python-client google-auth-httplib2 google-auth-oauthlib

• 操作步骤：在Google Cloud控制台开启Google Drive API，下载OAuth2凭据文件credentials.json放在脚本同级目录，使用如下代码读取：

from googleapiclient.discovery import build
from googleapiclient.http import MediaIoBaseDownload
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
import pandas as pd
import io
import os

# 定义权限范围，只读访问Drive文件
SCOPES = ['https://www.googleapis.com/auth/drive.readonly']
# 替换为你的CSV文件ID
FILE_ID = '1k5Q9wuq-mIeCl1zpPF0DJ31uzlJ8QfSO'

creds = None
# 已保存的token文件，首次运行后会自动生成
if os.path.exists('token.json'):
    creds = Credentials.from_authorized_user_file('token.json', SCOPES)
# 没有有效凭据时重新登录
if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
        creds.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file(
            'credentials.json', SCOPES)
        creds = flow.run_local_server(port=0)
    # 保存凭据，下次运行无需重复认证
    with open('token.json', 'w') as token:
        token.write(creds.to_json())

# 下载文件内容
service = build('drive', 'v3', credentials=creds)
request = service.files().get_media(fileId=FILE_ID)
file = io.BytesIO()
downloader = MediaIoBaseDownload(file, request)
done = False
while done is False:
    status, done = downloader.next_chunk()

# 直接从内存读取为DataFrame
file.seek(0)
df = pd.read_csv(file, encoding="ISO-8859-1")

方案二：临时修改权限，读取完成后自动回滚 #

如果需要沿用原有公开链接读取的逻辑，可以通过API自动化完成权限的临时开放和恢复，无需手动操作：

• 前置准备和凭据获取和方案一一致，使用如下代码：

from googleapiclient.discovery import build
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
import pandas as pd
import os

SCOPES = ['https://www.googleapis.com/auth/drive']
FILE_ID = '1k5Q9wuq-mIeCl1zpPF0DJ31uzlJ8QfSO'

creds = None
if os.path.exists('token.json'):
    creds = Credentials.from_authorized_user_file('token.json', SCOPES)
if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
        creds.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file(
            'credentials.json', SCOPES)
        creds = flow.run_local_server(port=0)
    with open('token.json', 'w') as token:
        token.write(creds.to_json())

service = build('drive', 'v3', credentials=creds)

# 新增公开可读权限
permission = {
    'type': 'anyone',
    'role': 'reader'
}
perm = service.permissions().create(
    fileId=FILE_ID,
    body=permission,
    fields='id'
).execute()

# 读取CSV
df = pd.read_csv(f'https://drive.google.com/uc?id={FILE_ID}', encoding="ISO-8859-1")

# 删除刚才新增的公开权限，恢复为受限状态
service.permissions().delete(
    fileId=FILE_ID,
    permissionId=perm['id']
).execute()

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