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 ** スクリーンショット 2020-11-24 22.39.30.png

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
  1. 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.
  1. 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
  1. 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. スクリーンショット 2020-12-13 0.37.53.png

3. 3. Constraint presentation, indexing

option Constraint code
primary_key Primary key constraint db.Column(db.Integer, primary_key = True)
unique Unique constraint db.Column(db.Integer,unique = True)
nullable NOT NULL constraint db.Column(db.Integer,nullable = False)
index Index db.Column(db.Text,Index = True)
db.Index It is possible to define an index outside the class. Define the index name in the first argument and the index function in the second argument db.Index('some_index',func_lower(Person.name))
server_default Set the default value of the column db.Column(db.Text, server_default='xxxx')
CheckConstraint ※ Check conditions can be set freely __table_args__=(CheckConstraint('update_at > create_at'),)

** If db.config ['SQLALCHEMY_ECHO'] = True ** スクリーンショット 2020-12-13 21.51.13.png The contents of the executed SQL are displayed on the console as follows. スクリーンショット 2020-12-13 21.51.23.png

4. Basic SQLAlchemy operation

code Overview
db.session.add(object) Insert record
db.session.add_all([List]) Add multiple records at once
db.session.delete()  Table.query.filter_by(conditions).delete() Delete record
Table.query.get(primary_key) Extract with primary key
Table.query.all() List data and extract all
Table.query.first() Get only the first element of data
Table.query.filter_by(name='A') Narrow down
Table.query.filter(Table.name.startswith('A')) Prefix search
Table.query.filter(Table.name.endswith('z')) End match search
Table.query.limit(1) Extract by specifying the number of cases
Table.query.update({'column': 'value'}) update

--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.

Overview template sample
Reference setting db.relationship(Reference model name, backref='Table name at the time of external reference' projects = db.relationship('Project', backref='employees', [layzy=xxxx])
Referenced settings db.Column(Data type, db.ForeignKey(Key item) employee_id = db.Column(db.Integer, db.ForeignKey('employees.id'))

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

option Overview
select Default settings. Execute the SELECT statement each time.
joined Associate a table with a JOIN clause
subquery Associate a table with a subquery
dynamic Create an object for query execution in the linked 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()

Recommended Posts

SNS Flask (Model) edition made with Flask
SNS Flask (Ajax) made with Flask
SNS Python basics made with Flask
SNS made with Flask Flask (Blueprint, bcrypt)
SNS made with Flask Flask (login process by flask_login)
Seq2Seq (2) ~ Attention Model edition ~ with chainer
REST API of model made with Python with Watson Machine Learning (CP4D edition)
I made a Mattermost bot with Python (+ Flask)
Container-like # 1 made with C
Full-scale server made with Nginx + uWSGI + Flask + Ubuntu (installation)
Model fitting with lmfit
Container-like # 2 made with C
IP restrictions with Flask
Regression with linear model
USB boot with Raspberry Pi 4 Model B (3) LVM edition
Full-scale server made with Nginx + uWSGI + Flask + Ubuntu (implementation)
Hello world with flask
Programming with Python Flask
MVC --Model edition to learn from 0 with prejudice only
Twitter posting client made with Flask with simple login function
How to deploy a web app made with Flask to Heroku
I made LINE-bot with Python + Flask + ngrok + LINE Messaging API
I made a simple book application with python + Flask ~ Introduction ~
(Failure) Deploy a web app made with Flask on heroku
I tried to implement SSD with PyTorch now (model edition)
Until API made with Flask + MySQL is converted to Docker
Deploy Flask with ZEIT Now
Pomodoro timer made with Errbot
Touch Flask + run with Heroku
Hello World with Flask + Hamlish
Unit test flask with pytest
API with Flask + uWSGI + Nginx
I made blackjack with python!
Web application development with Flask
Seq2Seq (3) ~ CopyNet Edition ~ with chainer
View flask coverage with pytest-cov
Calibrate the model with PyCaret
Web application with Python + Flask ② ③
I made COVID19_simulator with JupyterLab
I made Word2Vec with Pytorch
File upload with Flask + jQuery
I made blackjack with Python.
Othello made with python (GUI-like)
I made wordcloud with Python.
Web application with Python + Flask ④
I made a Nyanko tweet form with Python, Flask and Heroku