[Docs]: Connect to Microsoft Fabric Warehouse

[korawica]

Connect to Microsoft Fabric Warehouse

import os 
from sqlalchemy import create_engine
import pandas as pd
import time
from datetime import datetime

CLIENT_ID = '<client_id>'
CLIENT_SECRET = '<client_secret>'
server = '<server>'
database = '<database>'

connection_string = (
  f"mssql+pyodbc://{CLIENT_ID}:{CLIENT_SECRET}@{server}:1433/{database}"
  f"?driver={{ODBC Driver 18 for SQL Server}}"
  f"&authentication=ActiveDirectoryServicePrincipal"
  f"&timeout=120"
  f"&Encrypt=yes"
  f"&TrustServerCertificate=no"
) 

engine2 = create_engine(connection_string, connect_args={'timeout': 120})
query = "select * from dwh_ai.dbo.Employee"
df = pd.read_sql_query(query, engine2)
print(df)

import pandas as pd
import sqlalchemy as sa
from sqlalchemy import create_engine, MetaData, Table, select
import struct
from itertools import chain, repeat
import pyodbc
import urllib
from azure import identity

# authentication
resource_url = "https://database.windows.net/.default"
azure_credentials = identity.DefaultAzureCredential()
token_object = azure_credentials.get_token(resource_url)

# prepare connection string
sql_endpoint = "xxxxxx.datawarehouse.fabric.microsoft.com" 
database = "dwh_ai" 

connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server={sql_endpoint},1433;Database={database};Encrypt=Yes;TrustServerCertificate=No"
params = urllib.parse.quote(connection_string)

# Retrieve an access token
token_as_bytes = bytes(token_object.token, "UTF-8") # Convert the token to a UTF-8 byte string
encoded_bytes = bytes(chain.from_iterable(zip(token_as_bytes, repeat(0)))) # Encode the bytes to a Windows byte string
token_bytes = struct.pack("<i", len(encoded_bytes)) + encoded_bytes # Package the token into a bytes object
attrs_before = {1256: token_bytes}  # Attribute pointing to SQL_COPT_SS_ACCESS_TOKEN to pass access token to the driver

# build the connection
engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={0}".format(params), connect_args={'attrs_before': attrs_before})

# SQL query execution using Pandas
df = pd.read_sql("select * from dwh_ai.dbo.Employee", engine)
print(df)

References

• Connect to Microsoft Fabric Warehouse using Python and SQLAlchemy