[Python] Access an excel file in sharepoint using python to ingest into SQL sever using a...

I presently have a .env file which holds the sql login credentials and the sharepoint login credentials.

My code below fails in the second section of Step two with the following error:

An error occurred while retrieving token from XML response: AADSTS53003: Access has been blocked by Conditional Access policies. The access policy does not allow token issuance.
​

from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from sqlalchemy import create_engine
import pandas as pd
from io import BytesIO
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

# SharePoint credentials & file path
SHAREPOINT_SITE = os.getenv("SHAREPOINT_SITE")
SHAREPOINT_USERNAME = os.getenv("SHAREPOINT_USERNAME")
SHAREPOINT_PASSWORD = os.getenv("SHAREPOINT_PASSWORD")
SHAREPOINT_FILE_URL = os.getenv("SHAREPOINT_FILE_URL") # Must be a server-relative path

# SQL Server connection details
SQL_SERVER = os.getenv("SQL_SERVER")
SQL_DATABASE = os.getenv("SQL_DATABASE")
SQL_USERNAME = os.getenv("SQL_USERNAME")
SQL_PASSWORD = os.getenv("SQL_PASSWORD")
SQL_DRIVER = os.getenv("SQL_DRIVER") # e.g., "ODBC Driver 17 for SQL Server"
TABLE_NAME = os.getenv("TABLE_NAME")

# Step 1: Authenticate to SharePoint
ctx_auth = AuthenticationContext(SHAREPOINT_SITE)
if not ctx_auth.acquire_token_for_user(SHAREPOINT_USERNAME, SHAREPOINT_PASSWORD):
raise Exception("SharePoint authentication failed")

# Step 2: Download Excel file to memory
ctx = ClientContext(SHAREPOINT_SITE, ctx_auth)
file_obj = BytesIO()

# Get the file from SharePoint using the server-relative URL
ctx.web.get_file_by_server_relative_url(SHAREPOINT_FILE_URL).download(file_obj).execute_query()

# Step 3: Load the Excel file into a pandas DataFrame
file_obj.seek(0) # Reset the pointer back to the beginning of the file
df = pd.read_excel(file_obj)

# Step 4: Connect to SQL Server and insert data
connection_string = f"mssql+pyodbc://{SQL_USERNAME}:{SQL_PASSWORD}@{SQL_SERVER}/{SQL_DATABASE}?driver={SQL_DRIVER}"
engine = create_engine(connection_string)

# Write the data to SQL Server (replace table or append)
df.to_sql(TABLE_NAME, con=engine, if_exists="replace", index=False)

print(f"Data from {SHAREPOINT_FILE_URL} has been successfully uploaded to {TABLE_NAME} in SQL Server.")


I have spoken to our infrastructure team thinking this could be due to MFA blocking entry into SharePoint however I am using my credentials and in the office, so there is no MFA/2FA request.

I have previously looked at sharepoint: Access has been blocked by Conditional Access policies . There is no answer on this question, the post does suggest not using user credentials however suggests this doesn't work in anycase.

Any help would be appreciated.

Continue reading...