Flask(2) Flask_SQLAlchemy を使う

Flask_SQLAlechemyを使って、SQLiteでDBを構築する。この投稿は、前の投稿に続くプロジェクトです。

この説明は、少し細かいので、

「(最も参照中)　FLASK SQLAlchemyを使ってみる 」

を読んでいただき、基本的な操作ができるようになってから、参考にされるのをお勧めします。

---

1. ファイルとディレクトリの構成

---/microblog
     |---①microblog.py
     |---② config.py
     |--- /app
     |   |---③  __init__.py
     |   |---④  routes.py
     |   |---⑤  forms.py
     |   |---⑥  models.py
     |   |--- /templates
     |        |---⑦ login.html
     |--- /migrations

2. 各ファイルの内容

① microblog.py
from app import app, db
from app.models import User, Post

@app.shell_context_processor
def make_shell_context():
    return {'db': db, 'User': User, 'Post': Post}


② config.py
import os
basedir = os.path.abspath(os.path.dirname(__file__))

class Config(object):
    SECRET_KEY = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
    SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
        'sqlite:///' + os.path.join(basedir, 'app.db')
    SQLALCHEMY_TRACK_MODIFICATIONS = False

③  __init__.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from config import Config

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)
migrate = Migrate(app, db)

from app import routes, models

④  routes.py
from flask import render_template, flash, redirect, url_for
from app import app
from app.forms import LoginForm

@app.route('/')
@app.route('/index')
def index():
    user = {'username': 'Miguel'}
    posts = [
        {
            'author': {'username': 'John'},
            'body': 'Beautiful day in Portland!'
        },
        {
            'author': {'username': 'Susan'},
            'body': 'The Avengers movie was so cool!'
        }
    ]
    return render_template('index.html', title='Home', user=user, posts=posts)


@app.route('/login', methods=['GET', 'POST'])
def login():
    form = LoginForm()
    if form.validate_on_submit():
        flash('Login requested for user {}, remember_me={}'.format(
            form.username.data, form.remember_me.data))
        return redirect(url_for('index'))
    return render_template('login.html',  title='Sign In', form=form)

⑤  forms.py
from flask_wtf import FlaskForm
from wtforms import StringField, PasswordField, BooleanField, SubmitField
from wtforms.validators import DataRequired


class LoginForm(FlaskForm):
    username = StringField('Username', validators=[DataRequired()])
    password = PasswordField('Password', validators=[DataRequired()])
    remember_me = BooleanField('Remember Me')
    submit = SubmitField('Sign In')

⑥  models.py
from datetime import datetime
from app import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    posts = db.relationship('Post', backref='author', lazy='dynamic')

    def __repr__(self):
        return '<User {}>'.format(self.username)


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    def __repr__(self):
        return '<Post {}>'.format(self.body)

⑦ login.html
/migrations

---

**このPostでは、SQLAlchemy、Flask-Migrate (Alembic)を使って、DBを構築していく。

利用するDB自体は、SQLite
$ pip install flask-sqlalchemy
$ pip install flask-migrate

Step.1 Config.pyの修正


（接続するデータベースの指定）

*The Flask-SQLAlchemy extension takes the location of the application's database from the SQLALCHEMY_DATABASE_URI configuration variable.
*In this case I'm taking the database URL from the DATABASE_URL environment variable,
* I'm configuring a database named app.db located in the main directory of the application, which is stored in the basedir variable.
* I'm configuring a database named app.db located in the main directory of the application, which is stored in the basedir variable.

◇dbの存在するパスを直接記述する。



◇dbのパスを、Import OS で、os.pathからひっぱてきて指定。

basedir = os.path.abspath(os.path.dirname(__file__))
**実行中のファイルのファイル名、ディレクトリ名を取得するには標準ライブラリのosモジュールの関数os.basename(), os.dirname()を使う。os.path.dirname()は、フォルダ名を取得できる。__file__を引数にとると、実行したスクリプトファイルのカレントディレクトリからの相対パスが取得できている。

'sqlite:///' + os.path.join(basedir, 'app.db')
•ファイル名とフォルダ名を結合してパス文字列を作成: os.path.join() ◦同じフォルダの別のファイルのパス文字列を作成

SQLALCHEMY_TRACK_MODIFICATIONSは、DBが変更されそうな時に、Noticeを出す機構。 DefaultでTrueだが、不要なのでFalseに指定。


Step.2 __init__.pyの修正

Databaseエンジンの記述。

from flask import Flask
from config import Config
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)
migrate = Migrate(app, db)

from app import routes, models

* db = SQLAlchemy(app)
　Databaseをオブジェクトとして定義。
    この設定で全てのSQLAlchemyのfunctionにアクセス可能に。

* migrate = Migrate(app, db)
　Migrateエンジンをオブジェクトとして定義。
　（Flaskのextensionをinitializeする時の標準的な書き方）

*modelsモジュールもimport

Step.3 models.pyを作成

データベースは、複数のclassの集まりで構成される。SQLAlchemyが、これらclassで定義されたオブジェクトを、データベースのテーブルのrawに変換してくれる。 今回のプロジェクトでは、
models.pyを新規に作って、classを定義していく。

⑥  models.py

from datetime import datetime
from app import db

class User(db.Model):  <- User Tableを定義
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    posts = db.relationship('Post', backref='author', lazy='dynamic')

    def __repr__(self):
        return '<User {}>'.format(self.username)

class Post(db.Model): <- 投稿Tableを定義
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    timestamp = db.Column(db.DateTime, index=True,
                            default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    def __repr__(self):
        return '<Post {}>'.format(self.body)

* (db.Model): Flask-SQLAlchemyで定義されたベースとなるclass
* primary_key=Trueは、DBのTableにつくid


【２つのデータベースを関連付ける２つの記述方法】

①User DBで記述している方法（db.relationship）

posts = db.relationship('Post', backref='author', lazy='dynamic')
 

この場合のpostsは、実際の値をもつデータベース・フィールドではなく、2つのDBの関連を表すもの。
 

通常、one-to-manyの関係の中で、one側に記述される。backrefは、many側の参照するfield名を指定する。
 

ユーザ情報がu に保持されている場合、u.postsというdatabase queryを実行すると、ユーザによって書かれた全てのpostsを取得することができる。

②Post  DBで記述している方法(db.ForeignKey)

             user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

* db.ForeignKeyを宣言することで、外部のデータベース（userテーブルのidを参照）から値を参照している。

(参考）コラム名（）内にフィールド名を記述することも可。

            followers = db.Table('followers',

    db.Column('follower_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('followed_id', db.Integer, db.ForeignKey('user.id'))
    ) 

* password_hashは、password　自身を直接保存しないため。
* __repr__ method tells Python how to print objects of this class, which is going to be useful for debugging.

Step.4 Creating The Migration Repository


Alembic maintains a migration repository, which is a directory in which it stores its migration scripts. Each time a change is made to the database schema, a migration script is added to the repository with the details of the

So let's create the migration repository for microblog by running flask db init:

(venv) set FLASK_APP=microblog.py
(venv) $ flask db init

実行すると、Project folderの配下にmigrationsという名のdirectory(migration repository)が作られ、その中にファイルが作られていく。

(venv) C:\Users\Username\Desktop\microblog>flask db init
Creating directory C:\Users\Username\Desktop\microblog\migrations ... done
Creating directory C:\Users\Username\Desktop\microblog\migrations\versions ... do
ne



*Command実行時に、Automaticなmigration (差分を記録したscriptが書かれる)
(venv) $ flask db migrate -m "users table"

実行すると、先程作られた　\migrations\versions に
1face21ca0c6_users_table.py というファイルが作成された。


You will find that it has two functions called upgrade() and downgrade(). The upgrade() function applies the migration, and the downgrade() function removes it.

(venv) $ flask db upgrade
初めて実行すると、app.db　というデータベースが、プロジェクト・ディレクトリの直下に作られる。
（MySQL and PostgreSQLの時は、先にdbを作っておかなくてはダメ。）

*user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
user_id は、userテーブルのidを参照するということ。
backrefは、one to manyのレコードの関係づけに使われる。

ここまで、できたら確認です。

Once in the Python prompt, let's import the database instance and the models:

>>> from app import db
>>> from app.models import User, Post

Start by creating a new user:

>>> u = User(username='john', email='john@example.com')
>>> db.session.add(u)
>>> db.session.commit()

Changes to a database are done in the context of a session, which can be accessed as db.session.
(普通にセッションをアッドして、コミットすればよい。）


Multiple changes can be accumulated in a session and once all the changes have been registered you can issue a single db.session.commit(), which writes all the changes atomically.

If at any time while working on a session there is an error, a call to db.session.rollback() will abort the session and remove any changes stored in it. The important thing to remember is that changes are only written to the database when db.session.commit() is called. Sessions guarantee that the database will never be left in an inconsistent state.

Let's add another user:

>>> u = User(username='susan', email='susan@example.com')
>>> db.session.add(u)
>>> db.session.commit()

The database can answer a query that returns all the users:

>>> users = User.query.all()
>>> users
[<User john>, <User susan>]
>>> for u in users:
...     print(u.id, u.username)
...
1 john
2 susan

All models have a query attribute that is the entry point to run database queries.

The most basic query is that one that returns all elements of that class, which is appropriately named all(). Note that the id fields were automatically set to 1 and 2 when those users were added.
Here is another way to do queries. If you know the id of a user, you can retrieve that user as follows:

>>> u = User.query.get(1)
>>> u
<User john>

 

 

Now let's add a blog post:

>>> u = User.query.get(1)
>>> p = Post(body='my first post!', author=u)
>>> db.session.add(p)
>>> db.session.commit()

I did not need to set a value for the timestamp field because that field has a default, which you can see in the model definition. And what about the user_id field? Recall that the db.relationship that I created in the User class adds a posts attribute to users, and also a author attribute to posts. I assign an author to a post using the author virtual field instead of having to deal with user IDs. SQLAlchemy is great in that respect, as it provides a high-level abstraction over relationships and foreign keys.


To complete this session, let's look at a few more database queries:

>>> # get all posts written by a user
>>> u = User.query.get(1)
>>> u
<User john>
>>> posts = u.posts.all()
>>> posts
[<Post my first post!>]

>>> # same, but with a user that has no posts
>>> u = User.query.get(2)
>>> u
<User susan>
>>> u.posts.all()
[]

>>> # print post author and body for all posts 
>>> posts = Post.query.all()
>>> for p in posts:
...     print(p.id, p.author.username, p.body)
...
1 john my first post!

# get all users in reverse alphabetical order
>>> User.query.order_by(User.username.desc()).all()
[<User susan>, <User john>]

その他のQueryは、別のポスト、

良く使うQueryコマンド一覧

を参考にしてみてください。

Shell Context

Remember what you did at the start of the previous section, right after starting a Python interpreter? The first thing you did was to run some imports:


>>> from app import db
>>> from app.models import User, Post

While you work on your application, you will need to test things out in a Python shell very often, so having to repeat the above imports every time is going to get tedious. The flask shell command is another very useful tool in the flask umbrella of commands. The shell command is the second "core" command implemented by Flask, after run. The purpose of this command is to start a Python interpreter in the context of the application. What does that mean? See the following example:


(venv) $ python
>>> app
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'app' is not defined
>>>

(venv) $ flask shell
>>> app
<Flask 'app'>

With a regular interpreter session, the app symbol is not known unless it is explicitly imported, but when using flask shell, the command pre-imports the application instance. The nice thing about flask shell is not that it pre-imports app, but that you can configure a "shell context", which is a list of other symbols to pre-import.
The following function in microblog.py creates a shell context that adds the database instance and models to the shell session:


from app import app, db
from app.models import User, Post

@app.shell_context_processor
def make_shell_context():
    return {'db': db, 'User': User, 'Post': Post}

The app.shell_context_processor decorator registers the function as a shell context function. When the flask shell command runs, it will invoke this function and register the items returned by it in the shell session. The reason the function returns a dictionary and not a list is that for each item you have to also provide a name under which it will be referenced in the shell, which is given by the dictionary keys.
After you add the shell context processor function you can work with database entities without having to import them:


(venv) $ flask shell
>>> db
<SQLAlchemy engine=sqlite:////Users/migu7781/Documents/dev/flask/microblog2/app.db>
>>> User
<class 'app.models.User'>
>>> Post
<class 'app.models.Post'>

If you try the above and get NameError exceptions when you access db, User and Post, then the make_shell_context() function is not being registered with Flask. The most likely cause of this is that you have not set FLASK_APP=microblog.py in the environment. In that case, go back to Chapter 1 and review how to set the FLASK_APP environment variable. If you often forget to set this variable when you open new terminal windows, you may consider adding a .flaskenv file to your project, as described at the end of that chapter.