Listing and updating a sharepoint list in python


 

For my first blog entry, I will write about something I love: using APIs.

This one will be on using Sharepoint API in python, with nothing more than some well known python libraries: requests, requests-ntlm.

Requirements

As said previously, we won’t be using anything specific: python 2.7 (but there’s no reasons for this not to work with either older or newer versions), requests library, and requests-ntlm for authentication (this could depend on your sharepoint installation).

And of course some knowledge of python, and some understanding of HTTP requests.

Warning

This code is not to be copied and pasted into your production system, it is meant only to share some of the research I had to do to be able to query sharepoint API. Sample of code are provided as-is and the author of this article can’t be held responsible for the use you make of the code shared here.

I am not a sharepoint engineer, in fact I know more about its API than about sharepoint itself. #dontblamemeformymistakes 😉

Reading a sharepoint list in Python

So let’s start by reading the elements of our list. We will base our example on reading (and then updating) our list called MyDevopsBooks.

Headers and variables

 


import requests
import pprint
from requests_ntlm import HttpNtlmAuth
sharepoint_user = 'mysharepointuser'
sharepoint_password = 'mysharepointpassword'
#Sharepoint URL should be the address of the site followed by /_api/web/
sharepoint_url = 'https://mysharepointsiteurl/_api/web/'
sharepoint_contextinfo_url = 'https://mysharepointsiteurl/_api/contextinfo'
sharepoint_listname = 'MyDevopsBooks'

headers = {
"Accept":"application/json; odata=verbose",
"Content-Type":"application/json; odata=verbose",
"odata":"verbose",
"X-RequestForceAuthentication": "true"
}

Authentication

I believe there are multiple authentication methods with Sharepoint, I describe here the basic NTLM authentication.


auth = HttpNtlmAuth(sharepoint_user, sharepoint_password)

Reading all elements, and deal with pagination

As for most APIs out there, Sharepoint use a pagination method. Meaning that if your list contains thousands of entries, it would only send you results by groups of X results. The way sharepoint deals with pagination is with a ‘__next’ link. We can then base our tests on the presence or absence of this value to determine whether there is more to read, or if we reached the end of the list.



#Get List information
r = requests.get(sharepoint_url+"lists/getbytitle('%s')" % sharepoint_listname, auth=auth, headers=headers, verify=False)
list_id = r.json()['d']['Id']
list_itemcount = r.json()['d']['Id']

##### Query all items from the list ######
list_cursor = 0 
list_pagesize = 400
api_items_url = sharepoint_url + "Lists(guid'%s')/Items" % list_id
concat_items = []

# We start by an initial request and then loop through pages returned by sharepoint
cur_page = requests.get(api_items_url, auth=auth, headers=headers,verify=False)
concat_items += cur_page.json()['d']

while '__next' in cur_page.json()['d']['results']:
    cur_page = requests.get(cur_page.json()['d']['__next'], auth=auth, headers=headers, verify=False)
    concat_items += cur_page.json()['d']['results']


# Let's see the data we collected:
pprint.pprint(concat_items)

Update a specific item from a list

X-RequestDigest

I actually spent quite some time understanding what was this value and how to query it.
So basically when you want to do a post / update / delete call you need to initially do a post to a certain page which will give you some context data, that you will then need to send as part of your HTTP headers to perform the action you want on your data.

For more information, there are plenty of information about this here and there.

 


#### Update an Item in a list #### 
# First of all get the context info
r = requests.post(sharepoint_contextinfo_url, auth=auth, headers=headers, verify=False)

form_digest_value = r.json()['d']['GetContextWebInformation']['FormDigestValue']

Updating our headers


item_id = 2 #This id is one of the Ids returned by the code above and the value is a pure example. 
api_page = sharepoint_url + "lists/GetByTitle('%s')/GetItemById(%d)" % (sharepoint_listname, item_id)
update_headers = {
    "Accept":"application/json; odata=verbose",
    "Content-Type":"application/json; odata=verbose",
    "odata":"verbose",
    "X-RequestForceAuthentication": "true",
    "X-RequestDigest" : form_digest_value,
    "IF-MATCH": "*",
    "X-HTTP-Method" : "MERGE"
}

And executing the update!

r = requests.post(api_page, {'some_column':'some_value'}, auth=auth, headers=update_headers,verify=False)
if ret.status_code == 204:
    print('Well done, you just updated a list item, don\'t rush to sharepoint gui yet, there might be a delay due to internal caching')

Congratulations! You have now updated your first update of a sharepoint list’s item in python

Adding a new item to our sharepoint list

We will need to use again our X-RequestDigest header to perform this operation, so if you skipped this part, please read the “Update a specific item from a list” paragraph.

Headers

#### Add an entry in a list ####
r = requests.post(sharepoint_contextinfo_url, auth=auth, headers=headers, verify=False)
form_digest_value = r.json()['d']['GetContextWebInformation']['FormDigestValue']
post_headers = {
    "Accept":"application/json; odata=verbose",
    "Content-Type":"application/json; odata=verbose",
    "odata":"verbose",
    "X-RequestForceAuthentication": "true",
    "X-RequestDigest": form_digest_value
}

Specifying the type of the item to post

As we are now not updating an existing item, we need to give to sharepoint the type of the item we are posting.

There might be sexier ways to do it, but as a sharepoint list is a list of similar items, I just used the value of the ‘type’ key of previously selected items, and reused it.


# First we need to get the item type, dirtybutworks: get it fro previously 
# requested items
item_type = concat_items[1]['__metadata']['type']
input = {'Column1': 'Value1', 'Column2':'value2'} #This of course depends on your list
input['__metadata'] = {
    'type' : item_type,
}

Executing the post request to create our item

Finally we execute our query and check its return code:


r = requests.post(api_page, input, auth=auth, headers=post_headers, verify=False)
if r.status_code == 201:
    print('Successfully added')

Complete example

The full code example is available on github here

3 thoughts on “Listing and updating a sharepoint list in python”

  1. Your blog helped a lot when I was writing code to update file fields on Sharepoint. However, I had to change a couple of things to get it working. It might be the version of SP, or the python requests package I am using. The two things are as follows:

    1. At the execution of the update, instead of sending {‘some_column’:’some_value’}, I had to send a dictionary similar to what you would send when adding a new item, which includes the __metadata key. So I used this:

    payload = {‘Fields1’: ‘Somevalue’,
    ‘Field2’: ‘SomeOtherValue’,
    ‘__metadata’: {‘type’: ‘SomeAppropriateType’}
    }

    2. I had to send the payload as the value of json (not data). So requests.post() was called as follows:

    r = requests.post(api_page, json=payload, auth=auth, headers=update_headers, verify=False)

    and then everything started working. Thanks.

  2. Hello Mohammad,

    I’m glad this blog post has helped you! Unfortunately I don’t have my hands on a Sharepoint installation to test your suggestions and edit the article, but I’m sure at some point it will help someone!

  3. Thanks for posting this useful info.

    For some reason this code doesn’t work:
    list_id = r.json()[‘d’][‘Id’]
    list_itemcount = r.json()[‘d’][‘Id’]

    Also, when executing code up to the r = requests, I get an InsecureRequestWarning.

    I know the name of the list, and I even have the list url. Could you provide some links etc? I’ve tried downloading this list so many ways, including using the ‘sharepoint’ module but nothing has worked so far.

Leave a Reply

Your email address will not be published. Required fields are marked *