Pre-populate a Flask SQLAlchemy database

Question

I have two models, one is Identification which contains two IDs (first_id and second_id) and the second is User. The idea is that only authorised users will be given their first_id and second_id pair of values. They go to the site and login by entering the two id's plus a username and password (which they generate there and then).

I am trying to achieve two things here:

1. Pre-populate the Identification table with many (let's say 100) first_id/second_id values that will serve as the correct value pairs for logging in.

2. Set up the User class in such a way that only if the user enters a correct first_id/second_id pair in the login form can they log in (presumable this involves checking the form data with the Identification table somehow).

Here are the model classes:

class Identification(db.Model):

id = db.Column(db.Integer, primary_key=True)
first_id= db.Column(db.Text, unique=True)
second_id= db.Column(db.Text, unique=True)

def __init__(self, first_id, second_id):
    self.first_id= first_id
    self.second_id= second_id

def __repr__(self):
    return f"ID: {self.id}, first_id: {self.first_id}, second_id: {self.second_id}"



class User(db.Model, UserMixin):

__tablename__ = 'user'

first_id= db.relationship('Identification', backref = 'identificationFID', uselist=False)
second_id = db.relationship('Identification', backref = 'identificationSID', uselist=False)
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.Text, unique=True, index=True)
password_hash = db.Column(db.Text(128))
identification_id = db.Column(db.Integer, db.ForeignKey('identification.id'), unique=True)
first_id = db.Column(db.Text, unique=True)
second_id = db.Column(db.Text, unique=True)

I would appreciate any help on this as I'm struggling and this is really above my understanding of python/Flask. Thanks all!

Answer 1

The answer above didn't work for me, because the create_tables() function since being part of the User class, requested that I pass an Instance of that class. The solution I came up with, was to call the function after db.create_all(). This seemed like a good place to put the call, because of the @app.before_first_request decorator.

init.py

@app.before_first_request
def create_tables():
    """Create Tables and populate certain ones"""
    db.create_all()
    from app.models.init_defaults import init_defaults
    init_defaults()

init_defaults.py

def init_defaults():
"""Pre-Populate Role Table"""
if Role.query.first() is None:
    roles = ['Admin', 'Master', 'Apprentice']
    for role in roles:
        user_role = Role(access_level=role)
        if role != 'Apprentice':
            user_role.set_password('Passw0rd!')
        db.session.add(user_role)
    db.session.commit()
pass

Due to the decorator the function is now only called once per instance. Another solution I could imagine working, would be to use events: https://dzone.com/articles/how-to-initialize-database-with-default-values-in

Note: This is a development solution not fit for production.

Answer 2

You can use mock data to populate these tables. create a function in this py file where you can add objects to DB using ORM and then call the function in __init__.py, which will populate data once your flask server starts.

Update:-

here is a code for your reference.

Model.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
session = Session()

class User(Base):
     \__tablename__ = 'users'
     id = Column(Integer, primary_key=True)
     name = Column(String)
     fullname = Column(String)

     #Create getter setters

    def create_tables():
        Base.metadata.create_all(engine)
        user = User()
        user.id=1
        user.name="ABC"
        user.fullname="ABCDEF"
        session.add(user)
        # similarly create more user objects with mock data and add it using session

__init__.py

from model import User
User.create_tables()

Reference