Get SharePoint List with Python

Question

I am trying to find any way possible to get a SharePoint list in Python. I was able to connect to SharePoint and get the XML data using Rest API via this video: https://www.youtube.com/watch?v=dvFbVPDQYyk... but not sure how to get the list data into python. The ultimate goal will be to get the SharePoint data and import into SSMS daily.

Here is what I have so far..

import requests
from requests_ntlm import HttpNtlmAuth
url='URL would go here'
username='username would go here'
password='password would go here'
r=requests.get(url, auth=HttpNtlmAuth(username,password),verify=False)

I believe these would be the next steps. I really only need help getting the data from SharePoint in Excel/CSV format preferably and should be fine from there. But any recommendations would be helpful..

#PARSE XML VIA REST API
#PRINT INTO DATAFRAME AND CONVERT INTO CSV
#IMPORT INTO SQL SERVER
#EMAIL RESULTS

David Castro · Accepted Answer · 2019-09-19 16:42:07Z

9

from shareplum import Site
from requests_ntlm import HttpNtlmAuth

server_url = "https://sharepoint.xxx.com/"
site_url = server_url + "sites/org/"

auth = HttpNtlmAuth('xxx\\user', 'pwd')
site = Site(site_url, auth=auth, verify_ssl=False)
sp_list = site.List('list name in my share point')
data = sp_list.GetListItems('All Items', rowlimit=200)

answered Sep 19, 2019 at 16:42

David Castro

1931 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Trenton McKinney Over a year ago

Here are some guidelines for How do I write a good answer?. This provided answer may be correct, but it could benefit from an explanation. Code only answers are not considered "good" answers. From review.

David Castro Over a year ago

@shzyincu domain my friend (I was using a corporate network)

David Castro Over a year ago

@TrentonMcKinney thanks i will consider in the future to add a description or title at least

JWBG Over a year ago

if possible, change "sharepoint.xxx.com" to "xxx.sharepoint.com". Regards from México.... @DavidCastro

StarshipBot · Accepted Answer · 2025-02-28 13:48:28Z

6

This can be done using SharePlum and Pandas, using this code:

import pandas as pd     # importing pandas to write SharePoint list in excel or csv
from shareplum import Site 
from requests_ntlm import HttpNtlmAuth

cred = HttpNtlmAuth(#userid_here, #password_here)
site = Site('#sharePoint_url_here', auth=cred)

sp_list = site.List('#SharePoint_list name here') # this creates SharePlum object
data = sp_list.GetListItems('All Items') # this will retrieve all items from list

# this creates pandas data frame you can perform any operation you like do within 
# pandas capabilities     

data_df = pd.DataFrame(data[0:])
data_df.to_excel("data.xlsx")

edited Feb 28 at 13:48

StarshipBot

295 bronze badges

answered Jan 27, 2020 at 19:50

Abhishek Pratap Singh

1831 gold badge2 silver badges10 bronze badges

2 Comments

Andrew Over a year ago

how to get the List version history by python?

shzyincu Over a year ago

I get "certificate to verify failed: unable to get local issuer certificate" :( please some one help, sharepoint2013 is where I need to connect

Dr. Mantis Tobbogan · Accepted Answer · 2019-03-21 13:48:06Z

3

I know this doesn't directly answer your question (and you probably have an answer by now) but I would give the SharePlum library a try. It should hopefully simplify the process you have for interacting with SharePoint.

Also, I am not sure if you have a requirement to export the data into a csv but, you can connect directly to SQL Server and insert your data more directly.

I would have just added this into the comments but don't have a high enough reputation yet.

answered Mar 21, 2019 at 13:48

Dr. Mantis Tobbogan

5389 silver badges21 bronze badges

Comments

Morgana Sartor · Accepted Answer · 2020-01-23 19:27:50Z

0

I can help with most of these issues

import requests
import xml.etree.ElementTree as ET
import csv

from requests_ntlm import HttpNtlmAuth

response = requests.get("your_url", auth=HttpNtlmAuth('xxxx\\username','password'))
tree =  ET.ElementTree(ET.fromstring(response.content))
tree.write('file_name_xml.xml')
root = tree.getroot()

#Create csv file 
csv_file = open('file_name_csv.csv', 'w', newline = '', encoding='ansi')
csvwriter = csv.writer(csv_file)

col_names = ['Col_1', 'Col_2', 'Col_3', 'Col_n']
csvwriter.writerow(col_names)

field_tag = ['dado_1', 'dado_2', 'dado_3', 'dado_n']

#schema XML microsoft 
ns0 = "http://www.w3.org/2005/Atom"
ns1 = "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
ns2 = "http://schemas.microsoft.com/ado/2007/08/dataservices"


for member in root:
    if member.tag == '{' + ns0 + '}entry':
        for element in member:
            if element.tag == '{' + ns0 + '}content':
                data_line = []

                for field in element[0]:            
                    for count in range(0, len(field_tag)):
                        if field.tag == '{' + ns2 + '}' + field_tag[count]:
                            data_line.append(field.text)


                csvwriter.writerow(data_line)

csv_file.close()

answered Jan 23, 2020 at 19:27

Morgana Sartor

11 bronze badge

1 Comment

Hofbr Over a year ago

Hi Morgana. Is there a way to pull Column names and field tags from the XML data? The table I'm pulling from, it would be a bit tedious to write into a list manually. Perhaps you could also clarify what field_tags are? Any details add on your logic you're using would be helpful.

Collectives™ on Stack Overflow

Get SharePoint List with Python

4 Answers 4

4 Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related