Code to query a sql endpoint from a notebook. Cut and paste into a cell, update the sql_endpoint and database variables.

Query_SQL_Endpoint.py

import pandas as pd
import struct
import sqlalchemy
import pyodbc
import notebookutils
def create_engine(connection_string : str):
    token = notebookutils.credentials.getToken('https://analysis.windows.net/powerbi/api').encode("UTF-16-LE")
    token_struct = struct.pack(f'<I{len(token)}s', len(token), token)
    SQL_COPT_SS_ACCESS_TOKEN = 1256
    return sqlalchemy.create_engine("mssql+pyodbc://", creator=lambda: pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct}))
sql_endpoint = "__you_lake_house_address_dot_com"
database ="__name_of_the_lakehouse_"
connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server={sql_endpoint},1433;Database=f{database};Encrypt=Yes;TrustServerCertificate=No"
engine = create_engine(connection_string)
 
df = pd.read_sql_query("SELECT * from sys.objects where type = 'U' order by create_date desc", engine)
#display(df)