从Databricks将Pandas/PySpark DataFrame保存或更新至SharePoint的相关问题

一、是否可以直接操作？ #

可以直接操作SharePoint文件，并非必须通过Blob存储中转。不过针对PySpark大数据量场景，通过Blob存储中转会更高效；小数据量场景则可以直接用代码对接SharePoint API完成更新。

二、Pandas/PySpark DataFrame更新SharePoint文件的方法 #

1. Pandas DataFrame直接更新 #

借助SharePoint API客户端库，把DataFrame转成CSV/Excel字节流后，直接覆盖或上传到SharePoint文档库：

from office365.sharepoint.client_context import ClientContext
from office365.runtime.auth.user_credential import UserCredential
import pandas as pd
import io

# 初始化SharePoint连接上下文
ctx = ClientContext("https://你的SharePoint站点地址").with_credentials(
    UserCredential("你的账号", "密码或应用专用密码")
)

# 示例Pandas DataFrame
df = pd.DataFrame({"列1": [1,2,3], "列2": ["a","b","c"]})

# 将DataFrame转为CSV字节流
csv_buffer = io.StringIO()
df.to_csv(csv_buffer, index=False)
csv_bytes = csv_buffer.getvalue().encode('utf-8')

# 覆盖SharePoint上的目标文件（不存在则自动创建）
target_file_path = "/sites/你的站点名/Shared Documents/测试更新文件.csv"
file = ctx.web.get_file_by_server_relative_url(target_file_path)
file.save_binary(csv_bytes).execute_query()

2. PySpark DataFrame更新 #

• 小数据量：转成Pandas DataFrame后复用上面的逻辑（注意数据会被collect到Driver端，仅适合小数据量场景）

from pyspark.sql import SparkSession
from office365.sharepoint.client_context import ClientContext
from office365.runtime.auth.user_credential import UserCredential
import pandas as pd
import io

spark = SparkSession.builder.getOrCreate()
# 示例PySpark DataFrame
spark_df = spark.createDataFrame([(1,"a"),(2,"b"),(3,"c")], ["列1","列2"])

# 转Pandas处理（仅小数据量可用）
pandas_df = spark_df.toPandas()

# 后续上传逻辑和Pandas示例一致
csv_buffer = io.StringIO()
pandas_df.to_csv(csv_buffer, index=False)
csv_bytes = csv_buffer.getvalue().encode('utf-8')

ctx = ClientContext("https://你的SharePoint站点地址").with_credentials(
    UserCredential("你的账号", "密码或应用专用密码")
)
target_file_path = "/sites/你的站点名/Shared Documents/Spark测试文件.csv"
file = ctx.web.get_file_by_server_relative_url(target_file_path)
file.save_binary(csv_bytes).execute_query()

• 大数据量：推荐通过Blob存储中转，把SharePoint文档库关联到Azure Blob后挂载到Databricks，直接用PySpark写入挂载路径：

# 假设已将SharePoint关联的Blob存储挂载到/dbfs/mnt/sharepoint-blob
spark_df.write.mode("overwrite").csv("/dbfs/mnt/sharepoint-blob/大数据量文件.csv", header=True)

三、Office365-REST-Python-Client之外的替代库 #

• SharePlum：轻量级SharePoint API客户端，用法简洁，适合基础文件操作

from shareplum import Site
from shareplum.site import Version
import pandas as pd
import io

# 连接SharePoint站点
site = Site(
    "https://你的SharePoint站点地址/sites/你的站点名",
    version=Version.v365,
    auth=("你的账号", "密码")
)
# 指定目标文件夹
target_folder = site.Folder("Shared Documents")

# 生成DataFrame并转字节流
df = pd.DataFrame({"列1": [1,2,3], "列2": ["a","b","c"]})
csv_bytes = df.to_csv(index=False).encode('utf-8')

# 上传/覆盖文件
target_folder.upload_file(csv_bytes, "SharePlum测试文件.csv")

• Microsoft Graph API：适合需要集成更多Microsoft 365服务或复杂权限控制的场景，可直接用requests调用API：

import requests
import pandas as pd
import io

# 先获取Graph API访问令牌（需在Azure AD注册应用）
access_token = "你的Graph API令牌"
site_id = "你的SharePoint站点ID"
drive_id = "站点对应的Drive ID"
target_file_path = "/Graph测试文件.csv"

# 生成DataFrame字节流
df = pd.DataFrame({"列1": [1,2,3], "列2": ["a","b","c"]})
csv_bytes = df.to_csv(index=False).encode('utf-8')

# 发送PUT请求覆盖文件
api_url = f"https://graph.microsoft.com/v1.0/sites/{site_id}/drives/{drive_id}/root:/{target_file_path}:/content"
headers = {"Authorization": f"Bearer {access_token}"}
response = requests.put(api_url, headers=headers, data=csv_bytes)
response.raise_for_status()

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