英文:
How to use SQL function that returns a table in Flask-SQLAlchemy?
问题
我有一个返回表格输出的T-SQL数据库函数。我可以使用原始SQL查询它：
SELECT * FROM dbo.myTableFunction(param1, param2)
我尝试按照这里的解决方案：
https://stackoverflow.com/questions/38878846/how-do-i-call-a-database-function-using-sqlalchemy-in-flask
我的Python代码如下：
db.session.query(func.dbo.myTableFunction(param1, param2)).all()
然而，我遇到了一个错误：
> [SQL Server]无法找到列"dbo"或用户定义的函数或聚合"dbo.myTableFunction"，或者名称不明确。
> (4121) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]无法准备语句。(8180)
[SQL: SELECT dbo.myTableFunction(?, ?) AS myTableFunction_1]
我可以看到SQL语句明显是不正确的，但我不知道如何纠正它。我期望得到类似原始SQL的SQL语句：
SELECT * FROM dbo.myTableFunction(param1, param2)
但我不确定如何在Flask-SQLAlchemy中实现这一点。
英文:
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:
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.
答案1
得分: 1
我解决了。调用带有表输出的函数，请使用：
db.session.query(
select([column("col_1"), column("col_2")])
.select_from(func.dbo.myTableFunction(param1, param2).alias()).subquery()
)
其中"col_1"和"col_2"是从函数返回的列的名称
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论