[PYTHON] SNS Flask (Model) edition made with Flask

TL;DL Udemy attendance record for the following courses

Web application development course with Python + Flask! !! ~ Master Flask from 0 to create SNS ~ https://www.udemy.com/course/flaskpythonweb/

This article describes Flask's View side (Model side).

See the following article for View (Template) side https://qiita.com/kekosh/items/e4b5d1e3272a20d1c966

Model

A model defines the contents of a database table.

SQLAlchemy OR mapper library used in python. In short, the library used to operate the DB

migration Reflect Model changes in database tables.

1. 1. Creating a table

** Install **

pip install flask-sqlalchemy
pip install flask-migrate

model

#model.py
import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

base_dir = os.path.dirname(__file__)

app = Flask(__name__)

#Specify the file save destination of sqlite
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + \
    os.path.join(base_dir, 'data.sqlite')

#Set whether to send a signal when there is a change in the model
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

#Connect to DB with the contents set in app
db = SQLAlchemy(app)

#Db for model class definition.You need to inherit the Model class.
class Person(db.Model):
    #Set table name(Table names are generally pluralized in class names)
    __tablename__ = 'persons'

    #Define the column of the table to be created
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text)
    age = db.Column(db.Integer)

** Create a table from the model and add data. ** **

#crud.py
from model import db, Person

#Create a table from a model(For all imported models)
db.create_all()

*Create an instance of the data class defined in the model.
man1 = Person('Taro', 18)
man2 = Person('Jiro', 18)
man3 = Person('Saburo', 18)

#Add to the created table(add:Add add_all: Add list format at once)
db.session.add_all([man1, man2])
db.session.add(man3)

#Commit changes to the table.
db.session.commit()
print(man1, man2, man3)

** After executing the crud.py file, check that the sqlite data file is created in the Model folder **

2. 2. Table migration

What is migration? Reflect the update information of the table defined in the model file in the database and manage the information as a history. As a result, if you want to build a DB with the same content in another environment, you can successfully create a DB with the same content by updating the DB according to the history.

Note that if you create a table by executing the "db.create_all ()" method in the control file as in the previous section, it is not suitable for using migration.

from model import db, Person

#In this case, management by migration is not suitable.
db.create_all()

** Model for migration management **

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

base_dir = os.path.dirname(__file__)

app = Flask(__name__)

#Specify the file save destination of sqlite
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + \
    os.path.join(base_dir, 'migrate_data.sqlite')

#ON / OFF of the setting to trace when there is a change in the data of sqlalchemy
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

#DB instance creation(Connection)
db = SQLAlchemy(app)

#Preparing for migration (arguments are instances of FlaskApp and SQLAlchemy)
# 2021-01-10:
#Initialization is init_Also possible with the app method (Flask extension standard method)
# [Migration instance].init_app([flask app name],[db name]
#Example:
# migrate = Migrate()
# migrate.init_app(app, db)
Migrate(app, db)

# db.Allow table to be defined by inheriting Model class
class Person(db.Model):
    #Set table name(Table names are generally pluralized in class names)
    __tablename__ = 'persons'

    #Define the column of the table to be created
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text)
    gender = db.Column(db.Text)
    age = db.Column(db.Integer)

    def __init__(self, name, age):
        self.name = name
        self.age = age

    def __str__(self):
        return "id={},name={},age={}".format(self.id, self.name, self.age)

** Executing migration **

1. Set environment variables command: export FLASK_APP = [file creating Flask instance]

** The "file creating the Flask instance" is the file where "app = Flask ()" is running. (Where it is being executed, not where it is written) **

(flaskenv) (base) root@e8cf64ce12e9:/home/venv/flaskenv/Model# export FLASK_APP=migrate_model.py

2. Initialize the DB. This will create a Migratin folder to store the migration configuration information. command : flask db init

(flaskenv) (base) root@e8cf64ce12e9:/home/venv/flaskenv/Model# flask db init  Creating directory /home/venv/flaskenv/Model/migrations ...  done
  Creating directory /home/venv/flaskenv/Model/migrations/versions ...  done
  Generating /home/venv/flaskenv/Model/migrations/README ...  done
  Generating /home/venv/flaskenv/Model/migrations/alembic.ini ...  done
  Generating /home/venv/flaskenv/Model/migrations/env.py ...  done
  Generating /home/venv/flaskenv/Model/migrations/script.py.mako ...  done
  Please edit configuration/connection/logging settings in
  '/home/venv/flaskenv/Model/migrations/alembic.ini' before proceeding.

3. Create a migration file to reflect the model changes in the DB. command: flask db migrate -m'comment'

(flaskenv) (base) root@e8cf64ce12e9:/home/venv/flaskenv/Model# flask db migrate -m 'add Person'
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'persons'
  Generating /home/venv/flaskenv/Model/migrations/versions/aa0223688c58_add_person.py
  ...  done

4. Reflect the contents of the migration file in the DB. command : flask db upgrade

(flaskenv) (base) root@e8cf64ce12e9:/home/venv/flaskenv/Model# flask db upgrade
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade aa0223688c58 -> c881e626270e, persons db add new column "gender"

By executing the migration, the changes to the table on the model file will be reflected in the DB. In addition, the "alembic_version" table is automatically added to the DB along with the migration. This table is added to manage the migration version, and you can check the version information of the applied migration by checking the contents from the right-click Show_Table menu.

3. 3. Constraint presentation, indexing

• CheckConstraint module needs to be imported from sqlalchemy.

** If db.config ['SQLALCHEMY_ECHO'] = True ** The contents of the executed SQL are displayed on the console as follows.

4. Basic SQLAlchemy operation

--When multiple records are acquired, it is necessary to acquire them one by one with a for statement in order to refer to individual records.

Other references https://docs.sqlalchemy.org/en/14/orm/query.html?highlight=query%20limit#sqlalchemy.orm.Query.limit

5. Foreign key

Link between columns of multiple tables. When the data of the table for which the external reference key is set is SELECTed, the data associated with the referenced table will be acquired together with the result.

○ lazy option Set the processing method when associating the table.

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

base_dir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = \
    'sqlite:///' + os.path.join(base_dir, 'data.sqlite')

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = True

db = SQLAlchemy(app)


class Employee(db.Model):
    __tablename__ = 'employees'

    """Column definition"""
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text)
    #Foreign key declaration
    # One to Many
    projects = db.relationship('Project', backref='employees')
    # One to One
    company = db.relationship('Company', backref='emoployees', uselist=False)

    def __init__(self, name):
        self.name = name

    def __str__(self):
        if self.company:
            return f'Employee name {self.name} company is {self.company.name}'
        else:
            return f'Employee name = {self.name}, has no company'

    def show_projects(self):
        for project in self.projects:
            print(project)


class Project(db.Model):
    __tablename__ = 'project'

    """Column definition"""
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text)
    employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'))

    def __init__(self, name, employee_id):
        self.name = name
        self.employee_id = employee_id


class Company(db.Model):
    __tabelname__ = 'companies'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text)
    employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'))

    def __init__(self, name, employee_id):
        self.name = name
        self.employee_id = employee_id


#Create table
db.create_all()

6. Transaction

--A transaction is a collection of processes for the DB as one unit. --Since one transaction is until commit, if an error occurs in the transaction, all the processing in the transaction is rolled back. --Since a series of processing results are either completed or not all due to an error, the atomicity of the processing is maintained by using transactions.

#syntax
with db.sessino.begin(subtransactions=True):
DB processing
db.session.commit()

#Data update process
#Get target record
record = Model.query.get(1)
#Assign to the item you want to update
record.name = 'update'
# db.Add updated record object to session
db.session.add(record)
#Commit process
db.session.commit()