Forum Discussion
Using %%tsql to return Headers with no rows in dataframe using a Python notebook
Hi,
I'm using the %%tsql magic command described here Run T-SQL code in Fabric Python notebooks - Microsoft Fabric | Microsoft Learn. Within a python notebook I am wanting to be able to EXEC stored procedure and also query tables and views, saving query output to a dataframe, hence, why I am using this method. I haven't found any other way to be able to execute SPs in a notebook another way, if there is please let me know.
This method works fine and how I need it to act when my views or tables have records in them, however, when records are zero, the dataframe is None. What I need is it to return a df with no rows but include headings.
From the dataframe I then go on to export df to a csv or excel. Even if no results are returned I still need a file produced.
Any help or guidance would be much appreciated.
Thanks.
5 Replies
Well, it's called "dataframe" for a reason. What you are after is more of a "metadataframe". What happens when you SELECT TOP 0 ?
Haha, thanks for that great insight 😄
With spark dataframes you are able to use df.schema or df.column to obtain the dataframe metadata from even empty dataframes, which could then be used to define the headings for the empty df going into a csv. Dataframes created using %%sql don't seem to have this, and with this being a realativlty new feature to use in notebooks, I just wanted to check with the Fabric community if I wan't missing anything. Or maybe with many Fabric things currently, if there is a work around or another option 🤔
Hi caseybks ,
Thanks for raising this Microsoft Fabric Community.
I tested the scenario in a Fabric notebook and was able to reproduce the same behavior. When the query returns zero rows, %%tsql does not return a DataFrame object and the bound variable becomes None.
As suggested by lbendlin running a query like SELECT TOP 0 * FROM <table> helps capture the expected schema before executing the main logic. You can then check if the result is None and create an empty DataFrame with the same columns before exporting, so the process continues even when no data is returned.
There seems to be currently no built-in option in %%tsql to return an empty DataFrame with headers when the result set is empty. If this is important for your workflow, submitting feedback on the Fabric Ideas forum would help the team understand the requirement.
Hope this helps. Please reach out for further assistance.
Thank you.
Hi caseybks ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.
Hi caseybks ,
Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.Thank you.
