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
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.
** 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 **
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 **
** 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
(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.
(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
(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.
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 ** The contents of the executed SQL are displayed on the console as follows.
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
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()
--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