Flask - Database Setup

[s2t2]

Notes for using Flask-SQLAlchemy package for database with Flask-Migrate package for migrations.

Flask-SQLAlchemy:

• https://github.com/pallets/flask-sqlalchemy/
• https://flask-sqlalchemy.palletsprojects.com/en/2.x/
• https://flask-sqlalchemy.palletsprojects.com/en/2.x/api/#models
• https://docs.sqlalchemy.org/en/13/core/type_basics.html
• https://docs.sqlalchemy.org/en/13/orm/join_conditions.html?highlight=foreign%20key

Flask-Migrate:

• https://flask-migrate.readthedocs.io/en/latest/
• https://github.com/miguelgrinberg/Flask-Migrate

pipenv install Flask-SQLAlchemy Flask-Migrate

Model class(es):

# web_app/models.py

from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

db = SQLAlchemy()

migrate = Migrate()

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(128))
    author_id = db.Column(db.String(128))

    def __repr__(self):
        return f"<Book {self.id} {self.title}>"
    
def parse_records(database_records):
    """
    A helper method for converting a list of database record objects into a list of dictionaries, so they can be returned as JSON

    Param: database_records (a list of db.Model instances)

    Example: parse_records(User.query.all())

    Returns: a list of dictionaries, each corresponding to a record, like...
        [
            {"id": 1, "title": "Book 1"},
            {"id": 2, "title": "Book 2"},
            {"id": 3, "title": "Book 3"},
        ]
    """
    parsed_records = []
    for record in database_records:
        print(record)
        parsed_record = record.__dict__
        del parsed_record["_sa_instance_state"]
        parsed_records.append(parsed_record)
    return parsed_records

Some example usage of the models (for example in a route):

# SELECT * FROM books
book_records = Book.query.all()
print(book_records)

# INSERT INTO books ...
new_book = Book(title=request.form["title"], author_id=request.form["author_name"])
db.session.add(new_book)
db.session.commit()

Web app init config:

# web_app/__init__.py

from flask import Flask

from web_app.models import db, migrate
from web_app.routes.home_routes import home_routes
from web_app.routes.book_routes import book_routes

DATABASE_URI = "sqlite:///web_app_99.db" # using relative filepath
#DATABASE_URI = "sqlite:////Users/Username/Desktop/your-repo-name/web_app_99.db" # using absolute filepath on Mac (recommended)
#DATABASE_URI = "sqlite:///C:\\Users\\Username\\Desktop\\your-repo-name\\web_app_99.db" # using absolute filepath on Windows (recommended) h/t: https://stackoverflow.com/a/19262231/670433

def create_app():
    app = Flask(__name__)

    app.config["SQLALCHEMY_DATABASE_URI"] = DATABASE_URI
    db.init_app(app)
    migrate.init_app(app, db)

    app.register_blueprint(home_routes)
    app.register_blueprint(book_routes)

    return app

if __name__ == "__main__":
    my_app = create_app()
    my_app.run(debug=True)

Creating and Migrating the database:

FLASK_APP=web_app flask db init #> generates app/migrations dir

# FLASK_APP=web_app flask db stamp head # if there is an issue

# run both when changing the schema:
FLASK_APP=web_app flask db migrate #> creates the db (with "alembic_version" table)
FLASK_APP=web_app flask db upgrade #> creates the specified tables