Common file structure with flask and Flask-SQLAlchemy

Question

I'm building a flask app that retrieves data from a website, store the data on Flask-SQLAlchemy db and display the data. I wrote a script to do all of them but I've to run the files individually to achieve that.

my files structure:

-myapp
  database.py
  retrieve.py
  main.py
  templates
      --index.html
  static
      -css
        --style.css
      -js
        --script.js

database.py

from main import db, Session, User
from retrieve import git_api, DataTransform
session = Session()


def create_data():
     for row in df.iterrows():
          user = User(Issue_id=row[1]['Issue_id'],
                 Title= row[1]['Title'],
                 Description=row[1]['Description'],
                 State= row[1]['State'],
                 Assignee_name=row[1]['Assignee_name'],
                 Author_name = row[1]['Author_name'],
                 Created_at=row[1]['Created_at'][:10],
                 Updated_at=row[1]['Updated_at'][:10],
                 Closed_at= row[1]['Closed_at'],
                 Due_date=row[1]['Due_date'],
                 Issue_url= row[1]['Issue_url'])

         db.session.add(user)
    db.session.commit()
  
if __name__ == '__main__':
    git_api()
    df = DataTransform()
    create_data()

retrieve.py

import pandas as pd
import ssl
import requests

def git_api():
   //send a request and retrieve data
def def DataTransform():
  //transform data
  return df

main.py

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

app = Flask(__name__, template_folder='templates/layouts', static_folder='static')
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db.sqlite'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)
engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'])
Session = scoped_session(sessionmaker(bind=engine, autocommit=False,autoflush=False))
db.init_app(app)



class User(db.Model):

    Issue_id = db.Column(db.String, primary_key=True)
    Title = db.Column(db.String, index=True)
    Description = db.Column(db.String, index=True)
    State = db.Column(db.String(256))
    Assignee_name = db.Column(db.String(120))
    Author_name = db.Column(db.String(120))
    Created_at = db.Column(db.String(256))
    Updated_at = db.Column(db.String(20))
    Closed_at = db.Column(db.String(120))
    Due_date = db.Column(db.String(120))
    Issue_url = db.Column(db.String(120))


    def to_dict(self):
        return {
            'Issue_id': self.Issue_id,
            'Title': self.Title,
            'Description': self.Description,
            'State': self.State,
            'Assignee_name': self.Assignee_name,
            'Author_name': self.Author_name,
            'Created_at': self.Created_at,
            'Updated_at': self.Updated_at,
            'Closed_at': self.Closed_at,
            'Due_date': self.Due_date,
            'Issue_url': self.Issue_url
        }

db.create_all()


@app.route('/')
def index():
    return render_template('server_table.html')


@app.route('/api/data')
def data():
    query = User.query

    # search filter
    search = request.args.get('search[value]')
    if search:
        query = query.filter(db.or_(
            User.Title.like(f'%{search}%'),
            User.State.like(f'%{search}%'),
            User.Description.like(f'%{search}%')
        ))
    total_filtered = query.count()

    # sorting
    order = []
    i = 0
    while True:
        col_index = request.args.get(f'order[{i}][column]')
        if col_index is None:
            break
        col_name = request.args.get(f'columns[{col_index}][data]')
        if col_name not in ['Issue_id', 'Title', 'State', 'Assignee', 'Author_name']:
            col_name = 'Issue_id'
        descending = request.args.get(f'order[{i}][dir]') == 'desc'
        col = getattr(User, col_name)
        if descending:
            col = col.desc()
        order.append(col)
        i += 1
    if order:
        query = query.order_by(*order)

    # pagination
    start = request.args.get('start', type=int)
    length = request.args.get('length', type=int)
    query = query.offset(start).limit(length)

    # response
    return {
        'data': [user.to_dict() for user in query],
        'recordsFiltered': total_filtered,
        'recordsTotal': User.query.count()
    }



if __name__ == '__main__':
    app.run()

What I'm thinking to do is to structure the files and run the database.py every 5 minutes and update the db while the main.py is running but I'm new to python dev and I don't know how do it. Is there a simple or common way to achieve this? any pointers and suggestions would be great. thank you in advance for anyone who can help me!

Answer 1

My directory structure approach to avoid circular imports in Flask though you are free to create your own folder structure in flask in your own convenient way. This is the approach I use when the application grows and handling a lot of circular imports

.
├── app
│   ├── Auth
│   │   ├── forms.py
│   │   ├── helpers.py
│   │   ├── __init__.py
│   │   └── views.py
│   ├── config
│   │   ├── config.py
│   │   └── __init__.py
│   ├── db
│   │   ├── database.py
│   │   ├── __init__.py
│   │   └── models.py
│   ├── home
│   │   ├── __init__.py
│   │   └── views.py
│   ├── __init__.py
│   ├── login
│   │   ├── __init__.py
│   │   └── login.py
│   ├── static
│   │   ├── css
│   │   │   ├── style.css
│   │   │   └── style.css.map
│   │   ├── favicon_io
│   │   │   ├── android-chrome-192x192.png
│   │   │   ├── android-chrome-512x512.png
│   │   │   ├── apple-touch-icon.png
│   │   │   ├── favicon-16x16.png
│   │   │   ├── favicon-32x32.png
│   │   │   ├── favicon.ico
│   │   │   └── site.webmanifest
│   │   ├── images
│   │   │   └── avatar.png
│   │   └── scss
│   │       ├── forms.scss
│   │       ├── home.scss
│   │       ├── main.scss
│   │       ├── nav.scss
│   │       ├── profile.scss
│   │       └── reset.scss
│   └── templates
│       ├── auth
│       │   ├── blogs.html
│       │   ├── login.html
│       │   ├── profile.html
│       │   └── register.html
│       ├── base.html
│       ├── include
│       │   └── nav.html
│       └── index.html
├── Pipfile
├── Pipfile.lock
├── Procfile
├── README.md
├── requirements.txt
├── test
│   └── __init__.py
└── wsgi.py

When you are using plain flask_sqlalchemy you have to db migrations on your own manually. So the best solution you can have is to integrate flask-migrate in your project which you just use to migrate the current changes you have made to your models

Here is how you add it first you run pip install flask-migrate or pipenv install flask-migrate then import the Migrate class from the package and pass your flask app instance to it. This creates a singleton instance for the whole application.

app/__init__.py

"""App factrory file"""
from flask import Flask
from flask_migrate import Migrate
from app.config.config import APP_CONFIG
from app.db.database import db
from app.login.login import login_manager
from app.home.views import home as homeViews
from app.Auth.views import auth as authViews


def create_app():
    """App factory"""
    app = Flask(__name__)
    app.config.from_object(APP_CONFIG["development"])
    db.init_app(app)
    Migrate(app,db)
    login_manager.init_app(app)

    # Register blueprint apps
    app.register_blueprint(homeViews)
    app.register_blueprint(authViews)

    return app

app/db/database.py

"""database.py"""
from flask_sqlalchemy import SQLAlchemy


db = SQLAlchemy()

app/db/models.py

```
class User(db.Model, UserMixin):
    id = db.Column(db.Integer(), primary_key=True)
    ...


@login_manager.user_loader
def load_user(id):
    return User.query.get(int(id))

Afterwards you only need the cli to carry out the migrations

The manual way

(env) python
(env)>>> from app.db.database import db
(env)>>> from app import create_app
(env)>>> app=create_app()
(env)>>> app.app_context().push()
(env)>>> db.create_all()
(env)>>> exit()

The automated way

(env)>>> flask db init # When initializing the database to create initial migrations
(env)>>> flask db migrate -m "Initial tables creation" # On modified changes on the models with a commit message you can use for database rollback
(env)>>> flask db upgrade # Commit changes made to the models
(env)>>> flask run # Running the application

For subsequent changes you only need to run the

$ flask db migrate -m <your message>
$ flask_db upgrade