sharepoint - GET document library metadata with Python (other languages welcome too) - Stack Overflow

admin管理员组

文章数量:1296900

I'm working on a back-up system where, at some frequency, the metadata columns in a document library in my SharePoint is backed up into an Azure SQL Database. I have registered an app in Azure with the correct permissions that will complete this process. However, I haven't been able to make any progress with Microsoft's Graph API or the SharePoint REST API. Equally, I haven't been successful with Python libraries like Shareplum and Office365-REST.

I've trawled overflow, microsoft forums, reddit etc. and information is scarce or slightly adjacent to my case. Has anyone done this before?

Thank you!

I'm working on a back-up system where, at some frequency, the metadata columns in a document library in my SharePoint is backed up into an Azure SQL Database. I have registered an app in Azure with the correct permissions that will complete this process. However, I haven't been able to make any progress with Microsoft's Graph API or the SharePoint REST API. Equally, I haven't been successful with Python libraries like Shareplum and Office365-REST.

I've trawled overflow, microsoft forums, reddit etc. and information is scarce or slightly adjacent to my case. Has anyone done this before?

Thank you!

• sharepoint
• microsoft-graph-api
• office365
• office365api
• sharepoint-rest-api

Share Improve this question Follow asked Feb 11 at 22:21 Harry DuffyHarry Duffy 8777 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

1. Authentication & Permissions

Ensure your Azure-registered app has Sites.Read.All (for read access to SharePoint metadata) and SQL Server Contributor roles in Azure for database writes36.

Use OAuth 2.0 client credentials flow to generate tokens for API access. Example token request:

 POST https://login.microsoftonline/{tenant-id}/oauth2/v2.0/token
    Content-Type: application/x-www-form-urlencoded

client_id={app-id}&client_secret={secret}&scope=https://graph.microsoft/.default&grant_type=client_credentials

2. Retrieve SharePoint Metadata via REST API Use SharePoint REST API endpoints to fetch document library metadata:

List Items Endpoint:

GET https://{site-url}/_api/web/lists/getbytitle('Documents')/items?$select=Title,Modified,Author/Title,FileRef&$expand=Author

Replace Documents with your library name.

Use $select to specify metadata columns (e.g., Title, ModifiedBy, FileRef) 28.

Pagination: For large datasets, use $top and $skiptoken (e.g., ?$top=5000&$skiptoken=Paged=TRUE) 10.

3. Data Transformation

Parse the JSON response and map SharePoint fields to SQL columns. For example:

# Example using Python
for item in response_json['d']['results']:
    metadata = {
        "FileRef": item['FileRef'],
        "ModifiedBy": item['Author']['Title'],
        "LastModified": item['Modified']
    }
    # Insert into SQL

Handle nested properties (e.g., Author/Title via $expand).

4.Write to Azure SQL Use pyodbc or SQLAlchemy to insert data:

import pyodbc
conn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER={server};DATABASE={db};UID={user};PWD={password}')
cursor = conn.cursor()
cursor.execute("INSERT INTO Metadata (FilePath, ModifiedBy, ModifiedDate) VALUES (?, ?, ?)", 
               (metadata['FileRef'], metadata['ModifiedBy'], metadata['LastModified']))
connmit()

本文标签： sharepointGET document library metadata with Python (other languages welcome too)Stack Overflow