1

I have a T-SQL database function that returns a table output. I can query it in raw SQL simply:

SELECT * FROM dbo.myTableFunction(param1, param2)

I've tried following the solution here:

How do I call a database function using SQLAlchemy in Flask?

My Python code looks as follows:

db.session.query(func.dbo.myTableFunction(param1, param2)).all()

However I'm getting an error:

[SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.myTableFunction", or the name is ambiguous.
(4121) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)') [SQL: SELECT dbo.myTableFunction(?, ?) AS myTableFunction_1]

I can see that the SQL statement is clearly incorrect but I don't know how to correct it. I'm expecting a SQL statement similar to the raw SQL:

SELECT * FROM dbo.myTableFunction(param1, param2)

But I'm unsure of how to achieve this using Flask-SQLAlchemy.

LattePrincess
  • 53
  • 5

1 Answers1

1

I solved it. To call a function with a table output, use:

db.session.query(
    select([column("col_1"), column("col_2")])
    .select_from(func.dbo.myTableFunction(param1, param2).alias()).subquery()
)

where "col_1" and "col_2" are the names of the columns being returned from the function

LattePrincess
  • 53
  • 5