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:
- Create a connection
- Call the function as you normally would in the database GUI. E.g. for the function add_apples():
select add_apples();
- 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()