Forum Discussion
T-SQL magic cell with parameters in for loop Fabric Python Notebook
Hi austworks19,
Thanks for reaching out to the Microsoft fabric community forum. You're absolutely right Fabric notebooks using spark.sql() are scoped to the default lakehouse of the Spark session, and unfortunately, you can't dynamically switch workspaces or lakehouses mid-session using Spark commands.
Your workaround of constructing the full abfss path and replacing table references with backtick-enclosed paths is spot on and probably the most flexible approach for your use case.
As for using the %%sql magic function from the blog you linked, you're also correct that it requires separate cells for each query, which doesn’t play well with loops or dynamic execution.
If you're looking to keep everything inside a loop, here’s a suggestion that continue using spark.sql() but dynamically rewrite each query to use the full abfss path. You can store the rewritten queries in a list and iterate through them, executing each with spark.sql().
This way, you avoid the cell-per-query limitation and still get access to the correct lakehouse data.
I would also take a moment to thank tackytechtom , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
I'm doing something like the below code to loop through TSQL commands in a python notebook. I rewrote it slightly so it has ficticious data, but I think you'll get the point after seeing the example. Creating views have to be done one at a time, so what I did was for a different purpose, but similar situation in that they need to interate TSQL for loop.....
from IPython import get_ipython
import sempy.fabric as fabric
ipython = get_ipython()
# Example test input
view_scripts = {
"vw_SalesOrders": (
"CREATE OR ALTER VIEW [gold_sales].[vw_SalesOrders] AS\n"
"SELECT OrderID, CustomerID, OrderDate, TotalAmount\n"
"FROM bronze_orders.Orders",
"Sample Workspace",
"Sample Lakehouse"
),
"vw_Products": (
"CREATE OR ALTER VIEW [gold_sales].[vw_Products] AS\n"
"SELECT ProductID, ProductName, Category, Price\n"
"FROM bronze_catalog.Products",
"Sample Workspace",
"Sample Lakehouse"
),
"vw_Customers": (
"CREATE OR ALTER VIEW [gold_sales].[vw_Customers] AS\n"
"SELECT CustomerID, FirstName, LastName, Email\n"
"FROM bronze_customers.Customers",
"Sample Workspace",
"Sample Lakehouse"
)
}
# Create views
for view_name, (view_script, workspace_name, item_name) in view_scripts.items():
print(f"\nCreating view: {view_name} in {item_name} of {workspace_name}")
try:
workspace_id = fabric.resolve_workspace_id(workspace_name)
run_info = f"-artifact {item_name} -type Lakehouse -workspace {workspace_id}"
# Run the SQL via Fabric T-SQL magic
result = ipython.run_cell_magic("tsql", run_info, view_script)
print(f"Successfully created view: {view_name} on {item_name}")
except Exception as e:
print(f"Failed to create view {view_name} on {item_name}: {e}")