如何在Flask中使用SQLAlchemy调用数据库函数? [英] How do I call a database function using SQLAlchemy in Flask?
问题描述
我想调用在PostgreSQL数据库中创建的函数.我已经看过官方的SQLAlchemy文档以及此处有关SO的几个问题，但似乎没有人解释如何在SQLAlchemy中设置功能.</p>
我确实找到了这个问题，但是不确定如何如答案所示编译函数.该代码去哪儿了?尝试将其同时放入视图脚本和模型脚本时会出现错误.
编辑1(2016年8月11日)
根据社区的要求和要求，以下是我遗漏的所有详细信息:
我有一个表，称为书籍"，其各列安排了有关一般书籍的信息(书名，作者，出版日期...).
然后，我得到了许多同类型的表，这些表的列包含有关每本书中所有章节的信息(章节名称，长度，简短摘要...).每本书都有自己的桌子是绝对必要的.我在所有章节中放了一张大桌子，发现它不适合我的需要，不必太笨拙.
我要查询的函数查询书的表以获取单个书的名称，并将该书的名称转换为regclass.然后，它向regclass对象查询其所有数据，将所有行作为表返回，就像各个书本表一样，然后退出.这是原始代码:
创建或替换功能public.get_book(bookName字符不同)返回表(/*单个书本表的列在这里*/)语言plpgsqlAS $功能$宣布_tbl regclass;开始用于_tbl选择名称:: regclass从书中其中name = bookName环形返回查询执行'从'|| _tbl中选择*；结束循环；结尾;$功能$ 此功能已经在命令行和pgAdmin中进行了多次测试.它按预期工作.
我的意图是在我的Flask应用中查看一个路径为 @ app.route('/book/< string:bookName>')的视图，并在渲染模板之前调用上述函数.确切的视图如下:
@ app.route('/book/< string:bookName>')def book(bookName):chapterList =/*在此处调用函数*/返回render_template('book.html'，book = bookName，list = chapterList) 这是我的问题:如何以SQLAlchemy知道并可以调用数据库中具有的函数的方式设置我的应用程序?我也乐于接受其他建议以达到相同的结果.
P.S.我只是为了保持我的问题尽可能抽象而省略了这些信息，而不知道论坛的规则规定了一个非常具体的问题的要求.请原谅我缺乏知识.
如果要在不使用原始sql的情况下进行操作，则可以使用sqlalchemy中的 func :
数据= db.session.query(func.your_schema.your_function_name()).all() I want to call a function that I created in my PostgreSQL database. I've looked at the official SQLAlchemy documentation as well as several questions here on SO, but nobody seems to explain how to set up the function in SQLAlchemy.
I did find this question, but am unsure how to compile the function as the answer suggests. Where does that code go? I get errors when I try to put this in both my view and model scripts.
Edit 1 (8/11/2016)
As per the community's requests and requirements, here are all the details I left out:
I have a table called books whose columns are arranged with information regarding the general book (title, author(s), publication date...).
I then have many tables all of the same kind whose columns contain information regarding all the chapters in each book (chapter name, length, short summary...). It is absolutely necessary for each book to have its own table. I have played around with one large table of all the chapters, and found it ill suited to my needs, not too mention extremely unwieldy.
My function that I'm asking about queries the table of books for an individual book's name, and casts the book's name to a regclass. It then queries the regclass object for all its data, returns all the rows as a table like the individual book tables, and exits. Here's the raw code:
CREATE OR REPLACE FUNCTION public.get_book(bookName character varying)
RETURNS TABLE(/*columns of individual book table go here*/)
LANGUAGE plpgsql
AS $function$
declare
_tbl regclass;
begin
for _tbl in
select name::regclass
from books
where name=bookName
loop
return query execute '
select * from ' ||_tbl;
end loop;
end;
$function$
This function has been tested several times in both the command line and pgAdmin. It works as expected.
My intention is to have a view in my Flask app whose route is @app.route('/book/<string:bookName>') and calls the above function before rendering the template. The exact view is as follows:
@app.route('/book/<string:bookName>')
def book(bookName):
chapterList = /*call function here*/
return render_template('book.html', book=bookName, list=chapterList)
This is my question: how do I set up my app in such a way that SQLAlchemy knows about and can call the function I have in my database? I am open to other suggestions of achieving the same result as well.
P.S. I only omitted this information with the intention of keeping my question as abstract as possible, not knowing that the rules of the forum dictate a requirement for a very specific question. Please forgive me my lack of knowledge.
If you want to do it without raw sql, you can use func from sqlalchemy:
from sqlalchemy import func
data = db.session.query(func.your_schema.your_function_name()).all()
这篇关于如何在Flask中使用SQLAlchemy调用数据库函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
