[FIXED] Using views and classes with sqlalchemy

Issue

Would need some input on how to structure my project. Currently I have almost everything stored in my app.py file (about 2000 rows). Most of my app.routes looks similar where the main functionality being sql calls. In the real app the call strings are bigger than the example code below.

I have one additional file, animalhandling.py, that I use to call charting functions from the template files.

Questions:

1. Are there any other benefits of using views other than structure and readability? What do I need to think of if switching?
2. Most of my sql calls looks very similar but with some changes to the SELECT and WHERE parts. How can I make use of classes instead of repeating more or less the same sql string on each app.route?

Example:

@app.route("/exampleAnimal")
@login_required
@custom_decorator
def animalData():
    query = text("""
            SELECT
                an.name,
                bo.date,
                bo.race,
                ca.age
            FROM
                animals an
                INNER JOIN bored bo USING(name)
                INNER JOIN cat ca USING (name, date)
            WHERE
                (ca.age > 10)
                AND (ca.race < 1)
                AND (date = (SELECT max(date) FROM animals))
            ORDER BY bo.date DESC
            """)

    animal_data = db.session.execute(query)
    return render_template("animal.html", animal_data=animal_data, count_data=g.count_data)

I looked up some general guides on views and starting to grasp the context but I'm not there fully yet. I'm very unfamiliar with classes.

Solution

First I want to HIGHLY recommend you to take a look at this awesome flask tutorial. In chapter 15 you can see how blueprints can be used for a better projectstructure and chapter 1-8 give a good overview how database handling can be done in flask using sqlalchemy. That being said I will give my best to answer your questions and show you benefits of using sqlalchemy

Using blueprints and views

Using blueprints and views make sense if there are parts of your application that can serve as a "standalone" feature. For example your use of the login_required decorators makes me believe that you provide a feature where a user can login, maybe even create an account. This feature is very seperate from the route you showed which provides animal data. So you could move your login-logic to one blueprint and your animal logic to another blueprint and then use app.register_bluprint to register both. Things you need to think about when switching are in how many parts you can split your application and if you have this clear seperation of features in your app then using a blueprint might not suit your application.

Using sqlAlchemy Models

If you use a custom database session to execute queries (which I assume given your code) then you will have to set up some things differently for which I again recommend the above tutorial. Here I will show you the benefits:

From your code given above a database schema like this could be created in its seperate file:

from app import db # Assuming you use sqlalchemy this will be how you use import the database instance of the app to any file

class Animal(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())
    boredId = db.Column(db.Integer, db.ForeignKey("bored.id")
    catId = db.Column(db.Integer, db.ForeignKey("cat.id")

class Bored(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.String())
    race =db.Column(db.String())
    db.relationship("Animal", backref="bored")

class Cat(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    age = db.Column(db.Integer)
    race =db.Column(db.String())
    db.relationship("Animal", backref="cat")

With such models your database query could look something like this:

animal = db.session.scalars(db.select(Animal).where(Animal.cat.age > 10 and Animal.cat.race < 1)

Then you will be able to retrieve the wanted column data like that:

name = animal.name
catAge = animal.cat.age
...

I am pretty sure the schema and the query is not an exact representation of your query above but I hope it gives you an idea how this could shorten your code

Furthermore using sqlalchemy allows you to automatically create databases from the application at its startup and update it in a production environment if your tables or the relationships change. This is useful for a scenario where you have an app running and users using it and maybe they entered data. Now you develop a new feature and this results in a change in your database. Tools like flask migrate help you with updating the database your users are using without deleting data / doing other undesired stuff with the existing data. To understand this benefit better again take a look at the tutorial given above.

Answered By - Lenntror