def deleteListFunc(_SP, _SPL):
'''This function truncate Sharepoint list, it takes two argumentsthe target site and list to be deleted'''
print(f'deleting items in the Sharepoint List : {_SPL}....')
# Connecting to the destination sharepoint list with try
try:
site = Site(
f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{_SP}/',
authcookie=authcookie)
except Exception as e:
print(e)
# reading the the desitination Sharepoint list items
mylist1 = site.List(_SPL)
i = 1
count = 0
while i > 0:
# lopping while there are items
data1 = mylist1.GetListItems('All Items', rowlimit=2000)
ids = [item['ID'] for item in data1]
# Delete all selected items from the sharepoint list by IDs
mylist1.UpdateListItems(ids, kind='Delete')
if len(data1) == 2000:
print(f"deleted {count + 2000} chunck...")
time.sleep(300)
i = len(data1)
count = count + 2000
def conn(SP, li):
site1 = Site(f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{Sp}/', authcookie=authcookie, huge_tree=True)
# Reading The Sharepoiny admin input
sp_list = site1.List(li)
# Reading the last records in the SQltoshqrepoint list (one sql table at time )
data = sp_list.GetListItems('All Items')
return data
def SPList(_SP, _SPL):
try:
site = Site(f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{_SP}/', authcookie=authcookie)
mylist1 = site.List(_SPL)
data = mylist1.GetListItems('All Items')
return data
except Exception as e : print(e)
def pushToSP(data, mydata):
'''This function push python object to SharePoint lis, its takes tow argumnets both should dictionaries(object) the first one should containe the target site and list, the second one should contain the data to be pushed to the list'''
_SP = data['SP_Site']
_SPL = data['SP_List']
print(f'updating Sharepoint List... : {_SPL}....')
# Connecting to the destination sharepoint list with try
try:
site = Site(f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{_SP}/', authcookie=authcookie)
except Exception as e : print(e)
# reading the the desitination Sharepoint list
mylist1 = site.List(_SPL)
# # # Adding the new Data to the sharepoint list if data is more than 20000, break it down to batches
if len(mydata)>5000:
n=0
j = 5000
print("Starting batches ...........")
while len(mydata)> 0 :
chunk = mydata[n:j]
mylist1.UpdateListItems(data=chunk, kind='New')
print(f"Completed 1st {j} batch-------------")
n = n + 5000
j = j + 5000
time.sleep(60)
else :
mylist1.UpdateListItems(data=mydata, kind='New')
print(f'---------------Done -------------')
def incrementToSP(data, mydata):
'''This function push python object to SharePoint lis, its takes tow argumnets both should dictionaries(object) the first one should containe the target site and list, the second one should contain the data to be pushed to the list'''
_SP = data['SP_Site']
_SPL = data['SP_List']
col = data['Identity']
print(f'Inserting new Increments to Sharepoint List... : {_SPL}....')
# Connecting to the destination sharepoint list with try
try:
site = Site(
f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{_SP}/',
authcookie=authcookie)
except Exception as e:
print(e)
# reading the the desitination Sharepoint list
mylist1 = site.List(_SPL)
# # # Adding the new Data to the sharepoint list if data is more than 20000, break it down to batches
new_ids = [d[col] for d in mydata]
all_Sp_ids = [d[col] for d in listObject(mylist1)]
# Checking if new increment already being added
if set(new_ids).issubset(set(all_Sp_ids)):
print("No New increment")
else:
DatPush(mylist1, mydata, 'New')
def Lists(Sp):
site = Site(
f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{Sp}/',
authcookie=authcookie,
huge_tree=True)
# mylist = site.List(li)
li = site.GetListCollection()
# df = pd.DataFrame(li)
# df.to_excel('bidash_lists.xlsx', index=False)
mylists = [l['Title'] for l in li]
secur = {l['Title']: l['InheritedSecurity'] for l in li}
readSecurity = {l['Title']: l['ReadSecurity'] for l in li}
Allowance = {l['Title']: l['AllowAnonymousAccess'] for l in li}
depends = {}
cols = ['Business', 'Operating', 'Deal', 'DataSource']
# Get all list that have dependencies
for lis in mylists:
temp = []
try:
mylist = site.List(lis)
data = mylist.GetListItems('All Items', rowlimit=1)[0]
for col in cols:
if any(col in s for s in data.keys()):
temp.append(col)
if len(temp) != 0:
depends[lis] = temp
except Exception as e:
print(e)
print(depends)
def download_dealer_list():
''' downloads the current dealer listing from SharePoint and saves as CustomerDocuments.xlsx '''
creds = Credentials()
cred = HttpNtlmAuth(creds.username, creds.password)
site = Site('http://tng/SharedServices/AR', auth=cred)
rows = site.List('Document_Circulation').get_list_items()
columns = [
'Service_Location',
'Dealer_Name',
'Master',
'Chain_Master',
'Dealer_Number',
'Statements',
'Invoices',
'Credits',
'Fax Number',
'Attn To:',
'Output',
'Created',
'Modified',
'Item Type',
'Path',
'Email Address'
]
dealer_list = []
keys = []
for row in rows:
keys = keys + list(row.keys())
# keys = list(row.keys())
# r = [row[i] for i in keys]
r = [
row.get('Service_Location'),
row.get('Dealer_Name'),
row.get('Master'),
row.get('Chain_Master'),
row.get('Dealer_Number'),
row.get('Statements'),
row.get('Invoices'),
row.get('Credits'),
row.get('Fax Number', ''),
row.get('Attn To:', ''),
row.get('Output', ''),
row.get('Created'),
row.get('Modified'),
row.get('Item Type'),
row.get('Path', ''),
row.get('Email Address', '')
]
dealer_list.append(r)
keys = set(keys)
print(keys)
df = pd.DataFrame(dealer_list, columns=columns)
# delete existing listing, if it exists
if os.path.isfile('../Customer_Documents.xlsx'): os.remove('../Customer_Documents.xlsx')
df.to_excel('../Customer_Documents.xlsx', index=False, header=True)
def pushToSP(_SP, _SPL, mydata):
try:
site = Site(f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{_SP}/', authcookie=authcookie)
except Exception as e : print(e)
# reading the the desitination Sharepoint list
mylist1 = site.List(_SPL)
# data1 = mylist1.GetListItems('All Items')
# Retreiving all IDs of the list if it not empty
# ids = [item['ID'] for item in data1]
# Delete all items from the sharepoint list by IDs
# mylist1.UpdateListItems(ids, kind='Delete')
# # # Adding the new Data to the sharepoint list if data is more than 20000, break it down to batches
if len(mydata)>20000:
n=0
j = 20000
print("Starting batches ...........")
while len(mydata)> 0 :
chunk = mydata[n:j]
mylist1.UpdateListItems(data=chunk, kind='New')
print(f"Completed 1st {j} batch-------------")
n = n + 20000
j = j + 20000
else :
mylist1.UpdateListItems(data=mydata, kind='New')
print(f'---------------Done -------------')
def get_sp_list(listName):
domain = os.environ.get('USERDOMAIN')
user = os.environ.get('USERNAME')
password = os.environ.get('PASSWORD')
auth = HttpNtlmAuth(f'{domain}\\{user}', password)
site = Site('https://data.erg.net/', auth=auth)
sp_list = site.List(listName)
list_data = sp_list.GetListItems()
spRccEng = {}
spRccRus = {}
n = 0
for rcc in list_data:
if list_data[n]['Active/Inactive'] == 'Inactive':
n += 1
else:
if list_data[n].get('RCC code') is None:
n += 1
else:
spRccEng[list_data[n]
['RCC code']] = list_data[n]['HFM RCC name']
spRccRus[list_data[n]
['RCC code']] = list_data[n]['Наименование ЦЗО']
n += 1
return (spRccEng, spRccRus)
def ListsTobePushed(c, _SPL):
site1 = Site(
f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/bidash',
authcookie=authcookie)
# Reading The Sharepoiny admin input
sp_list = site1.List(_SPL)
# Reading the last records in the SQltoshqrepoint list (one sql table at time )
data = sp_list.GetListItems('All Items')
# retreiving the header of the list
if type(data) is list:
return data
else:
header = sp_list.GetListItems('All Items', rowlimit=1)
# getting the list of fields to be inserted to SQL
# fields = list(header[0].keys())
# retreiving the first ID
i = int(header[0]['ID'])
n = i + 5000
m = 5000
data = []
# looping over the 5000 chuncks at time because of the limit of SharePoint
while m == 5000:
query = {
'Where': ['And', ('Geq', 'ID', str(i)), ('Lt', 'ID', str(n))]
}
dt = sp_list.GetListItems(viewname='All Items', query=query)
df = pd.DataFrame(dt)
data_c = df.to_dict('records')
# data_c = [{k: v for k, v in mydict.items() if k in (c, 'ID')} for mydict in data_c]
print(data_c[:2])
data.extend(data_c)
i = i + 5000
n = n + 5000
m = len(dt)
return data
def main(req: func.HttpRequest) -> func.HttpResponse:
# Configuration Sanity Check
o365HostUri = os.environ['O365HostUri']
o365UserName = os.environ['O365UserName']
o365UserPassword = os.environ['O365UserPassword']
o365SiteUri = os.environ['O365SiteUri']
o365ListName = os.environ['O365ListName']
validationError = False
if not o365HostUri:
validationError = True
logging.error('O365HostUri configuration missing.')
if not o365UserName:
validationError = True
logging.error('O365UserName configuration missing.')
if not o365UserPassword:
validationError = True
logging.error('O365UserPassword configuration missing.')
if not o365SiteUri:
validationError = True
logging.error('O365SiteUri configuration missing.')
if not o365ListName:
validationError = True
logging.error('O365ListName configuration missing.')
if validationError:
return func.HttpResponse("Invalid configuration.", status_code=400)
authcookie = Office365(o365HostUri, username=o365UserName, password=o365UserPassword).GetCookies()
site = Site(o365SiteUri, version=Version.v365, authcookie=authcookie)
sp_list = site.List(o365ListName)
data = sp_list.GetListItems('All Items')
logging.info(data)
return func.HttpResponse(json.dumps(data), status_code=200, mimetype='application/json')
def run():
wb = load_workbook(filename='data.xlsx', read_only=True)
ws = wb['Raw Data']
data = ws.values
columns = next(data)[1:]
data = list(data)
idx = [r[0] for r in data]
data = (islice(r, 1, None) for r in data)
df = pd.DataFrame(data, index=idx, columns=columns)
# print(df.loc[[1130359]])
authcookie = Office365('https://purdue0.sharepoint.com',
username='',
password='').GetCookies()
site = Site('https://purdue0.sharepoint.com/sites/HRIS',
version=Version.o365,
authcookie=authcookie)
test_list = site.List('ELT-Test')
# while True:
# list_items = test_list.GetListItems('All Items', fields=['ID'], row_limit=500)
# if len(list_items) == 0:
# break
# id_list = [x['ID'] for x in list_items]
# log.info('Starting deletion of {} records'.format(str(len(list_items))))
# test_list.UpdateListItems(data=id_list, kind='Delete')
# log.info('Deletion complete.')
# print (len(list_items))
list_items = []
try:
list_items = test_list.GetListItems(
'All Items',
fields=['ID', 'Employee Name'],
# query={'Where': ['Or', ('Eq', 'Employee Name', 'Mark Holmes'), ('Eq', 'Employee Name', 'Patricia Prince')]},
query={'Where': [('Eq', 'Employee Name', 'Mark Holmes')]},
)
except shareplum.errors.ShareplumRequestError as err:
log.error(err)
if err.details and type(
err.details) == requests.exceptions.HTTPError:
if err.details.response.status_code in [429, 503]:
# TODO: Sleep for Retry-After to prevent further throttling
pass
elif err.details.response.status_code in [500]:
log.error(err.details.response.request.body)
log.error(err.details.response.content)
for list_item in list_items:
log.info(list_item)
def deepLokkup (Sp, df1, l2, col, col1, col2):
site1 = Site(f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{Sp}/', authcookie=authcookie, huge_tree=True)
sp_list = site1.List(l2)
data = sp_list.GetListItems('All Items')
df2 = pd.DataFrame(data)
df2 = df2.drop_duplicates(subset=col2)
df2[col1] = df2[col1].astype(np.int64).astype(str)
merge = pd.merge(df1, df2[[col1, col2]], how='left', left_on = col , right_on= col2, validate='m:1', suffixes=('', '_y'))
merge[col] = np.where(pd.notnull(merge[col1]), merge[col1].astype(str).str.cat(merge[col2],sep=";#"), merge[col])
merge = merge.replace(np.nan, '', regex=True)
return merge[list(df1.columns)].to_dict('records')
def MetaData():
'''This function retreive metadat from the admin sharepoint list where there we specify the source SQL table and destination sharepoint list'''
# Connecting to the Sharepoint site where the Sharepoint_Admin lists lives.
site1 = Site(
f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/bidash/',
authcookie=authcookie)
# Reading The Sharepoiny admin input
sp_list = site1.List('Adm SqlToSharepoint')
# Reading the last records in the SQltoshqrepoint list (one sql table at time )
data = sp_list.GetListItems('All Items')
return data
def mySharepoint():
cred = HttpNtlmAuth(config.USERNAME, config.PASSWORD)
site = Site('https://applications.level3.com/sites/ProactiveRehabTracking',
auth=cred)
sp_list = site.List(
'Proactive Rehab Tracking List') # Get the List by Name
data = sp_list.GetListItems('All Items') # Called 'AllItems' In Sharepoint
df = pd.DataFrame(data[0:]) # Brings in 'live' Sharepoint to DataFrame
return df
def ListsTobePushed(Sp, li):
site1 = Site(f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{Sp}/', authcookie=authcookie, huge_tree=True)
# Reading The Sharepoiny admin input
sp_list = site1.List(li)
# Reading the last records in the SQltoshqrepoint list (one sql table at time )
data = sp_list.GetListItems('All Items')
if type(data) is list:
# print(len(data))
print("this is small list...getting data.....")
return data
else :
# retreiving the header of the list
print("large list skipping the try...pushing...")
return largeList(sp_list)
def sp_conn(data):
_SP = data['SP_Site']
_SPL = data['SP_List']
# Connecting to the destination sharepoint list with try
try:
site = Site(
f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{_SP}/',
authcookie=authcookie)
except Exception as e:
print(e)
# reading the the desitination Sharepoint list
mylist1 = site.List(_SPL)
return mylist1
def sharepoint_list(Secrets):
"""
Connection to SharePoint API with password authentication
"""
username = Secrets['sharepoint']['user_name']
password = Secrets['sharepoint']['password']
web_site = Secrets['sharepoint']['web_site']
my_site = Secrets['sharepoint']['my_site']
authcookie = Office365(my_site, username=username,
password=password).GetCookies()
site = Site(web_site, authcookie=authcookie)
result = site.List('Risk Registry').GetListItems()
return result
def pushToSP(_SP, _SPL, source_c, dest_c):
try:
site = Site(
f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{_SP}/',
authcookie=authcookie)
except Exception as e:
print(e)
mydata = ListsTobePushed(source_c, _SPL)
for d in mydata:
# d = dict_clean(d)
# d.get(dest_c, "empty")
# d[dest_c] = d.pop(source_c, None)
d[dest_c] = d.get(source_c, None)
print(f"After Renaming my column \n {mydata[:10]}")
# reading the the desitination Sharepoint list
mylist1 = site.List(_SPL)
# Updating the column
mylist1.UpdateListItems(data=mydata, kind='Update')
print(f'---------------Done -------------')
def pushToSP(_SP, _SPL, mydata):
# try:
site = Site(f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{_SP}/', authcookie=authcookie)
# except Exception as e : print(e)
# reading the the desitination Sharepoint list
mylist1 = site.List(_SPL)
if len(mydata)>20000:
n=0
j = 20000
print("Starting batches ...........")
while len(mydata)> 0 :
chunk = mydata[n:j]
mylist1.UpdateListItems(data=chunk, kind='New')
print(f"Completed 1st {j} batch-------------")
n = n + 20000
j = j + 20000
else :
mylist1.UpdateListItems(data=mydata, kind='New')
print(f'---------------Done -------------')
def UpdatesToSP(data, mydata):
'''This function push python object to SharePoint lis, its takes tow argumnets both should dictionaries(object) the first one should containe the target site and list, the second one should contain the data to be pushed to the list'''
_SP = data['SP_Site']
_SPL = data['SP_List']
col = data['Identity']
print(f'Adding updated records to Sharepoint List... : {_SPL}....')
# Connecting to the destination sharepoint list with try
try:
site = Site(
f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{_SP}/',
authcookie=authcookie)
except Exception as e:
print(e)
# reading the the desitination Sharepoint list
mylist1 = site.List(_SPL)
# # # Adding the new Data to the sharepoint list if data is more than 20000, break it down to batches
ks = list(mydata[0].keys())
for i, d in enumerate(ks):
if 'Name' in d:
ks[i] = 'Name_'
all_d = [set({k: d[k] for k in ks}.items()) for d in listObject(mylist1)]
print(type(all_d))
print(all_d[0])
mydata_d = [set(d.items()) for d in mydata]
print(type(mydata_d))
print(mydata_d[0])
if set(mydata_d).issubset(set(all_d)):
print("No Updates")
else:
for d in mydata:
val = d[col]
d['ID'] = GenID(mylist1, col, val)
DatPush(mylist1, mydata, 'Update')
'Limited Term Lecturer', 'M/P Professional', 'Service', 'Support',
'Clinical/Research', 'Intern', 'Residence Hall Counselor', 'Student'
]
logging.info('Starting up...')
fake = Faker()
authcookie = Office365('https://purdue0.sharepoint.com',
username='',
password='').GetCookies()
site = Site('https://purdue0.sharepoint.com/sites/HRIS',
version=Version.o365,
authcookie=authcookie)
test_list = site.List('ELT-Test')
employees = []
supervisor = None
count = 0
for x in range(1, 10001):
if supervisor == None or x % randint(3, 7) == 0:
# print ("SUPER: {}\t COUNT: {}".format(supervisor, str(count)))
count = 1
supervisor = fake.name()
else:
count += 1
employee = {}
employee['Title'] = str(x)
employee['Employee PERNR'] = str(x)
from shareplum import Site
from shareplum import Office365
from shareplum.site import Version
# DICT WITH THE INFO THAT YOU NEED TO FILL THE FORM INTO THE LIST
upload_dict = {"ID":4,"ClientName":"JOHN RED","StreetAdrress":"120 BROADWAY","Created":"2020-05-05"}
# Authenticate to Office365 Sharepoint
authcookie = Office365('https://YOUR-NAME.sharepoint.com', username='******',password='******').GetCookies()
site = Site('https://YOUR-NAME.sharepoint.com/sites/YOUR-SITE/', version=Version.v2016, authcookie=authcookie)
sp_list = site.List('YOUR-LIST')
# GET ALL LIST EXIST OF MY ACCOUNT
sp_data = sp_list.GetListItems()
# CHECK IF LIST IS ALREADY EXIST
sp_dataListItems = sp_list.GetListItems(fields=['ID', 'Title'])
for spResultData in sp_dataListItems:
id = spResultData.get("ID")
Title = spResultData.get("Title")
# IF ALREADY EXIST MADE UPDATE OF THE LIST IF DOESNT EXIST MADE NEW LIST
if id == "1" and Title == 'TEST':
update_date = [upload_dict]
sp_list.UpdateListItems(data=update_date, kind='Update')
else:
update_date = [upload_dict]
sp_list.UpdateListItems(data=update_date, kind='New')
# setting up Passwords
sh_pwd = secret.sharepoint_password
sq_pwd = secret.password
# setting up the authentication to sharepint site
authcookie = Office365('https://foundationriskpartners.sharepoint.com',
username=secret.sharepoint_username,
password=sh_pwd).GetCookies()
# Setting up the connection to sharepoint site
site1 = Site(
f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/bidash/',
authcookie=authcookie)
# Connecting to the SharePoint_Admin list
sp_list1 = site1.List('Sharepoint_Admin')
data1 = sp_list1.GetListItems('All Items')
# ------------------------------------------------------------------------------------
def PushToSql(df, db, Insert_Methode):
'''This function pushes the supplaied daatfarme to the supplied db and insert methode (replace or append)'''
engine = create_engine(
f"mssql+pyodbc://{secret.user}:{sq_pwd}@{secret.server}:1433/{db}?driver=SQL+Server+Native+Client+11.0",
fast_executemany=True)
print(f'inserting {li} into {db}')
df.to_sql(dest,
engine,
schema=schema,
if_exists=Insert_Methode,
index=False)
self.item = item
self.email = ""
user = config('iptvmail')
pwd = config('pwd')
emailist = []
authcookie = Office365('https://reutlingenuniversityde.sharepoint.com/',
username=config('username'),
password=config('password')).GetCookies()
site = Site('https://reutlingenuniversityde.sharepoint.com/sites/Test01',
authcookie=authcookie)
sp_list = site.List('KK_Test_List_01')
sp_list1 = site.List('Taschen_Liste')
fields = ['Person', 'Item']
fields1 = ['Person', 'Tasche']
query = {'Where': [('Lt', 'EndDate', str(date.today()))]}
data = pd.DataFrame(sp_list.GetListItems(fields=fields,
query=query)[0:]).dropna(thresh=1)
data1 = pd.DataFrame(sp_list1.GetListItems(fields=fields1,
query=query)[0:]).dropna(thresh=1)
data.reset_index(drop=True, inplace=True)
data1.reset_index(drop=True, inplace=True)
dpd = data.append(data1.rename(columns={"Tasche": "Item"}),
#Sharepoint site information
server_url = "https://ytpl.sharepoint.com"
site_url = "https://ytpl.sharepoint.com/sites/demosite/PowerApps/"
username = ""
passwd = ""
leave_balance_list = 'lm_emp_leave_balance_record'
leave_records_list = 'lm_employee_leave_record'
#Get Authentication cookies
authcookie = Office365(server_url, username=username,
password=passwd).GetCookies()
site = Site(site_url, authcookie=authcookie)
#Get all the data from sharepoint list which holds employess leave balance record
sp_list = site.List(leave_balance_list)
#Get all the data from sharepoint list which holds employees leave records
leave_records = site.List(leave_records_list)
# Create an object of Flask Class and configure it run in debug mode
app = flask.Flask(__name__)
app.config["DEBUG"] = True
#------------------------------------------------------------------------------------------------------
#Route to get leave balance based on user id (email address - for example [email protected]"
#-------------------------------------------------------------------------------------------------------
@app.route('/leaveapi/v1/balance', methods=['GET'])
def all_leave_balances():
#Check if an id is passed as query string and read the data from sharepoint based on the value of id passed
# lookupFields = ['BusinessUnit', 'OperatingUnit', 'DealName', 'FRPS_DataSourceSK']
# for item in mydata:
# if 'Name' in item:
# item['Name_'] = item.pop('Name')
# for it in lookupFields:
# item[it]= lookupFormat(item[it])
print(f'updating Sharepoint List... : {_SPL}....')
# Connecting to the destination sharepoint list with try
print(mydata[0])
try:
site = Site(
f'https://foundationriskpartners.sharepoint.com.us3.cas.ms/sites/{_SP}/',
authcookie=authcookie)
except Exception as e:
print(e)
# print(site.GetListCollection()[0])
# Writing data to sharepoint list from sql
# reading the the desitination Sharepoint list
mylist1 = site.List(_SPL)
data1 = mylist1.GetListItems('All Items')
lists = [item['Title'] for item in site.GetListCollection()]
# Retreiving all IDs of the list if it not empty
# ids = [item['ID'] for item in data1]
# Delete all items from the sharepoint list by IDs
# mylist1.UpdateListItems(ids, kind='New')
# Adding the new Data to the sharepoint list
mylist1.UpdateListItems(data=mydata, kind='Update')
print(f'---------------Done -------------')
# -*- coding: utf-8 -*-
"""
Created on Thu Feb 20 11:22:12 2020
@author: FL014036
"*****@*****.**", "P@$$word"
"""
from shareplum import Site
from shareplum import Office365
import pandas as pd
authcookie = Office365('https://cocacolaflorida.sharepoint.com',
username='******',
password='******').GetCookies()
site = Site('https://cocacolaflorida.sharepoint.com/Distribution/',
authcookie=authcookie)
sp_list = site.List('Hazardous Material Disclosure Statement')
data = sp_list.GetListItems()
data_df = pd.DataFrame(data)
data_df = data_df[['Date', 'Drivers Name', 'Shipment number', 'DSD Location']]
class ListTestCase(unittest.TestCase):
def setUp(self):
if TEST_SETTINGS["version"] in ["2014", "2016", "2019", "365"]:
version = Version.v2016
else:
version = Version.v2007
if TEST_SETTINGS["version"] == "365":
authcookie = Office365(TEST_SETTINGS["server_url"],
username=TEST_SETTINGS["username"],
password=TEST_PASSWORD).GetCookies()
self.site = Site(TEST_SETTINGS["site_url"],
version=version,
authcookie=authcookie)
else:
auth = HttpNtlmAuth(TEST_SETTINGS["username"], TEST_PASSWORD)
self.site = Site(TEST_SETTINGS["site_url"],
version=version,
auth=auth)
self.test_list = TEST_SETTINGS["test_list"]
def tearDown(self):
self.site._session.close()
def test_a_create_list(self):
print("Create List")
self.site.AddList(self.test_list,
description='Great List!',
template_id='Custom List')
self.assertTrue(self.test_list in
[i['Title'] for i in self.site.get_list_collection()])
def test_b_get_list_fields(self):
print("Get Fields")
self.list = self.site.List(TEST_SETTINGS["test_list"])
self.assertIsNotNone(self.list.fields)
def test_c_update_list(self):
print("Update List")
self.list = self.site.List(TEST_SETTINGS["test_list"])
my_data = [{
'Title': 'First Row!'
}, {
'Title': 'Another One!'
}, {
'Title': 'Thrid Row'
}]
self.list.UpdateListItems(data=my_data, kind='New')
self.assertEqual(len(self.list.get_list_items(row_limit=5)), 3)
def test_d_delete_row(self):
print("Delete Row")
self.list = self.site.List(TEST_SETTINGS["test_list"])
my_data = [1]
self.list.UpdateListItems(data=my_data, kind='Delete')
self.assertEqual(len(self.list.get_list_items(row_limit=2)), 2)
def test_e_get_view(self):
print("Get View")
self.list = self.site.List(TEST_SETTINGS["test_list"])
self.assertEqual(len(self.list.GetListItems("All Items")), 2)
def test_f_query_list(self):
print('Test Query')
self.list = self.site.List(TEST_SETTINGS["test_list"])
query = {'Where': [('Eq', 'Title', 'Another One!')]}
items = self.list.GetListItems(fields=['Title'], query=query)
self.assertEqual(len(items), 1)
def test_g_users(self):
print("Test Users")
self.list = self.site.List(TEST_SETTINGS["test_list"])
self.assertIsNotNone(self.list.users)
self.site.delete_list(TEST_SETTINGS["test_list"])
from shareplum import Site
from shareplum import Office365
from shareplum.site import Version
from config import config
# get data from configuration
username = config['sp_user']
password = config['sp_password']
authcookie = Office365('https://xxx-my.sharepoint.com',
username=username,
password=password).GetCookies()
site = Site('https://xxx-my.sharepoint.com/personal/abc_xxx_onmicrosoft_com',
version=Version.v365,
authcookie=authcookie)
sp_list = site.List('Documents')
data = sp_list.GetListItems('All', row_limit=10)
print(data)
