使用 Python 取得 Microsoft Sharepoint 的資料

Microsoft Sharepoint 是許多公司內部分享資料的平台。我們有時會需要從 Sharepoint 上取得大量資料，但是一個一個點擊下載會花很多時間。本文將介紹如何用 Python 將 Microsoft Sharepoint 的資料更快速自動地下載到本機。

載入套件

1
2
3
!pip install python-dotenv
!pip install Office365-REST-Python-Client
!pip install git+https://github.com/vgrem/Office365-REST-Python-Client.git

1
2
3
4
5
6
7
8
# For office365 library
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File 
# For .env file
import os
from dotenv import load_dotenv
load_dotenv()

連上 Sharepoint

為了安全可以將資訊另存在 .env 中，但要注意不要不小心上傳到 Git。

1
2
3
SHAREPOINT_BASE_URL = # Sharepoint 連結
SHAREPOINT_USER = # 帳號
SHAREPOINT_PASSWORD = # 密碼

接著就可以使用 os.getenv() 取得 .env 中的參數。

1
2
3
sharepoint_base_url = os.getenv("SHAREPOINT_BASE_URL")
sharepoint_user = os.getenv("SHAREPOINT_USER")
sharepoint_password =  os.getenv("SHAREPOINT_PASSWORD")

連接到 Sharepoint。

1
2
3
4
5
6
7
8
auth = AuthenticationContext(sharepoint_base_url)
auth.acquire_token_for_user(sharepoint_user, sharepoint_password)
ctx = ClientContext(sharepoint_base_url, auth)
web = ctx.web
ctx.load(web)
ctx.execute_query()
print('Connected to SharePoint: ',web.properties['Title'])
# Output: Connected to SharePoint:  My Sharepoint

下載檔案

設定資料夾路徑。

1
2
3
4
sharepoint_folder = # Sharepoint 資料夾路徑
output_dir = # 檔案儲存於本機的路徑
if not os.path.exists(output_dir):
    os.mkdir(output_dir)

列出資料夾下的檔案名稱。

1
2
3
4
5
6
7
folder = ctx.web.get_folder_by_server_relative_url(sharepoint_folder)
filenames = []
sub_folders = folder.files 
ctx.load(sub_folders)
ctx.execute_query()
for s_folder in sub_folders:
    filenames.append(s_folder.properties["Name"])

下載檔案到本機。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
for filename in filenames:
    path = os.path.join(sharepoint_folder, filename)
    file_response = File.open_binary(ctx, path)
    if file_response.content:
        with open(os.path.join(output_dir, filename), 'wb') as output_file:  
            output_file.write(file_response.content)
        print(f"{filename} saved")
# Output: 
# 001.txt saved
# 002.txt saved
# fig.png saved

這樣就完成了！

參考資料

Photo by Windows on Unsplash