Search
  • Nishant Sethi

Working with SharePoint Lists using Python (CRUD Operations)

Updated: Nov 17, 2019

In this post I will show you how you can connect to your sharepoint online site from Python and then easily carry out all sorts of operations with either document library,list or other objects and apps.

In this example in particular, we will first connect to a sharepoint site and then will work with lists, like retrieving the data from the lists and then updating the existing records with some new values.

I will also show you how you can actually implement row(item) level security in your Sharepoint list using these above simple operations but that will be a part of a later post.


So lets get going!!


First of all we need to generate the Client ID and Client Secret of the site we need to connect and then register it in the 0365 Admin Center ... for exact details of doing that step by step I have written a separate article, which you can refer from here ... you would also need the Tenant ID of your Azure Active Directory and also your Resource ID and if your wondering how to get those then dont worry, I have also explained that step by step in the same above article.


So now to start with we have the below four details which we would need to pass in our python code:



  1. Client ID

  2. Client Secret

  3. Tenant ID

  4. Resource ID


First step is to install the requests package of python using pip


open Command Prompt and install this package using the below command:


pip install requests

Now lets start writing the actual code.


First of all import the below packages:


import os
import sys
import requests


Now we will make a POST request to the below URL using the Client ID, Client Secret, Tenant ID and Resource ID we captured to get the access token for connecting to our Sharepoint Site.


url = 'https://accounts.accesscontrol.windows.net/3b7c8eb0-1c25-423a-8876-fa9ad6f4a70c/tokens/OAuth/2'
x = requests.post(url,data = {'grant_type':'client_credentials','client_id':' <Your Client ID>@<Your Tenant ID> ','client_secret':' <Your Client Secret> ','resource':' <Your Resource ID>/nishantsethi.sharepoint.com@<Your Tenant ID> '},headers = {'Content-Type':'application/x-www-form-urlencoded'})

Now we will convert the response of the above the POST Request to a dictionary type object and then extract the value of the access_token Key from this dictionary and store into a variable for later use:

response = eval(x.text)
access_token = response.get("access_token")

Now we have the ticket to access our Sharepoint Site and we can retrieve/update data in our sharepoint list using POST/GET requests.


Lets first retrieve some data from a list called AzureConnect in my Sharepoint site called Sharepoint Forms.


x = requests.get(url,headers = {'Authorization':'Bearer '+access_token,'Accept':'application/json;odata=verbose','Content-Type':'application/x-www-form-urlencoded'})

The above code will retrieve the top 10 items from my list called AzureConnect and in the same way I can fetch data from my list based on different query filters.


To see the actual data that you got you can use the text property of the request object as shown below:


x.text

This was about fetching the data from the List, now lets update the data already present in our list by making a POST request to it this time as shown below:



body = "{'__metadata': { 'type': 'SP.Data.AzureConnectListItem'}, 'Country': 'Brazil'}"
url = "https://nishantsethi.sharepoint.com/sites/SharepointForms/_api/web/lists/GetByTitle('AzureConnect')/Items(4)"

the above body and url will be passed in the actual POST request below and will change the value of the country column for the item with item id 4 to Brazil,


Right now the value of Country for Item ID 4 is "United States" :



So here is the Code for doing that:


x = requests.post(url,data = body,headers = {'Authorization':'Bearer '+access_token,'X-RequestDigest':'form digest value','If-Match':'*','X-HTTP-Method':'MERGE','Accept':'application/json;odata=verbose','Content-Type':'application/json;odata=verbose'})

This is it... now lets check the List in our site, the Country for Item ID 4 should have got changed from "United States" to "Brazil"





And now in this way we can keep manipulating our Sharepoint list items using the CRUD operations(CREATE, UPDATE, DELETE) through the POST request we made above by making some very minor changes in the Url and Body of these requests.


For details of all the operations that you can perform on the Sharepoint list refer the official Microsoft Documentation below:


https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest



Hope this was helpful!!


Cheers!!

Nishant Sethi

Data Engineer, KPMG India

39 views
 
 
pandasecurity-Who-are-the-most-famous-ha