Forum Discussion
Solved
Notebook to query Synapse Serverless SQL
I'm struggling with Fabric Notebooks - attempting a basic python notebook not a spark notebook. I want to connect and pull data from Synapse Serverless SQL. Presumably, this should be fairly well...
Here is what worked in the end for me, maybe this helps someone else.
def query_synapse(db, query_string): """ Function to query synapse """ # Synapse serverless SQL endpoint server = "<yourserver>.sql.azuresynapse.net" database = "<yourdb>" driver = "ODBC Driver 18 for SQL Server" connection_string = f""" Driver={driver}; Server={server}; Database={database}; """ token = notebookutils.credentials.getToken('https://database.windows.net/').encode("UTF-16-LE") token_struct = struct.pack(f"<I{len(token)}s", len(token), token) conn = pyodbc.connect(connection_string, attrs_before={1256:token_struct}) cursor = conn.cursor() cursor.execute(query_string) # Get column names columns = [column[0] for column in cursor.description] # Fetch data rows = cursor.fetchall() # Convert to DataFrame df = pd.DataFrame.from_records(rows, columns=columns) return df
I gave it a try but doesn't allow with an authentication method and a token- removing the auth method but keeping your encoding also doesn't work.
Error: ('FA005', '[FA005] [Microsoft][ODBC Driver 18 for SQL Server]Cannot use Access Token with any of the following options: Authentication, Integrated Security, User, Password. (0) (SQLDriverConnect)')
Here is what worked in the end for me, maybe this helps someone else.
def query_synapse(db, query_string):
"""
Function to query synapse
"""
# Synapse serverless SQL endpoint
server = "<yourserver>.sql.azuresynapse.net"
database = "<yourdb>"
driver = "ODBC Driver 18 for SQL Server"
connection_string = f"""
Driver={driver};
Server={server};
Database={database};
"""
token = notebookutils.credentials.getToken('https://database.windows.net/').encode("UTF-16-LE")
token_struct = struct.pack(f"<I{len(token)}s", len(token), token)
conn = pyodbc.connect(connection_string, attrs_before={1256:token_struct})
cursor = conn.cursor()
cursor.execute(query_string)
# Get column names
columns = [column[0] for column in cursor.description]
# Fetch data
rows = cursor.fetchall()
# Convert to DataFrame
df = pd.DataFrame.from_records(rows, columns=columns)
return df