How to use SQL function that returns a table in Flask-SQLAlchemy?

Question

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 · Accepted Answer · 2023-03-07 21:00:04Z

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

answered Mar 7, 2023 at 21:00

LattePrincess

955 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use SQL function that returns a table in Flask-SQLAlchemy?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related