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

Question

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.

Answer 1

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()

Answer 2

You can use func

Syntax:

  from sqlalchemy import func

  func.function_name(column)

Example:

from sqlalchemy import func

result = db.session.query(func.lower(Student.name)).all()

Answer 3

I found a solution to execute the function with raw SQL:

1. Create a connection
2. Call the function as you normally would in the database GUI. E.g. for the function add_apples():

select add_apples();

3. Execute this statement, which should be a string.

Example code:

transaction = connection.begin()
    sql = list() # Allows multiple queries
    sql.append('select add_apples();')

    print('Printing the queries.')

    for i in sql:
        print(i)

    # Now, we iterate through the sql statements executing them one after another. If there is an exception on one of them, we stop the execution 
    # of the program.
    for i in sql:
        # We execute the corresponding command      
        try:
            r = connection.execute(i)
            print('Executed ----- %r' % i)
        except Exception as e:
            print('EXCEPTION!: {}'.format(e))
            transaction.rollback()
            exit(-1)

    transaction.commit()