T-SQL magic cell with parameters in for loop Fabric Python Notebook

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}")
​

Hi austworks19 ,

That's a cool use case!

Couldn't you do something like this? (note, this is pseudo code 🙂 )

for workspace in workspaces:
    for schema in schemas: 
        for table in tables:
             table_name = f"{workspace.name}.{schema.name}.{table.name}"
             spark.sql(f"SELECT * FROM {table_name}")
             

Essentially, just use the full identifier of the table? If this does not work, you might wanna try out to add some spark.sql(f"USE [catalog}.{schema}").

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/