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

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

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

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();
  1. 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()