Forum Discussion
Solved
user data fuctions lakehouse
in Fabric i have a user data function that read a lakehouse table, see code.
how can i convert the result of the cursor to panda dataframe ?
i can't find these information in the documentation of Microsoft UDF.
# Connect to the Lakehouse SQL Endpoint
connection = myLakehouse.connectToSql()
sql = f"SELECT * FROM [LH_Silver_Int].[dbo].[person4]"
# Use connection to execute a query
cursor = connection.cursor()
cursor.execute(sql)
Hi bruinsmm,
You should be able to do this:
import pandas as pd # Connect to the Lakehouse SQL endpoint connection = myLakehouse.connectToSql() sql = "SELECT * FROM [LH_Silver_Int].[dbo].[person4]" df = pd.read_sql(sql, connection) # or pd.read_sql_query(sql, connection)Why this works in Fabric UDFs
- FabricLakehouseClient.connectToSql() returns a SQL connection object, suitable for DB-API usage and compatible with pandas read_sql. See the class docs for FabricLakehouseClient and its connectToSql() method: FabricLakehouseClient.
- Fabric UDFs support returning a pandas DataFrame directly from a function, so you can even make the DataFrame your function’s return value if you want. See the UDF Python programming model for supported outputs and connection decorators: UDF Python model and Connect to data sources.
- pandas.read_sql is the simplest, most robust path from SQL to DataFrame: pandas.read_sql.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
2 Replies
Hi bruinsmm,
You should be able to do this:
import pandas as pd # Connect to the Lakehouse SQL endpoint connection = myLakehouse.connectToSql() sql = "SELECT * FROM [LH_Silver_Int].[dbo].[person4]" df = pd.read_sql(sql, connection) # or pd.read_sql_query(sql, connection)Why this works in Fabric UDFs
- FabricLakehouseClient.connectToSql() returns a SQL connection object, suitable for DB-API usage and compatible with pandas read_sql. See the class docs for FabricLakehouseClient and its connectToSql() method: FabricLakehouseClient.
- Fabric UDFs support returning a pandas DataFrame directly from a function, so you can even make the DataFrame your function’s return value if you want. See the UDF Python programming model for supported outputs and connection decorators: UDF Python model and Connect to data sources.
- pandas.read_sql is the simplest, most robust path from SQL to DataFrame: pandas.read_sql.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
