On-Premise Python Code to Load Data to Microsoft Fabric Warehouse using Token based Authentication
This blog post includes the Python script to load file data from an on-premises SQL Server to Microsoft Fabric Warehouse. The data is pushed using an on-premise Python script with MFA authentication using an authentication token(OAuth). The Token has been generated using Azure App. The app is registered on portal.azure.com. The append incremental/delta load method has been used to push new data.
You can download the below CSV files for testing
https://github.com/amitchandakpbi/powerbi/raw/main/csv/
The video:
The SQL code: Create Table
CREATE TABLE [dbo].[sales_py2](
[Order_No] [int] ,
[Item_ID] [int] ,
[Sales_Date] [datetime2](6) ,
[Customer_Id] [int] ,
[City_Id] [int] ,
[Qty] [decimal](18, 10) ,
[Price] [decimal](18, 10) ,
[COGS] [decimal](18, 10) ,
[Discount_Percent] [decimal](18, 10)
)The Python code
import pandas as pd
import sqlalchemy as sa
import urllib
import struct
import requests
import json
def get_access_token(app_id,client_secret,directory_id,user_name,password):
"""It will create a access token to access the mail apis"""
app_id = app_id #Application Id - on the azure app overview page
client_secret = client_secret
directory_id = directory_id
token_url = "https://login.microsoftonline.com/"+directory_id+"/oauth2/token"
token_data = {
"grant_type": "password",
"client_id": app_id,
"client_secret": client_secret,
"resource": "https://storage.azure.com/",
"scope":"https://graph.microsoft.com",
"username": user_name,
"password": password,
}
token_headers={
"Content-Type":"application/x-www-form-urlencoded"
}
print(token_url)
token_response = requests.post(token_url,data=token_data,headers=token_headers)
token_response_dict = json.loads(token_response.text)
error_exists = checkKey(token_response_dict,"error")
#print(token_response.text)
if error_exists == True :
raise Exception("Error in getting in access token")
else:
token = token_response_dict.get("access_token")
refresh_token = token_response_dict.get("refresh_token")
if token == None :
print("Unable to get access token")
print(str(token_response_dict))
raise Exception("Error in getting in access token")
elif refresh_token == None :
print("Unable to get refresh token")
print(str(token_response_dict))
raise Exception("Error in getting in refresh token")
else:
return token
def checkKey(dict, key):
"""It will check the given key exists in the response dictionary and return True or False"""
if key in dict.keys():
# print(dict[key])
return True
else:
return False
def checkKey(dict, key):
"""It will check the given key exists in the response dictionary and return True or False"""
if key in dict.keys():
# print(dict[key])
return True
else:
return False
print("SQL Code is called")
# get access token
access_token = get_access_token("<Application id>","<client_secret>","<directory_id>","<user_name>","<password>")
print(access_token)
# Load Table for first time
server = '<URL>'
database = 'Warehouse2'
driver='ODBC Driver 17 for SQL Server'
user= '<User>'
pass1='<pass>'
# Create a connection string for SQL Server using Windows authentication
connection_string = f'mssql+pyodbc://{user}:{pass1}@{server}/{database}?driver={driver}&Trusted_Connection=no&Authentication=ActiveDirectoryPassword&Token={access_token}'
print(connection_string)
engine = sa.create_engine(connection_string, echo=False)
sql_query = "SELECT coalesce(max(Sales_Date) ,'2018-01-01') _date FROM dbo.sales_py2"
df = pd.read_sql(sql_query, engine)
max_date = df['_date'].iloc[0]
print(max_date)
# Create the SQL query with the max_date value
query1 = f"SELECT * FROM dbo.sales_fact WHERE sales_date > '{max_date}' "
server1 = 'localhost\\SQLEXPRESS'
database1 = 'sales'
# Create a connection string for SQL Server using Windows authentication
connection_string = f'mssql+pyodbc://{server1}/{database1}?driver=SQL+Server&Trusted_Connection=yes'
# Create an engine using the connection string
engine1 = sa.create_engine(connection_string)
# Run a SQL query to get a Pandas DataFrame
df = pd.read_sql(query1, engine1)
# Print the DataFrame
#print(df)
server = '<URL>'
database = 'Warehouse2'
driver='ODBC Driver 17 for SQL Server'
user= '<User>'
pass1='<pass>'
# Create a connection string for SQL Server using Windows authentication
connection_string = f'mssql+pyodbc://{user}:{pass1}@{server}/{database}?driver={driver}&Trusted_Connection=no&Authentication=ActiveDirectoryPassword&Token={access_token}'
print(connection_string)
# Create an engine using the connection string
engine = sa.create_engine(connection_string, echo=False, connect_args={'autocommit': True}, fast_executemany=True)
# Run a SQL query to get a Pandas DataFrame
#sql_query = "SELECT TOP(10) * FROM [dbo].[Customer]"
df.to_sql(name='sales_py2', con=engine, if_exists='append',dtype={
'Order_No': sa.Integer() ,
'Item_ID': sa.Integer(),
'Sales_Date': sa.String(50) ,
'Customer_Id': sa.Integer(),
'City_Id': sa.Integer() ,
'Qty': sa.Integer(),
'Price': sa.Float(),
'COGS': sa.Float() ,
'Discount_Percent': sa.Float()}, index = False, chunksize=100, method='multi')I hope you are able to use this code with the video.
More on Microsoft Fabric:
Comprehensive Free Course for Your Team to Learn Microsoft Power BI, SQL, and Data Factory
Microsoft Fabric: A Brief Introduction
How to enable Microsoft Fabric; on Power BI Service| Start the Fabric (Preview) trial
Microsoft Fabric-What it is, Why Use, Who Should, When and How to use
Microsoft Fabric: Various Components| Microsoft Fabric Create Workspace, Lakehouse, Warehouse
Microsoft Fabric: How to load data in Lakehouse using Dataflow Gen2
Microsoft Fabric: How to load data in Warehouse using Dataflow Gen2
Microsoft Fabric: How to load data in Warehouse using Data Pipeline | End-to-End Scenario
Microsoft Fabric: How to load data in Lakehouse using Spark; Python/Notebook
Microsoft Fabric: Integrated Machine Learning- k-Means clustering
Microsoft Fabric: Create a Date table in Dataflow Gen2, and use in Lakehouse and Warehouse| Time Intelligence with Power BI
Microsoft Fabric: How to load data in Lakehouse using Data Pipeline | End-to-End Scenario
Microsoft Fabric: What are the options to load local files in Lakehouse
Microsoft Fabric: Shortcut with Fabric and for Azure Data Lake | File vs Table Shortcut
Microsoft Fabric: Import Azure SQL Data to Warehouse | Multiple tables using Pipeline
Microsoft Fabric: Incremental Data Load(ETL) for Warehouse using Dataflow Gen 2, SQL Procedure, Data Pipeline
Microsoft Fabric: How about incremental without Dataflow, Only using pipeline and SQL procedure for Microsoft Fabric Warehouse
Microsoft Fabric: Load local SQL server data in Lakehouse using on-premise Gateway and Dataflow Gen2
Microsoft Fabric: Dataflow Gen2: Native warehouse Destination- Power Query Transformation — Pivot, Unpivot, Transpose Data
Microsoft Fabric: Load Azure SQL Data to Lakehouse & Warehouse simultaneously with Dataflow Gen2
Microsoft Fabric: Load Snowflake DB Data to Lakehouse & Warehouse simultaneously with Dataflow Gen2
Microsoft Fabric: How to load local SQL server data in Warehouse using Python, Pandas, and sqlalchemy
Microsoft Fabric: How to append only Incremental data using Data Pipeline in Lakehouse
Microsoft Fabric: Load incremental data from the local SQL server to Warehouse using on-premise Python
Microsoft Fabric: Load on-premise data file to Lakehouse using on-premise Python using OAuth Token
Introduction to Microsoft Fabric in Hindi | What it is, Why to Use, Who Should, When and How to use
Microsoft Fabric in Hindi Part 2: Create Workspace, Lakehouse, Warehouse
Microsoft Fabric in Hindi Part 3: How to load data in Lakehouse using Data Pipeline | End to End Scenario
Microsoft Fabric in Hindi Part 4: How to load data in Lakehouse using Dataflow Gen 2 | End to End Scenario
Microsoft Fabric in Hindi Part 5: How to load data in Warehouse using Dataflow Gen 2 | End to End Scenario
Microsoft Fabric in Hindi Part 6: How to load data in Warehouse using Data Pipeline | End to End Scenario
Microsoft Fabric Hindi part 7: How to load data in Lakehouse using Spark; Python using the notebook
Microsoft Fabric Hindi part 8: Do k-Means clustering in spark; analyze in Power BI | Machine Learning
To take your data journey to the next level, I’ll be providing more examples in my upcoming blogs and videos.
Complete Power BI in one Video — 11 Hours
Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.
In addition, I have over 500 videos on my YouTube channel that are related to Power BI, Tableau, and Incorta. With the help of these videos, you will learn hundreds of DAX and Power Query functions, in addition to hundreds of Power BI, use cases that will assist you in becoming an expert in Power BI. Make sure you subscribe, like, and share it with your friends.
Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
Learn SQL
