def setUpClass(cls):
credentials = UserCredential(settings['user_credentials']['username'],
settings['user_credentials']['password'])
cls.client = ClientContext(
settings.get("admin_site_url")).with_credentials(credentials)
cls.tenant = Tenant(cls.client)
def login(self):
try:
self.__site_url = StringUtils.StringUtils.websiteAPI
self.__ctx = ClientContext(self.__site_url).with_credentials(
UserCredential(StringUtils.StringUtils.email, StringUtils.StringUtils.passwort))
except ValueError:
raise LoginException
def setUpClass(cls):
super(TestCommunicationSite, cls).setUpClass()
cls.user_credentials = UserCredential(settings['user_credentials']['username'],
settings['user_credentials']['password'])
cls.client = ClientContext(settings['url']).with_credentials(cls.user_credentials)
cls.site_manager = SPSiteManager(cls.client)
def connectToSite(site_url):
ctx = ClientContext(site_url).with_credentials(
UserCredential(sys.argv[1], sys.argv[2]))
web = ctx.web
ctx.load(web)
ctx.execute_query()
return ctx
def setUpClass(cls):
tenant = os.environ.get('office365_python_sdk_tenant', 'mediadev8')
admin_site_url = "https://{0}-admin.sharepoint.com/".format(tenant)
credentials = UserCredential(settings['user_credentials']['username'],
settings['user_credentials']['password'])
cls.client = ClientContext(admin_site_url).with_credentials(credentials)
cls.tenant = Tenant(cls.client)
def fetch_file_sharepoint(email, password, site_url, file_name, file_url):
ctx = ClientContext(site_url).with_credentials(
UserCredential(email, password))
download_path = os.path.join(tempfile.mkdtemp(),
os.path.basename(file_url))
with open('./data_sources/' + file_name, "wb") as local_file:
file = ctx.web.get_file_by_server_relative_url(file_url).download(
local_file).execute_query()
print("[Ok] file has been downloaded: {0}".format(download_path))
def setUpClass(cls):
super(TestTeamSite, cls).setUpClass()
user_credentials = UserCredential(
settings['user_credentials']['username'],
settings['user_credentials']['password'])
cls.client = ClientContext(
settings['url']).with_credentials(user_credentials)
cls.site_manager = GroupSiteManager(cls.client)
def setUpClass(cls):
super(TestDirectorySession, cls).setUpClass()
user_credentials = UserCredential(
settings['user_credentials']['username'],
settings['user_credentials']['password'])
cls.client = ClientContext(
settings['url']).with_credentials(user_credentials)
cls.session = DirectorySession(cls.client)
def setUpClass(cls):
credentials = UserCredential(
user_name=settings['user_credentials']['username'],
password=settings['user_credentials']['password'])
cls.client = ClientContext(
settings['url']).with_credentials(credentials)
current_user = cls.client.web.current_user.get().execute_query()
cls.target_user = current_user
def run(file_path):
download_path = file_path
user_credentials = UserCredential(settings['username'], settings['password'])
ctx = ClientContext(settings['url']).with_credentials(user_credentials)
target_web = ctx.web.get().execute_query()
print(target_web.url)
with open(download_path, "wb") as local_file:
ctx.web.get_file_by_id(file_id).download(local_file).execute_query()
print("[Ok] file has been downloaded: {0}".format(download_path))
def with_user_credentials(self, username, password, allow_ntlm=False, browser_mode=False):
"""
Assigns credentials
:type username: str
:type password: str
:type allow_ntlm: bool
:type browser_mode: bool
"""
self.authentication_context.register_provider(UserCredential(username, password), allow_ntlm=allow_ntlm,
browser_mode=browser_mode)
return self
def __init__(self, *args, **kwargs) -> None:
self.relative_url = kwargs.get('relative_url', None)
self.site_path = kwargs.get('url', config.SHAREPOINT_TENANT)
if config.SHAREPOINT_CONNECTION == 'app':
client_id = kwargs.get('client_id', config.SHAREPOINT_CLIENT_ID)
client_secret = kwargs.get('client_secret',
config.SHAREPOINT_CLIENT_SECRET)
credentials = ClientCredential(client_id, client_secret)
elif config.SHAREPOINT_CONNECTION == 'user':
username = kwargs.get('username', config.SHAREPOINT_USERNAME)
password = kwargs.get('password', config.SHAREPOINT_PASSWORD)
credentials = UserCredential(username, password)
else:
raise SharePointClientException('Invalid connection type')
self.folder = kwargs.get('folder', 'Documents')
self.context = ClientContext(
self.site_path).with_credentials(credentials)
def acquire_token_password_grant(self, client_id, username, password,
resource, scope):
"""
Acquire token via resource owner password credential (ROPC) grant
:param str resource: A URI that identifies the resource for which the token is valid.
:param str username: : The username of the user on behalf this application is authenticating.
:param str password: The password of the user named in the username parameter.
:param str client_id: str The OAuth client id of the calling application.
:param list[str] scope:
"""
self.provider = OAuthTokenProvider(self.url)
return self.provider.acquire_token_password_type(
resource=resource,
client_id=client_id,
user_credentials=UserCredential(username, password),
scope=scope)
def load_remote_to_dataframe(self) -> pd.DataFrame:
if (not self._abs_file_url) | (not self._username) | (
not self._password) | (not self._url) | (not self._document):
data = f'- username: {self._username}\n' \
f'- password: {self._password}\n' \
f'- url: {self._url}\n' \
f'- document: {self._document}\n' \
f'- full_url: {self._abs_file_url}\n'
raise Exception(f'Dati richiesti mancanti\n{data}')
user_credentials = UserCredential(self._username, self._password)
with tempfile.TemporaryDirectory() as local_path:
file_name = os.path.basename(self._abs_file_url)
with open(os.path.join(local_path, file_name), 'wb') as local_file:
file = File.from_url(self._abs_file_url).with_credentials(
user_credentials).download(local_file).execute_query()
# print("'{0}' file has been downloaded into {1}".format(file.serverRelativeUrl, local_file.name))
df = pd.read_excel(local_file.name,
engine='openpyxl',
converters={'ProvinciaResidenza': str})
df['DataDiNascita'] = pd.to_datetime(
df.DataNascita).dt.strftime('%Y-%m-%d')
return df
def eventFromSharePoint():
collection = db['calendar']
calendar_url = "https://mirageworks.sharepoint.com/sites/msteams_a0f4c8/_api/web/lists(guid'%s')/items" %(ACCOUNT['guid'])
ctx = ClientContext(calendar_url).with_credentials(UserCredential(ACCOUNT['email'], ACCOUNT['password']))
request = RequestOptions(calendar_url)
response = ctx.execute_request_direct(request)
json_data = json.loads(response.content)
for i, data in enumerate(json_data['d']['results']):
event = {}
event['title'] = data['Title']
event['start'] = data['EventDate']
event['end'] = data['EndDate']
event['id'] = data['ID']
event['allday'] = data['fAllDayEvent']
event['every'] = data['fRecurrence']
if data['fRecurrence']:
collection.update_one({'id': event['id']}, {'$set': event}, upsert=True)
else:
collection.update_one({'id': event['id']}, {'$set': event}, upsert=True)
def get_data_from_excel(self, relative_url, worksheet_name, header_row,
start_row):
# adapted from: https://stackoverflow.com/a/69292234/14125255
# (accessed 2021-10-15)
ctx = ClientContext(self.conn.site_url).with_credentials(
UserCredential(self.conn.user, self.conn.password))
response = File.open_binary(ctx, relative_url)
bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0)
ws = load_workbook(bytes_file_obj, data_only=True)[worksheet_name]
headers = {
ws.cell(row=header_row, column=col).value: col
for col in range(1, ws.max_column + 1)
if ws.cell(row=header_row, column=col)
}
data = [{
k: ws.cell(row=row, column=v).value
for k, v in headers.items()
} for row in range(start_row, ws.max_row + 1)]
return data
from settings import settings
from office365.runtime.auth.user_credential import UserCredential
from office365.sharepoint.client_context import ClientContext
ctx = ClientContext(settings["url"]).with_credentials(
UserCredential(
settings.get('user_credentials').get('username'),
settings.get('user_credentials').get('password')))
web = ctx.web
ctx.load(web)
ctx.execute_query()
print(web.properties["Url"])
return value
def load_settings():
cp = ConfigParser(interpolation=SecEnvInterpolation())
root_dir = os.path.dirname(os.path.abspath(__file__))
config_file = os.path.join(root_dir, 'settings.cfg')
cp.read(config_file)
return cp
settings = load_settings()
# shortcuts
test_tenant = settings.get('default', 'tenant')
test_client_credentials = ClientCredential(
settings.get('client_credentials', 'client_id'),
settings.get('client_credentials', 'client_secret'))
test_user_credentials = UserCredential(
settings.get('user_credentials', 'username'),
settings.get('user_credentials', 'password'))
test_site_url = settings.get('default', 'site_url')
test_team_site_url = settings.get('default', 'team_site_url')
test_admin_site_url = settings.get('default', 'admin_site_url')
test_user_principal_name = settings.get('users', 'test_user1')
test_user_principal_name_alt = settings.get('users', 'test_user2')
import os
from settings import settings
from office365.runtime.auth.user_credential import UserCredential
from office365.sharepoint.client_context import ClientContext
credentials = UserCredential(settings['user_credentials']['username'],
settings['user_credentials']['password'])
ctx = ClientContext(settings['url']).with_credentials(credentials)
target_url = "/Shared Documents"
target_folder = ctx.web.get_folder_by_server_relative_url(target_url)
size_chunk = 1000000
local_path = "../../../tests/data/big_buck_bunny.mp4"
# local_path = "../../../tests/data/SharePoint User Guide.docx"
file_size = os.path.getsize(local_path)
def print_upload_progress(offset):
print("Uploaded '{}' bytes from '{}'...[{}%]".format(offset, file_size, round(offset / file_size * 100, 2)))
uploaded_file = target_folder.files.create_upload_session(local_path, size_chunk, print_upload_progress)
ctx.execute_query()
print('File {0} has been uploaded successfully'.format(uploaded_file.serverRelativeUrl))
def setUpClass(cls):
credentials = UserCredential(settings['user_credentials']['username'],
settings['user_credentials']['password'])
cls.my_client = ClientContext(
settings.get('team_site_url')).with_credentials(credentials)
def setUpClass(cls):
super(TestSearch, cls).setUpClass()
user_credentials = UserCredential(settings['user_credentials']['username'],
settings['user_credentials']['password'])
cls.client = ClientContext(settings['url']).with_credentials(user_credentials)
cls.search = SearchService(cls.client)
def listFolder(path):
root = ctx.web.get_folder_by_server_relative_url(path)
ctx.load(root)
ctx.execute_query()
folders = root.folders
ctx.load(folders)
ctx.execute_query()
for folder in folders:
print(folder.properties["ServerRelativeUrl"])
listFolder(folder.properties["ServerRelativeUrl"])
files = root.files
ctx.load(files)
ctx.execute_query()
for file in files:
print(file.properties["ServerRelativeUrl"])
site_url = "https://vutbr.sharepoint.com/sites/IZU/"
ctx = ClientContext(site_url).with_credentials(
UserCredential(sys.argv[1], sys.argv[2]))
web = ctx.web
ctx.load(web)
ctx.execute_query()
listFolder('Sdilene dokumenty/Přednášky - úterý od 13 hodin/Recordings/')
def __init__(self, site_url, username, password):
self.site_url = site_url
self.ctx = ClientContext(site_url).with_credentials(
UserCredential(username, password))
# create_file
# log and variables
log_file = create_file("logs", "spo_list_item", "log")
report_file = create_file("report", "spo_list_item", "csv")
site_url = "https://techwizard.sharepoint.com/sites/TeamSIte/"
sp_list = "DL Modification"
admin_user = "******"
admin_password = "******"
write_log("start.....script", log_file)
try:
ctx = ClientContext(site_url).with_credentials(
UserCredential(admin_user, admin_password))
sp_lists = ctx.web.lists
s_list = sp_lists.get_by_title(sp_list)
l_items = s_list.get_items()
ctx.load(l_items)
ctx.execute_query()
write_log("Fetched list items", log_file)
with open(report_file, mode='w', newline='', encoding='utf-8') as csv_file:
fieldnames = ['ID', 'Title', 'Check']
writer = csv.DictWriter(csv_file, fieldnames=fieldnames)
writer.writeheader()
for item in l_items:
write_log("exporting..." + str(item.properties['ID']), log_file)
writer.writerow({
'ID': item.properties['ID'],
'Title': item.properties['Title'],
#Con este script la idea es poder descargar archivos contenidos en un Sharepoint
from office365.runtime.auth.user_credential import UserCredential
from office365.sharepoint.client_context import ClientContext
import os
import tempfile
site_url= 'https://empresa.sharepoint.com/sites/Test' #URL del Sharepoint
ctx = ClientContext(site_url).with_credentials(UserCredential("usuario","contraseña"))#Credenciales del Sharepoint
file_url='/sites/Test/Documentos compartidos/Vtas(prueba).xlsx' #Pasamos la Otra parte del Sharepoint donde esta el recurso, fijarse de tener en cuenta los espacios en la URL
ruta='C:/Users/Dell/Downloads' #Establecemos la ruta donde queremos que nos descargue el Excel
download_path = os.path.join(ruta, os.path.basename(file_url))
with open(download_path, "wb") as local_file:
file = ctx.web.get_file_by_server_relative_path(file_url).download(local_file).execute_query()
print("[Ok] file has been downloaded: {0}".format(download_path))
#C/este Script lo que vamos hacer es descargar un Excel de un sharepoint y cargarlo a slq server
from office365.runtime.auth.user_credential import UserCredential
from office365.sharepoint.client_context import ClientContext
import os
import tempfile
site_url = 'https://Empresa.com/sites/CLIENTES-MERCADOS' #URL del Sharepoint
ctx = ClientContext(site_url).with_credentials(
UserCredential("Usuario", "Contraseña")) #Credenciales del Sharepoint
file_url = '/sites/CLIENTES-MERCADOS/Documentos compartidos/Clientes - Mercados.xlsx' #Pasamos la Otra parte del Sharepoint donde esta el recurso, fijarse de tener en cuenta los espacios en la URL
ruta = 'C:/Users/Dell/Downloads' #Establecemos la ruta donde queremos que nos descargue el Excel
download_path = os.path.join(ruta, os.path.basename(file_url))
with open(download_path, "wb") as local_file:
file = ctx.web.get_file_by_server_relative_path(file_url).download(
local_file).execute_query()
print("[Ok] file has been downloaded: {0}".format(download_path))
#Ahora cargamos el Excel que se descargo del Sharepoint a un DataFrame
df = pd.read_excel(r'C:/Users/Dell/Downloads/Vtas(prueba).xlsx',
sheet_name='Hoja1')
print(df)
#COnectamos a nuestra BD
conexion = pb.connect('Driver={SQL Server};'
'Server=Nombre SErvidor;'
'Database=Destino;'
'Trusted_Connection=yes;')
cursor = conexion.cursor()
#Procedemos a cargar todos los registros del Excel a nuestra tabla
for row in df.itertuples():
#C/este Script podemos cargar archivos a un sharepoint
import os
from office365.runtime.auth.user_credential import UserCredential
from office365.sharepoint.client_context import ClientContext
import test
#URL del Sharepoint
site_url = 'https://Empresa.sharepoint.com/sites/Test'
ctx = ClientContext(site_url).with_credentials(
UserCredential("Usuario", "Pass"))
path = 'D:/Practicas/ETL_Python/JunioVtas.CSV.txt'
with open(path, 'rb') as content_file:
file_content = content_file.read()
list_title = "Documentos"
target_folder = ctx.web.lists.get_by_title(list_title).root_folder
name = os.path.basename(path)
target_file = target_folder.upload_file(name, file_content)
ctx.execute_query()
print("File has been uploaded to url: {0}".format(
target_file.serverRelativeUrl))
