Run T-SQL code in Fabric Python notebooks

The mix of T-SQL and Python in modern data workflows offers a powerful and flexible approach that blends the strengths of both languages. SQL remains the most efficient and readable way to query, filter, and join structured data, while Python excels at data transformation, statistical analysis, machine learning, and visualization. By combining T-SQL and Python, data engineers can use the best of both worlds, enabling them to build robust data pipelines that are efficient, maintainable, and capable of handling complex data processing tasks.

In Microsoft Fabric Python notebooks, we introduced a new feature called T-SQL magic command. This feature allows you to run T-SQL code directly in Python notebooks with full syntax highlighting and code completion. This means you can write T-SQL code in a Python notebook, and it will be executed as if it were a T-SQL cell. This feature is useful for data engineers who want to use the power of T-SQL while still using the flexibility of Python notebooks.

In this article, we explore the T-SQL magic command in Microsoft Fabric notebooks. We cover how to enable this command, specify which warehouse to use, and how to bind the results of T-SQL queries to Python variables.

This feature is available for Fabric Python notebooks. You need to set the language to Python in the notebook.

Important

This feature is in preview.

Using T-SQL magic command to query Fabric data warehouse

To enable the T-SQL magic command in your Fabric notebook, you need to set the %%tsql magic command at the beginning of your cell. This command indicates the code in that cell should be treated as T-SQL code.

In this example, we're using the T-SQL magic command to query a Fabric Data Warehouse. The command takes the following parameters:

• The -artifact parameter specifies the name of the data warehouse to use. The T-SQL code in the cell is executed against the specified data warehouse.
• The -type parameter specifies the type of the artifact, which in this case is a data warehouse.
• The -bind parameter specifies the name of the variable to bind the results of the T-SQL query to. In the following example, the results of the query are stored in a Python variable called df1. If you need to apply any transformation to the df1 variable, you can do so using Python code in the next cell. The -bind parameter is optional, but it's recommended to bind the results of the T-SQL query to a Python variable. This parameter allows you to easily manipulate and analyze the results using Python code.
• The -workspace parameter is optional and is used if the warehouse is located in a different workspace. Without this parameter, the notebook uses the current workspace.

%%tsql -artifact dw1 -type Warehouse -bind df1
SELECT TOP (10) [GeographyID],
			[ZipCodeBKey],
			[County],
			[City],
			[State],
			[Country],
			[ZipCode]
FROM [dw1].[dbo].[Geography]

If both the -artifact and -type parameters are skipped, the notebook uses the default data warehouse in the current notebook.

Using T-SQL magic command to query SQL Database

You can also use the T-SQL magic command to query a Fabric SQL Database. The syntax is similar to querying a data warehouse, but the artifact parameter must be set to SQLDatabase. The -bind parameter specifies the name of the variable to bind the results of the T-SQL query to. In the following example, the result of the query is stored in a Python variable called df2.

%%tsql -artifact sqldb1 -type SQLDatabase -bind df2
SELECT TOP (10) [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvince]
      ,[CountryRegion]
      ,[PostalCode]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [SalesLT].[Address]

Using T-SQL magic command to query lakehouse SQL endpoint

You can also use the T-SQL magic command to query a Fabric SQL endpoint. The syntax is similar to querying a data warehouse, but the artifact parameter must be set to Lakehouse.

Using T-SQL magic command as line magic

Beside running T-SQL in a full code cell with %%tsql, you can also run T-SQL in a single line with %tsql as line magic. The following line command allows running quick queries without needing to create a full code cell.

df = %tsql select top(10) * from [dw1].[dbo].[Geography]

Reference Python variables in T-SQL

You can also reference Python variables in T-SQL code. To do so, use the {} symbol followed by the name of the Python variable. For example, if you have a Python variable called count, you can reference it as follows in your T-SQL code:

count = 10

df = %tsql select top({count}) * from [dw1].[dbo].[Geography]

To see the full syntax, use the %tsql? command. This command displays the help information for the T-SQL magic command, including the available parameters and their descriptions.

Note

You can run the full DML and DDL commands against the data warehouse or SQL database, but only read-only query against the lakehouse sql endpoint.

Related content

For more information about Fabric notebooks, see the following articles.

• Questions? Try asking the Fabric Community.
• Suggestions? Contribute ideas to improve Fabric.