☆☆ 新着記事 ☆☆

2019年3月13日水曜日

Python self を理解する


クラスの定義
Pythonではクラスの定義にclass文を使用します。class文を使った定義の仕方は以下のようになります。

class クラス名:

class文の後にそのクラスの名前となるクラス名をつけて、最後に「:」を付け加えます。そのあとに一段落インデントを入れてクラス定義をしていきます。

class Test:
    pass  #何もしないクラスの場合はpassと記入する
test = Test()  #インスタンスを生成

メソッド
クラスにはメソッドを定義することができます。メソッドの定義は以下のように行います。
(※メソッドとは、簡単に言うとクラスの中で定義された関数のことです。)
class クラス名:
    def メソッド名(self):
        #メソッドの定義

メソッドは必ず1つ以上の引数を持ちます。また、引数のうち最初のものはselfという名前にすることになっています。

メソッドを呼び出し。

インスタンス.メソッド名()

class Test:

    def test_print(self):
        print('This is test')

test = Test()      #インスタンス生成
test.test_print()  #メソッド呼び出し

実行結果

This is test


以下のコードは、生成したインスタンスを使って”methodName”という名前のメソッドを
呼び出したものです。

>>> class className():
       def methodName(self):
           print("Hello World")
>>> instance =className()
>>> instance.methodName()

こちらを実行すると、

Hello World

と表示されました。 ここで記述したメソッドの引数として、“self”が登場します。


selfとは?

上述のように、クラスの中のコンストラクタやメソッドには、selfというものが存在します。
このselfは、インスタンス自身を示すものです。 また、selfはpythonの設計仕様で欠かすことはできません。
なお、名前を”self”以外のキーワード(例えば、myselfなど)にすることは可能ですが、慣例としてselfを使用しています。
pythonでは作成されたコードを後から読む人が、どのように実装されているかが一目でわかるように書く事が重要である為、基本的には”self”とするのが良いでしょう。
以降では、selfがどのように使われるかを確認します。

selfの使い方

インスタンス変数として参照する

まずはじめに、コンストラクタで使用する例を見てみましょう。
pythonにおけるコンストラクタは”__init__”と表記します。

class className():
    def __init__(self, strA, strB):
        self.strA = strA
        self.strB = strB
test = className("Hello", "World!")
print(test.strA)
print(test.strB)

 class className():
    def __init__(self, strA, strB):
        self.strA = strA
        self.strB = strB

test = className("Hello", "World!")
print(test.strA)
print(test.strB)

このように実装すると、以下のような結果を得られます。

Hello
World!

このように、インスタンスを生成する際に引数を渡すと、selfを使ってインスタンス変数として代入する事ができます。
この時に確認していただきたい点は、selfはインスタンス自身を示すものなので、呼び出す側は引数として値を入れない事です。
また、”__init__”のように__で囲まれた関数や変数は特殊な機能をもったものです。
発見したら注意しましょうね。


クラス変数として参照する
また、以下のようにクラス変数として別メソッドでも使う事ができます。

class className():
    def __init__(self, strA, strB):
        self.strA = strA
        self.strB = strB
   
    def output(self):
        print(self.strA)
        print(self.strB)

test = className("Hello", "World!")
test.output()



実行結果は、先ほどと同様です。

クラス継承の際も使える

selfはクラス変数として参照する事ができるので、クラスを継承した際にも参照する事ができます。

class classA():
    def __init__(self):
        self.strA = "Hello World!"
class classB(classA):
    def output(self):
        print(self.strA)
test = classB()
test.output()



実行結果:

Hello World!


selfの注意点
一点だけselfで注意していただきたい点があります。
以下のコードをご確認ください。

# -*- coding: utf-8 -*-
class cls():
    strA = "Hello python"
    def __init__(self):
        print("1: " + self.strA)
        self.strA = "Hello World!"
        print("2: " + self.strA)
        strA = "Hello python"
        print("3: " + self.strA)
test = cls()


これらを実行すると、何が表記されると思いますか?
実行結果は以下になります。

1: Hello python
2: Hello World!
3: Hello World!


予想に反した結果だと思った方も多いのではないでしょうか?
まず1について、”self.strA”はクラス内で定義したクラス変数”strA”と同じ意味を持っている為、参照エラーとはなりません。
続いて2について、こちらはインスタンス変数として”self.strA”が代入されたので、このような結果となります。
そして3番、こちらは最後に代入された”Hello Python”が表示されない点に疑問を感じられませんか?
実はこれ、pythonの仕様なのです。
pythonは、クラス変数もインスタンス変数も”self.変数名”という表記で参照できるのですが、
クラス変数にもインスタンス変数にも値がある場合は、インスタンス変数を優先して参照します。





コンストラクタとデストラクタ
メソッドの中でも、インスタンスが生成されるときに自動的に呼び出されるメソッドのことをコンストラクタと言います。コンストラクタを定義するには「init」という名前のメソッドを作成します。

class Test:
    def __init__(self, 引数1, 引数2, ….):
        #コンストラクタの定義

initも必ずselfという引数が必要になります。それ以外にも引数をつけることができ、その引数はインスタンスを生成する際にクラスを呼び出す時の引数が渡されます。

class Test:
    def __init__(self, num):
        self.num = num;  #このクラスが持つ「num」変数に引数を格納
    def print_num(self):
        print('引数で渡された数字は{}です。'.format(self.num))

test = Test(10)   #ここで渡された引数が__init__のnumに渡される
test.print_num()

実行結果

引数で渡された数字は10です。



コンストラクタとは逆に、インスタンスが不要になり削除される時に呼ばれるメソッドをデストラクタと言います。デストラクタは「del」という名前のメソッドで定義されます。

class Test:
    def __init__(self):
        print('コンストラクタが呼ばれました')
    def __del__(self):
        print('デストラクタが呼ばれました')

test = Test()  #インスタンスを生成
del test      #インスタンスを削除

実行結果

コンストラクタが呼ばれました
 デストラクタが呼ばれました

継承
Pythonのクラスも他のクラスを継承し、拡張することができます。クラスを継承する場合はクラス文に親となるクラスを指定します。親のクラスのメソッドを呼び出す場合はsuper()を使います。

class Test:
    def __init__(self, num):
        self.num = num;
    def print_num(self):
        print('引数で渡された数字は{}です。'.format(self.num))

class Test2(Test):  #Testクラスを継承
    def print_test2_info(self):
        print('このクラスはTestクラスを継承しています。')
        super().print_num()  #親クラスのprint_num()を呼び出す
test = Test2(10)
test.print_test2_info()

実行結果

このクラスはTestクラスを継承しています。
 引数で渡された数字は10です。

 シェルスクリプトで実行する特殊メソッド __repr__
•呼び方: レパー
•備考 : シェルスクリプトのみで実行可能なコード
 class Length():
    def __init__(self, data):
        self.data = data
    def __repr__(self):
        return "Length(%s, %s) " % (len(self.data), id(self))
    def __str__(self):
        return "Length(%s) " % (len(self.data))

>>> l = Length([1,2,3])
>>> l
実行結果:確認要
>>> print(l)
実行結果:確認要

__init__(self)__repr__(self) の例

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

2019年3月12日火曜日

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


DBモデル(one to many, many to many, one to one等)を中心に、 Python FlaskでRDBを使うときのデファクトである(且つ、簡単そうな) SQLAlchemy を使ってみる。

SQLAlchemyは、ORM ( Object Relational Mapper ) で、PythonのClassとして定義した内容を、SQL文に変換(mapping)してくれる。SQLAlchemyでは、MySQL, PostgreSQL, Oracle, MS-SQL, SQLite などの代表的なRDBがサポートされている。

今回は、Flaskを利用していてるので、SQLAlchemyよりも更に簡単そうな、
Flask-SQLAlchemyを利用していく。

(SQLALchemyとFlask-SQLAlchemyは、文法が若干違う。  Flask-SQLAlchemyは、利便性の向上のため、Flaskに特化してSQLAlchemyをパッケージ化したものなので、Flask-SQLAlchemyの中でSQLAlchemy文を記述しても、正しく解釈される。

尚、

◇1対1 1対多のデータベースのリレーションについては、
FLASK - SQLAlchemy 1対1 1対多 リレーション・データベース

に、分割して記述しました。

◇よく使う Query(検索コマンド)の一覧は、
flask-SQLAlchemy で良く使うクエリーコマンド一覧

に、まとめました。

◇作成したSQLiteのデータベースの内容を確認
== databaseのオンラインチェック用 url ==
SQLite Viewer: http://inloop.github.io/sqlite-viewer/
で出来ます。


(インストール)
$ pip install flask-sqlalchemy


 (利用する場合の基本)

Flask-SQLAlchemyを利用する場合、flask_sqlalchemy packageからSQLAlchemy classをインポートして、SQLAlchemy objectをapplication instance としてイニシエイトする。

プロジェクトフォルダ/app.py

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)


app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:pass@localhost/flask_app_db'


db = SQLAlchemy(app)
#...


 ① SQLALCHEMY_DATABASE_URIの設定

アクセスするDBの種類と位置を指定します。

Flask-SQLAlchemyでは、SQLALCHEMY_DATABASE_URI configuration key を利用する。




[記述フォーマット]

dialect+driver://username:password@host:port/database名

・dialect は、mysql, mssql, postgresql などのDBの名前。

#(このdriverの記述はオプション。)

# ・driver は、データベースにアクセスする時のDBAPI。
#     Sqliteでは指定不要だが、
#     MySQL  の場合、PyMysql。
#    PostgreSQLの場合、 Psycopg 2 等
# と指定する。 指定しなければデフォルト。 指定しない場合は、’+’は書かない。

例)

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://scott:tiger@localhost/[database: データベース名]'  *注)


・username と password はDBへのアクセス時に求められるなら記述するオプション。
・host は、DBサーバーの場所。 portは、オプション。databaseは、使用するdatabaseの名前。
・Sqliteは、ファイルベースのDBで、unsernameとpasswordを求められないので、URIには、データベースファイルまでのパスだけを記述すればよい。

(SQLITE3のようにメインAppと同じ場合、OS PATHで現在のパスを取得して記述する方法)

project_dir = os.path.dirname(os.path.abspath(__file__))
database_file = "sqlite:///{}".format(os.path.join(project_dir, "tw.db"))
app.config["SQLALCHEMY_DATABASE_URI"] = database_file

カレントディレクトリ(作業ディレクトリ)からの相対パスが得られる。標準ライブラリのosモジュールを使うと絶対パスが取得できる。
Factory Modelで、models.pyなどのDB定義を別に作成している場合、(__file__)でdbが作成されるのは、__init__.pyと同じフォルダ。

(# Windowsでsqliteを使う場合は///(スリー・スラッシュ))

sqlite:///c:/absolute/path/to/mysql.db
///(スリー・スラッシュ)はカレント・ディレクトリからの相対パスで記述しても良いし、
絶対パスを、スリー・スラッシュの直後にそのまま記述しても良い。
(フォルダでディレクトリをコピペする場合のwindowsの円マークは、pythonの記述の時には、
フォワード・スラッシュに手で書き換えて!)


◇ エラー・トラックによる負荷について
必須ではないが、毎回、FLASK-SQLAlchemyを起動するたびに、エラー・トラックを抑制しないと、サーバーに非常に負荷がかかるので対処して欲しい、というメッセージが出る。 将来は、エラー・トラックはデフォルトではしない設定になるらしい。 抑制する場合は、DATABASE BRI の設定の後に、以下の記述をしておくと良い。

app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

*この設定をする前に25秒かかっていた処理(データ取得、データベース登録・表示)が、この設定後でも、26秒程度だった。処理時間に、あまり効果はないかもしれない。 



③ SQLAlchemyのインスタンス(db)を定義する。

The SQLAlchemy instance db により、全てのSQLAlchemy functionsにアクセス可能になる。


以上が基本的な初期設定。 では、実際にDBを構築して操作してみる。


使い方

1. モデルを作る

* db = flask_sqlalchemy.SQLAlchemy()をつくる
* db.Modelを継承してモデルをつくる
* db.Columnでcolumnを定義する
* db.relationshipでモデル同士を繋ぐ


*********************
DBの構造<

ID
date
username
email
1
   
2
   



app.py(上記のapp.pyを修正)


 from flask import Flask
 from flask_sqlalchemy import SQLAlchemy

 app = Flask(__name__)

 app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///tmp/test.db'
 db = SQLAlchemy(app)


  class User(db.Model):

      __tablename__ = 'user'
      id = db.Column(db.Integer, primary_key=True)

     date  = db.Column(db.DateTime(), default=datetime.utcnow)
     username = db.Column(db.String(80), unique=True)
     email = db.Column(db.String(120), unique=True)

     def __init__(self, username, email):
         self.username = username
         self.email = email

     def __repr__(self):
         return '<User %r>' % self.username


 2. データベースを作る


 __tablename__ =
Flask-SQLAlchemyでは明示的に記述しなくてもよい。 SQLAlchemyでは明示的な記述が必須。



(Flask shellを起動する)

 >set FLASK_APP=app.py
 >flask shell
 >>> from app import db  #このappはファイル名
 >>> db.create_all()

 * create_all()で、データベースの枠組み(各Columnを含むテーブル)を作成する。 この段階では、
テーブルに値は入っていない。

*tmpフォルダが作ってあれば、test.dbが無い場合、db.create_all()で自動で作成される。
後でDBの構造を変更(テーブルの追加)して、db.create_all()を実行しても、それ以前のデータは保持されているので、その後、何度実行しても問題ない。 

(SQLITEの場合、逆に、一度テーブルを作ってしまうと、後からコラムの属性変更をすることは非常に難しい。属性を変えたい場合、新しいコラムを作って、そこにデータを移行し、元のコラムを削除する。 新しいコラムの順番(位置)は変えられないので、テーブル全体のコラムの構成は変わってしまうので、最初に作る時に充分、注意する。又は、FLASK-MIGRATEを利用する。)


*注)dbの作り方

方法①) Flask shellを利用する場合

Flaskアプリに、DB操作まで書き込んであると flask shellを実行した時点でエラーになる。
まずは、DBを作成するまでを記述して、flask shell でDB作成。
その後、FlaskアプリにDBの処理を記述し、flask shell をexit()し、
> python <フラスク・アプリファイル名>.py で実行するのが確実。

*(2020/4/18 メモ)
set FLASK_APP=
flask shell
としただけで、DBが作られる??


方法②) pyhonコマンドラインから実行する場合

通常の(仮想環境などの)コマンドラインで、flaskの環境変数を設定( set FLASK_APP=)をし、
その後で、pythonを起動。

>from <フラスク・アプリファイル名>. import db
>db.create_all( )

でも、dbを作れる。


*注)1つのflask アップに複数のDBクラスがあるものをpythonで扱う場合は、classを特定する。
>from <フラスク・アプリのファイル名>. import db
>from <フラスク・アプリのファイル名>. import < class名>

これで特定のクラス(テーブル)をpythonで操作できるようになる。

result = User.query.all()
for r in result:
    print(r)

とか。 

以下の典型的な構成のように、ファイルを分割して記入している場合、
--- app.py
 |
 |-- app(Directory)
       |---  __init__.py
       |--- models.py

from app.models import (テーブルのクラス名)で、インポートする。




◇データベースのテーブル削除
>>> from yourapplication import db
>>> db.drop_all()
(データベース・ファイルそのもは削除されない。)


3. CRUD操作

create


 (*クラスを定義したらインポートする)
 >>> from app import User  #このappもファイル名/ Userはクラス名
 (*インスタンスの生成)
 >>> admin = User(username='admin', email='admin@example.com')
 >>> guest = User(username='guest', email='guest@example.com')

 >>> db.session.add(admin)
 >>> db.session.add(guest)
 >>> db.session.commit()

 *Userクラスをインポートして、userというインスタンスを作成している。 db.session.add()メソッドを呼び出して、これをテーブルに追加した後に、コミットを行っている。これでデータベースにデータが保存された。*dbにcommitした際に重複データがありエラーになった場合、 db.session.rollback()で、commit()前の状態に戻す。

一括登録は、

 db.session.add_all([admin, guest])
 db.session.commit()


read all

 >>> users = User.query.all()
>>> print(users)
[<User 'admin'>, <User 'guest'>]

*ここでusernameだけが抽出されるのは、app.pyに return '<User %r>' % self.username と記述しているから。'<User %r>' % self.email  と記述すると

[<User 'admin@example.com'>, <User 'guest@example.com'>] とReturnが返る。

class定義以降、何も記述しないでprint(users)を実行すると[<User 1>, <User 2>] を返す。

又は


>>> db.session.query(User).all()
#[<User 'admin'>, <User 'guest'>]

 read, delete

 >>> User.query.filter_by(username='admin').first()
<User 'admin'>
  (又は、db.session.query(User).filter(username=='admin’).first()<?>)

filter_by()メソッドとfilter()メソッドの2つのメソッドがある。

取得されるオブジェクトに格納されるのは、行全体のデータ。このオブジェクトを使って、アップデート等をおこなう。

例)
usr1 = db.session.query(User).filter(username ==‘admin').first()

usr1.email = ' new@tesr.com'
db.session.add(usr1)
db.session.commi()

これで、adminのデータがアップデートされる。(アップデートの項で同じことを再説明)

filter_byメソッドは「キーワード引数の形式で条件を指定」する。
これに対して、filterメソッドはより柔軟な形で条件を記述できる
(例えば、item_idの値が100以上といった条件を記述できるのはfilterメソッドだけとなる)。

>>>db.session.delete(admin)
>>> users = User.query.all()
>>> print(users)
[<User 'guest'>]

 *条件を指定して削除も可能
db.query.filter(user_id > 1).delete()
db.session.commit()

*全レコードの一括削除
db.session.query(クラス名).delete()
db.session.commit()



 read, update

行毎のオブジェクトを作ってから、変更し、commit()する。
 user = db.session.query(User).filter_by(name='qtaro').first()
 user.email = 'qtaro@test.com'
 db.session.add(user)
 db.session.commit()



Database内に作られている全てのTable(Table名と全てのColumn)を確認する。


>>from app import db
>> for t in db.metadata.tables.items():
...     print(t)
...
('user', Table('user', MetaData(bind=None), Column('id', Integer(), table=<user>, primary_key=True, nullable=False), Column('username', String(length=20), table=<user>, nullable=False), Column('email', String(length=120), table=<user>, nullable=False), Column('image_file', String(length=20), table=<user>, nullable=False, default=ColumnDefault('default.jpg')), Column('password', String(length=60), table=<user>, nullable=False), schema=None))
('tw_msg', Table('tw_msg', MetaData(bind=None), Column('id', Integer(), table=<tw_msg>, primary_key=True, nullable=False), Column('time', String(length=6), table=<tw_msg>), Column('message', String(length=140), table=<tw_msg>), Column('hashtag', String(length=140), table=<tw_msg>), Column('url', String(length=140), table=<tw_msg>), Column('lentgh', String(le
ngth=140), table=<tw_msg>), schema=None))
 






(練習用スクリプト)
import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
project_dir = os.path.dirname(os.path.abspath(__file__))
database_file = "sqlite:///{}".format(os.path.join(project_dir, "tweet.db"))
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = database_file
db = SQLAlchemy(app)

class Tweet (db.Model):
    # title = db.Column(db.String(80), unique=True, nullable=False, primary_key=True)
    id = db.Column(db.Integer, unique=True, nullable=False, primary_key=True)
    t_id = db.Column(db.String(30), unique=True, nullable=False)
    text = db.Column(db.String(300), nullable=False)
    def __repr__(self): #シェルスクリプトで利用する特殊メソッド
        #return "<Title: {}>".format(self.title)
        return "<{}:{}>".format(self.t_id, self.text[:140])

@app.before_first_request
def init():
    db.create_all()
   
def Create():
    t1 =Tweet(t_id = "0040", text='Hello!')
    t2 =Tweet(t_id = "0050", text='World!')
    t3 =Tweet(t_id = "0060", text='Bon Jour')
    db.session.add_all([t1, t2, t3])
    db.session.commit()
    tweet = Tweet.query.all()
    print(tweet)
Create()




[サーバー環境でSQLに接続してエラーになる場合]

*ここの記述は、サーバーにDeployする場合でも、SQLITEを利用している場合は不要です。
(本当にSQLITEはお手軽です。)


DATABASE_URI'= 'mysql://scott:tiger@localhost/[database: データベース名]' 
で、

>> set FLASK_APP=app.py
>>python app.py

を実行すると、以下のエラーが出る場合がある。(というか、でた。)

(1) DBコネクター(API)が必要でした。

dbapi = dialect_cls.dbapi(**dbapi_args)
 File "/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 118, in dbapi
    return __import__("MySQLdb")
ModuleNotFoundError: No module named 'MySQLdb'

調べると、SQLAlchemy(及び、FLASK=SQLAlchemy)は、デフォルトではSQLITEしか動かず、その他のDBを操作する場合、相応のDriver (接続API;DBAPI が必須とのこと。

MySQLの場合のDBAPIは、MySQLdbPyMySQLがある。

https://stackoverflow.com/questions/7224807/what-is-pymysql-and-how-does-it-differ-from-mysqldb-can-it-affect-django-deploy

を、参考にすると、デフォルトのMySQLdbよりも使いかってがよさそうなので、PyMySQLをインストールしてURIを指定。

pip install PyMySQL

app.config["SQLALCHEMY_DATABASE_URI"] = 'mysql+pymysql://[ユーザ名]:[パスワード]@localhost/[データベース名].db?charset=utf8'

(2) 先にDBは作成しておかないといけない

(1)の段階でpython アプリを実行すると、今度は、MySQLに接続して後で、DBがない、とエラーに。
(1049, "Unknown database 'msg.db'")

SQLITEの場合、ここでDBを自動で作ってくれるのですが、SQLは事前に作っておかないといけないみたい。

mysql -u root -p
mysql> create database [データベース名];

このDBさえ作っておけば、テーブルはFLASK-SQLAlchemyのモデルから自動に作成される。

尚、dbを作成した後に接続しようとしても

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1049, "Unknown database 'msg.db'")

というエラーに遭遇した。localhost/[データベース名].db のように拡張子まで記述してはいけないようだ。 .dbを書かなかったら接続できた。

[sqlite の場合は、.dbまで記述する。 難しい。]



[SQLITEのデータベースを削除する]

ファイルベースのDBなので、本来なら簡単に削除できる。
できないときは、

pythonを起動し、
>>> import sqlite3
>>> connection = sqlite3.connect('msg.db')
>>> connection.close()
で、データベースのコネクションを閉じてみる。
更に詳細は、
https://mycodingjp.blogspot.com/2019/06/python3-sqlite3.html



Creating Tables (テーブルの作成)

To work with other databases, we have to install a DBAPI compliant driver. As we are using MySQL, we will install PyMySql driver.
 (env) overiq@vm:~/flask_app$ pip install pymysql

Next login to MySQL server and create a database named flask_app_db using the following command:
(env) overiq@vm:~/flask_app$ mysql -u root -p
mysql>
mysql> CREATE DATABASE flask_app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.26 sec)
mysql> \q
Bye
(env) overiq@vm:~/flask_app$
This command creates flask_app_db database with full Unicode support.
To create the necessary tables from models invoke create_all() method of the SQLAlchemy object (db). Start the Python shell and
enter the following command:

(env) overiq@vm:~/flask_app$ python main2.py shell
>>>
>>> from main2 import db
>>>
>>> db.create_all()
>>>

create_all() methodでは、DBのClassでテーブルのスキーマを変更しても、実際のテーブルには反映されない。

対応策)
FLASK - MIGRATE


The create_all() method only creates table if it doesn’t already exist in the database. So you can run it safely multiple times. In addition to that, the create_all() method doesn’t take account of the modifications made to the models while creating tables. This means that once a table is created in the database, running create_all() method after modifying its model will not alter the table
schema. To do that, we use a migration tool like Alembic. We will learn how to perform database migration using Alembic in lesson

Database Migrations with Alembic.
To view the tables created login to MySQL server and execute the following command:
<mysql>
mysql> use flask_app_db
Database changed
mysql>
mysql> show tables;
+------------------------+
| Tables_in_flask_app_db |
+------------------------+
| categories             |
| post_tags              |
| posts                  |
| tags                   |
+------------------------+
4 rows in set (0.02 sec)
mysql>




Another way to view the tables is to use a Database Administration tool like HeidiSQL. HeidiSQL is a cross-platform open source software for administering MySQL, MS-SQL, and PostgreSQL.

It allows us to browse data, edit data, view schema, alter table and much more without writing a single line of SQL. You can download HeidiSQL from here.

Once installed open flask_app_db database in HeidiSQL and you will be displayed a list of tables like this:

The flask_app_db database now has 4 tables in it. Tables namely categories, posts and tags are created directly from models and table post_tags is an association table which represents a many-to-many relationship between Post and Tag model.

The SQLAlchemy class also defines a method called drop_all() to delete all the tables from the database. Remember that drop_all()
doesn’t care whether tables contain any data or not. It will delete all the data and tables right away, so use it sparingly.
We now have all the tables in place. Let’s enter some data into them.

Inserting Data

To create a new record using SQLAlchemy, we follow these steps:
1.Create an object.
2.Add the object to the session.
3.Commit the session.

In SQLAlchemy, we interact with the database using session. Fortunately, we don’t need to create session manually, Flask-SQLAlchemy
manages that for us. We access the session object as db.session. It is the session object which handles the connection to the
database. The session object is also a handler for the transaction. By default, transaction implicitly starts and will remain open until the session is committed or rolled back.
Start the Python shell and create some model objects as follows:

(env) $ python main2.py shell
>>>
>>> from main2 import db, Post, Tag, Category
>>>
>>> c1 = Category(name='Python', slug='python')
>>> c2 = Category(name='Java', slug='java')
>>>
Here we have created two Category objects. We can access attributes of an object using the dot(.) operator as follows:

>>>
>>> c1.name, c1.slug
('Python', 'python')
>>>
>>> c2.name, c2.slug
('Java', 'java')
>>>
Next, we add the objects to the session.
>>>
>>> db.session.add(c1)
>>> db.session.add(c2)
>>>
Adding objects to the session doesn’t actually writes them to the database, it only prepares the objects to be saved in the next
commit. We can verify this by checking the primary key of the objects.
>>>
>>> print(c1.id)
None
>>>
>>> print(c2.id)
None
>>>
The value of id attribute of both the objects is None. That means our objects are not yet saved in the database.
Instead of adding one object to the session at a time, we can use add_all() method. The add_all() method accepts a list of objects
to be added to the session.

>>>
>>> db.session.add_all([c1, c1])
>>>

Adding an object to the session multiple times doesn’t throw any errors. At any time, you can view the objects in the session using
db.session.new.

>>>
>>> db.session.new
IdentitySet([<None:Python>, <None:java>])
>>>
Finally, to save the objects to the database call commit() method as follows:

>>>
>>> db.session.commit()
>>>

Accessing the id attribute of the Category object will now return the primary key instead of None.
>>>
>>> print(c1.id)
1
>>>
>>> print(c2.id)
2
>>>


At this point, categories table in HeidiSQL should look like this:
Our newly created categories are not associated with any posts. So c1.posts and c2.posts would return an empty list.
 >>>
>>> c1.posts
[]
>>>
>>> c2.posts
[]
>>>

Let’s create some posts now.

>>>
>>> p1 = Post(title='Post 1', slug='post-1', content='Post 1', category=c1)
>>> p2 = Post(title='Post 2', slug='post-2', content='Post 2', category=c1)
>>> p3 = Post(title='Post 3', slug='post-3', content='Post 3', category=c2)
>>>
Instead of passing category while creating the Post object, we can also set it as follows:
>>>
>>> p1.category = c1
>>>
Add the objects to the session and commit.
>>>
>>> db.session.add_all([p1, p2, p3])
>>> db.session.commit()
>>>
Access the posts attribute of the Category object again, this time you will get a non-empty list like this:
>>>
>>> c1.posts
[<1:Post 1>, <2:Post 2>]
>>>
>>> c2.posts
[<3:Post 3>]
>>>

From the other side of the relationship, we can access the Category object to which the post belongs using the category attribute on
the Post object.
>>>
>>> p1.category
<1:Python>
>>>
>>> p2.category
<1:Python>
>>>
>>> p3.category
<2:Java>
>>>

Remember that, all of this became possible because of the relationship() directive in the Category model. We now have three posts in
our database but none of them are associated with any tags.

>>>
>>> p1.tags, p2.tags, p3.tags
([], [], [])
>>>
Its time to create some tags. In the shell create Tag objects as follows:
>>>
>>> t1 = Tag(name="refactoring", slug="refactoring")
>>> t2 = Tag(name="snippet", slug="snippet")
>>> t3 = Tag(name="analytics", slug="analytics")
>>>
>>> db.session.add_all([t1, t2, t3])
>>> db.session.commit()
>>>
This code creates three tag objects and commits them to the database. Our posts are still not connected to any tags. Here he how we
can connect a Post object to a Tag object.
>>>
>>> p1.tags.append(t1)
>>> p1.tags.extend([t2, t3])
>>> p2.tags.append(t2)
>>> p3.tags.append(t3)
>>>
>>> db.session.add_all([p1, p2, p3])
>>>
>>> db.session.commit()
>>>
This commit adds the following five records in the post_tags table.
Our posts are now associated with one or more tags:
>>>
>>> p1.tags
[<1:refactoring>, <2:snippet>, <3:analytics>]
>>>
>>> p2.tags
[<2:snippet>]
>>>
>>> p3.tags
[<3:analytics>]
>>>
From the other way around, we can access posts which belongs to a tag as follows:
>>>
>>> t1.posts
[<1:Post 1>]
>>>
>>> t2.posts
[<1:Post 1>, <2:Post 2>]
>>>
>>> t3.posts
[<1:Post 1>, <3:Post 3>]
>>>
>>>
It is important to note that instead of first committing Tag objects and then associating it with Post objects, we could have done
all this at once as follows:
>>>
>>> t1 = Tag(name="refactoring", slug="refactoring")
>>> t2 = Tag(name="snippet", slug="snippet")
>>> t3 = Tag(name="analytics", slug="analytics")
>>>
>>> p1.tags.append(t1)
>>> p1.tags.extend([t2, t3])
>>> p2.tags.append(t2)
>>> p3.tags.append(t3)
>>>
>>> db.session.add(p1)
>>> db.session.add(p2)
>>> db.session.add(p3)
>>>
>>> db.session.commit()
>>>
Notice that in lines 11-13, we are only adding the Post objects to the session. The Tag and Post object are connected via a
many-to-many relationship. As a result, adding a Post object to the session implicitly adds its associated Tag objects into the
session as well. Even if you still add Tag objects manually to the session, you wouldn’t get any error.


Updating Data
To update an object simply set its attribute to a new value, add the object to the session and commit the changes.
>>>
>>> p1.content # initial value
'Post 1'
>>>
>>> p1.content = "This is content for post 1" # setting new value
>>> db.session.add(p1)
>>>
>>> db.session.commit()
>>>
>>> p1.content # final value
'This is content for post 1'
>>>


Deleting Data
To delete an object use the delete() method of the session object. It accepts an object and marks it to be deleted in the next commit.
Create a new temporary tag named seo and associate it with the post p1 and p2 as follows:
>>>
>>> tmp = Tag(name='seo', slug='seo') # creating a temporary Tag object
>>>
>>> p1.tags.append(tmp)
>>> p2.tags.append(tmp)
>>>
>>> db.session.add_all([p1, p2])
>>> db.session.commit()
>>>
This commit adds a total of 3 rows. One in the tags table and two in the post_tags table. In the database these three rows look like
this:
Let’s now delete the seo tag:
>>>
>>> db.session.delete(tmp)
>>> db.session.commit()
>>>
This commit deletes all the three rows added in the previous step. However, it doesn’t delete the post to which the tag was
associated.
By default, if you delete an object in the parent table (like categories) then the foreign key of its associated object in the child
table (like posts) is set to NULL. The following listing demonstrates this behavior by creating a new category object along with a
post object and then deleting that category object:
>>>
>>> c4 = Category(name='css', slug='css')
>>> p4 = Post(title='Post 4', slug='post-4', content='Post 4', category=c4)
>>>
>>> db.session.add(c4)
>>>
>>> db.session.new
IdentitySet([<None:css>, <None:Post 4>])
>>>
>>> db.session.commit()
>>>

This commit adds two rows. One in the categories table and one in the posts table.

Now let’s see what happens when we delete a Category object.
>>>
>>> db.session.delete(c4)
>>> db.session.commit()
>>>

This commit deletes the css category from the categories table and sets the foreign key (category_id) of its associated post to NULL.

On some occasions, you might want to delete all child records once parent records is deleted. We can achieve that by passing
cascade='all,delete-orphan' to the db.relationship() directive. Open main2.py file and modify the db.relationship() directive in the
Category model as follows (changes are highlighted):
flask_app/main2.py
#...
class Category(db.Model):
    #...
    posts = db.relationship('Post', backref='category', cascade='all,delete-orphan')
#...

From now on, deleting a category will also remove all the posts associated with it. Restart the shell for the changes to take
effect, import necessary objects, and create a new category along with a post as follows:
(env) overiq@vm:~/flask_app$ python main2.py shell
>>>
>>> from main2 import db, Post, Tag, Category
>>>
>>> c5 = Category(name='css', slug='css')
>>> p5 = Post(title='Post 5', slug='post-5', content='Post 5', category=c5)
>>>
>>> db.session.add(c5)
>>> db.session.commit()
>>>

Here is how the database should look after this commit.
Delete the category now.

>>>
>>> db.session.delete(c5)
>>> db.session.commit()
>>>

After this commit database should look like this:

Querying Data
To query database we use the query() method of the session object. The query() method returns a flask_sqlalchemy.BaseQuery object
which is just an extension of the original sqlalchemy.orm.query.Query object. The flask_sqlalchemy.BaseQuery object represents the
SELECT statement that will be used to query the database. The following table lists some common methods of
flask_sqlalchemy.BaseQuery class.
Method Description
all()  returns the result of the query (represented by flask_sqlalchemy.BaseQuery) as a list.
count()  returns the total number of records in the query.
first()  returns the first result of the query or None, if there are no rows in the result.
first_or_404()  returns the first result of the query or HTTP 404 Error, if there are no rows in the result.
get(pk)  returns an object that matches the given primary key (pk), or None, if no such object is found.
get_or_404(pk)  returns an object that matches the given primary key (pk), or HTTP 404 Error, if no such object is found.
filter(*criterion)  returns a new flask_sqlalchemy.BaseQuery instance after applying the WHERE clause to the query.
limit(limit)  return a new flask_sqlalchemy.BaseQuery instance after applying the LIMIT clause to the query.
offset(offset)  return a new flask_sqlalchemy.BaseQuery instance after applying the OFFSET clause to the query.
order_by(*criterion)  return a new flask_sqlalchemy.BaseQuery instance after applying ORDER BY clause to the query.
join()  return a new flask_sqlalchemy.BaseQuery instance after creating SQL JOIN on the query.
all() method
In its simplest form, the query() method can take one or more model class or columns as arguments.

The following code returns all
the records from the posts table.
>>>
>>> db.session.query(Post).all()
[<1:Post 1>, <2:Post 2>, <3:Post 3>, <4:Post 4>]
>>>
Similarly, the following code returns all the records from the categories and tags table.
>>>
>>> db.session.query(Category).all()
[<1:Python>, <2:Java>]
>>>
>>>
>>> db.session.query(Tag).all()
[<1:refactoring>, <2:snippet>, <3:analytics>]
>>>

To get the raw SQL used to query the database simply print the flask_sqlalchemy.BaseQuery object as follows:
>>>
>>> print(db.session.query(Post))
SELECT
    posts.id AS posts_id,
    posts.title AS posts_title,
    posts.slug AS posts_slu g,
    posts.content AS posts_content,
    posts.created_on AS posts_created_on,
    posts.u pdated_on AS posts_updated_on,
    posts.category_id AS posts_category_id
FROM
    posts
>>>
>>>
In the preceding examples, the data is returned from all columns of the table. We can prevent this by passing the column names
explicitly to the query() method as follows:
>>>
>>> db.session.query(Post.id, Post.title).all()
[(1, 'Post 1'), (2, 'Post 2'), (3, 'Post 3'), (4, 'Post 4')]
>>>

count() method
The count() method returns the number of results returned by the query.

>>> db.session.query(Post).count() # get the total number of records in the posts table
4
>>> db.session.query(Category).count() # get the total number of records in the categories table
2
>>> db.session.query(Tag).count() # get the total number of records in the tags table
3
>>>


first() method
The first() method returns only the first result of the query or None if the query returns zero results.


>>>
>>> db.session.query(Post).first()
<1:Post 1>
>>>
>>> db.session.query(Category).first()
<1:Python>
>>>
>>> db.session.query(Tag).first()
<1:refactoring>
>>>
get() method
The get() method returns the instance which matches the primary key passed to it or None if no such object found.

>>>
>>> db.session.query(Post).get(2)
<2:Post 2>
>>>
>>> db.session.query(Category).get(1)
<1:Python>
>>>
>>> print(db.session.query(Category).get(10)) # no result found for primary key 10
None
>>>


get_or_404() method
Same as get() method but instead of returning None when no object found, it returns HTTP 404 Error.

>>>
>>> db.session.query(Post).get_or_404(1)
<1:Post 1>
>>>
>>>
>>> db.session.query(Post).get_or_404(100)
Traceback (most recent call last):
...
werkzeug.exceptions.NotFound: 404 Not Found: The requested URL was not found on
the server. If you entered the URL manually please check your spelling and try
again.
>>>


filter() method
The filter() method allows us to filter our result by adding WHERE clause to the query. At the minimum, it accepts a column, an operator and a value. Here is an example:

>>>
>>> db.session.query(Post).filter(Post.title == 'Post 1').all()
[<1:Post 1>]
>>>

This query returns all the posts where title is "Post 1". The SQL equivalent of the query is:

>>>
>>> print(db.session.query(Post).filter(Post.title == 'Post 1'))
SELECT
    posts.id AS posts_id,
    posts.title AS posts_title,
    posts.slug AS posts_slu g,
    posts.content AS posts_content,
    posts.created_on AS posts_created_on,
    posts.u pdated_on AS posts_updated_on,
    posts.category_id AS posts_category_id
FROM
    posts
WHERE
    posts.title = % (title_1) s
>>>
>>>

The string % (title_1) s in the WHERE clause is a placeholder and will be replaced by the actual value when the query is executed.
We can pass multiple filters to the filter() method and they will be joined together using SQL AND operator. For example:
>>>
>>> db.session.query(Post).filter(Post.id >= 1, Post.id <= 2).all()
[<1:Post 1>, <2:Post 2>]
>>>
>>>

This query returns all the posts whose primary key is greater than or equal to 1 but less than or equal to 2. Its SQL equivalent is:

>>>
>>> print(db.session.query(Post).filter(Post.id >= 1, Post.id <= 2))
SELECT
    posts.id AS posts_id,
    posts.title AS posts_title,
    posts.slug AS posts_slu g,
    posts.content AS posts_content,
    posts.created_on AS posts_created_on,
    posts.u pdated_on AS posts_updated_on,
    posts.category_id AS posts_category_id
FROM
    posts
WHERE
    posts.id >= % (id_1) s
AND posts.id <= % (id_2) s
>>>


first_or_404() method
Same as first() method but instead of returning None when the query returns no result, it returns HTTP 404 Error.

>>>
>>> db.session.query(Post).filter(Post.id > 1).first_or_404()
<2:Post 2>
>>>
>>> db.session.query(Post).filter(Post.id > 10).first_or_404().all()
Traceback (most recent call last):
...
werkzeug.exceptions.NotFound: 404 Not Found: The requested URL was not found on
the server. If you entered the URL manually please check your spelling and try
again.
>>>


limit() method
The limit() method adds LIMIT clause to the query. It accepts the number of rows you want to return from the query.
>>>
>>> db.session.query(Post).limit(2).all()
[<1:Post 1>, <2:Post 2>]
>>>
>>> db.session.query(Post).filter(Post.id >= 2).limit(1).all()
[<2:Post 2>]
>>>

The SQL equivalent of the above queries is as follows:
>>>
>>> print(db.session.query(Post).limit(2))
SELECT
    posts.id AS posts_id,
    posts.title AS posts_title,
    posts.slug AS posts_slu g,
    posts.content AS posts_content,
    posts.created_on AS posts_created_on,
    posts.u pdated_on AS posts_updated_on,
    posts.category_id AS posts_category_id
FROM
    posts
LIMIT % (param_1) s
>>>
>>>
>>> print(db.session.query(Post).filter(Post.id >= 2).limit(1))
SELECT
    posts.id AS posts_id,
    posts.title AS posts_title,
    posts.slug AS posts_slu g,
    posts.content AS posts_content,
    posts.created_on AS posts_created_on,
    posts.u pdated_on AS posts_updated_on,
    posts.category_id AS posts_category_id
FROM
    posts
WHERE
    posts.id >= % (id_1) s
LIMIT % (param_1) s
>>>
>>>


offset() method
The offset() method adds the OFFSET clause to the query. It accepts offset as an argument. It is commonly used with the limit()
clause.

>>>
>>> db.session.query(Post).filter(Post.id > 1).limit(3).offset(1).all()
[<3:Post 3>, <4:Post 4>]
>>>

The SQL equivalent of the above query is as follows:
>>>
>>> print(db.session.query(Post).filter(Post.id > 1).limit(3).offset(1))
SELECT
    posts.id AS posts_id,
    posts.title AS posts_title,
    posts.slug AS posts_slu g,
    posts.content AS posts_content,
    posts.created_on AS posts_created_on,
    posts.u pdated_on AS posts_updated_on,
    posts.category_id AS posts_category_id
FROM
    posts
WHERE
    posts.id > % (id_1) s
LIMIT % (param_1) s, % (param_2) s
>>>

The strings % (param_1) s and % (param_2) s are placeholders for offset and limit respectively.
order_by() method
The order_by() method is used to order the result by adding ORDER BY clause to the query. It accepts column names on which the order
should be based. By default, it sorts in ascending order.

>>>
>>> db.session.query(Tag).all()
[<1:refactoring>, <2:snippet>, <3:analytics>]
>>>
>>> db.session.query(Tag).order_by(Tag.name).all()
[<3:analytics>, <1:refactoring>, <2:snippet>]
>>>

To sort in descending order use db.desc() function as follows:

>>>
>>> db.session.query(Tag).order_by(db.desc(Tag.name)).all()
[<2:snippet>, <1:refactoring>, <3:analytics>]
>>>

join() method
The join() method is used to create SQL JOIN. It accepts table name for which you want to create SQL JOIN.

>>>
>>> db.session.query(Post).join(Category).all()
[<1:Post 1>, <2:Post 2>, <3:Post 3>]
>>>

This query is equivalent to the following SQL:

 >>>
>>> print(db.session.query(Post).join(Category))
SELECT
    posts.id AS posts_id,
    posts.title AS posts_title,
    posts.slug AS posts_slu g,
    posts.content AS posts_content,
    posts.created_on AS posts_created_on,
    posts.u pdated_on AS posts_updated_on,
    posts.category_id AS posts_category_id
FROM
    posts
INNER JOIN categories ON categories.id = posts.category_id

The join() method is commonly used to get the data from one or more table in a single query. For example:

>>>
>>> db.session.query(Post.title, Category.name).join(Category).all()
[('Post 1', 'Python'), ('Post 2', 'Python'), ('Post 3', 'Java')]
>>>

We can create SQL JOIN for more than two table by chaining join() method as follows:

 db.session.query(Table1).join(Table2).join(Table3).join(Table4).all()

Let’s conclude this lesson by completing our Contact Form.
Recall that, In lesson Form Handling in Flask, we have created a Contact Form to receive feedback from users. As things stand, the
contact() view function doesn’t save the submitted feedback to the database. It only prints the feedback to the console. To save the feedback to the database, we have to create a new table first. Open main2.py and add the Feedback model just below the Tag model as follows:


flask_app/main2.py
#...
class Feedback(db.Model):
    __tablename__ = 'feedbacks'
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(1000), nullable=False)
    email = db.Column(db.String(100), nullable=False)
    message = db.Column(db.Text(), nullable=False)
    created_on = db.Column(db.DateTime(), default=datetime.utcnow)
    def __repr__(self):
        return "<{}:{}>".format(self.id, self.name)
#...

Restart the Python shell and invoke create_all() method of the db object to create the feedbacks table.

(env) overiq@vm:~/flask_app$ python main2.py shell
>>>
>>> from main2 import db
>>>
>>> db.create_all()
>>>

Next, modify the contact() view function as follows (changes are highlighted):
flask_app/main2.py
#...
@app.route('/contact/', methods=['get', 'post'])
def contact():
    form = ContactForm()
    if form.validate_on_submit():
        name = form.name.data
        email = form.email.data
        message = form.message.data
        print(name)
        print(Post)
        print(email)
        print(message)
        # db logic goes here
        feedback = Feedback(name=name, email=email, message=message)
        db.session.add(feedback)
        db.session.commit()
        print("\nData received. Now redirecting ...")
        flash("Message Received", "success")
        return redirect(url_for('contact'))
    return render_template('contact.html', form=form)
#...

Start the server and visit http://127.0.0.1:5000/contact/, fill the form and submit feedback.

The submitted feedback in HeidiSQL should look like this:

ちょっと別ねた==================

2019年3月9日土曜日

Flask(5) Follower機能 -self referential many to many model


(このPostのOutput Image)





生徒が複数の先生に紐づけられる、先生も複数の生徒をもつ、というような関係を
Many-to-manyの関係と呼ぶ。 
この関係を紐づける場合、RDBでは、生徒のTableと先生のTable以外に
association tableというテーブルを持つ必要がある。
 
特に、Many-to-manyの関係で、関係付られるデータベースが1つ(この場合、ユーザ)
しかないものを、self-referential relationship(自己参照関係)という。
 
A relationship in which instances of a class are linked to other instances of the same class
is called a self-referential relationship
 
1のユーザが複数のユーザをフォローし、複数のユーザからフォローされる関係を記述していく。
 
1. 新しいassociation tableを作成する。
(models.pyへの追加記述)
followers = db.Table('followers',
    db.Column('follower_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('followed_id', db.Integer, db.ForeignKey('user.id'))
)
このテーブルは、foreign keysをもつ予備的なテーブルなので、クラスとして定義していない。
 
2self-referential relationshipを記述する。
(models.pyへの追加記述)
Userというクラス(インスタンス)に、UserというDB作成する。
class User(UserMixin, db.Model):
    # ...
    followed = db.relationship(
        'User', secondary=followers,
        primaryjoin=(followers.c.follower_id == id),
        secondaryjoin=(followers.c.followed_id == id),
        backref=db.backref('followers', lazy='dynamic'), lazy='dynamic')
 
 
Let's add followers to the database first. Here is the followers association table:


app/models.py: Followers association table

 

followers = db.Table('followers',

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

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

 
 
This is a direct translation of the association table from my diagram above. Note that I am not declaring this table as a model, like I did for the users and posts tables. Since this is an auxiliary table that has no data other than the foreign keys, I created it without an associated model class.
Now I can declare the many-to-many relationship in the users table:


app/models.py: Many-to-many followers relationship
 
class User(UserMixin, db.Model):
    # ...
    followed = db.relationship(
        'User', secondary=followers,
        primaryjoin=(followers.c.follower_id == id),
        secondaryjoin=(followers.c.followed_id == id),
        backref=db.backref('followers', lazy='dynamic'), lazy='dynamic')
 
The setup of the relationship is non-trivial. Like I did for the posts one-to-many relationship, I'm using the db.relationship function to define the relationship in the model class. This relationship links User instances to other User instances, so as a convention let's say that for a pair of users linked by this relationship, the left side user is following the right side user. I'm defining the relationship as seen from the left side user with the name followed, because when I query this relationship from the left side I will get the list of followed users (i.e those on the right side). Let's examine all the arguments to the db.relationship() call one by one:
  • 'User' is the right side entity of the relationship (the left side entity is the parent class). Since this is a self-referential relationship, I have to use the same class on both sides.
  • secondary configures the association table that is used for this relationship, which I defined right above this class.
  • primaryjoin indicates the condition that links the left side entity (the follower user) with the association table. The join condition for the left side of the relationship is the user ID matching the follower_id field of the association table. The followers.c.follower_id expression references the follower_id column of the association table.
  • secondaryjoin indicates the condition that links the right side entity (the followed user) with the association table. This condition is similar to the one for primaryjoin, with the only difference that now I'm using followed_id, which is the other foreign key in the association table.
  • backref defines how this relationship will be accessed from the right side entity. From the left side, the relationship is named followed, so from the right side I am going to use the name followers to represent all the left side users that are linked to the target user in the right side. The additional lazy argument indicates the execution mode for this query. A mode of dynamic sets up the query to not run until specifically requested, which is also how I set up the posts one-to-many relationship.
  • lazy is similar to the parameter of the same name in the backref, but this one applies to the left side query instead of the right side.
Don't worry if this is hard to understand. I will show you how to work with these queries in a moment, and then everything will become clearer.
The changes to the database need to be recorded in a new database migration:

(venv) $ flask db migrate -m "followers"
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'followers'
  Generating /home/miguel/microblog/migrations/versions/ae346256b650_followers.py ... done

(venv) $ 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 37f06a334dbf -> ae346256b650, followers

Adding and Removing "follows"

Thanks to the SQLAlchemy ORM, a user following another user can be recorded in the database working with the followed relationship as if it was a list. For example, if I had two users stored in user1 and user2 variables, I can make the first follow the second with this simple statement:
user1.followed.append(user2)
To stop following the user, then I could do:
user1.followed.remove(user2)
Even though adding and removing followers is fairly easy, I want to promote reusability in my code, so I'm not going to sprinkle "appends" and "removes" through the code. Instead, I'm going to implement the "follow" and "unfollow" functionality as methods in the User model. It is always best to move the application logic away from view functions and into models or other auxiliary classes or modules, because as you will see later in this chapter, that makes unit testing much easier.
Below are the changes in the user model to add and remove relationships:

app/models.py: Add and remove followers
class User(UserMixin, db.Model):
    #...

    def follow(self, user):
        if not self.is_following(user):
            self.followed.append(user)

    def unfollow(self, user):
        if self.is_following(user):
            self.followed.remove(user)

    def is_following(self, user):
        return self.followed.filter(
            followers.c.followed_id == user.id).count() > 0
 
The follow() and unfollow() methods use the append() and remove() methods of the relationship object as I have shown above, but before they touch the relationship they use the is_following() supporting method to make sure the requested action makes sense. For example, if I ask user1 to follow user2, but it turns out that this following relationship already exists in the database, I do not want to add a duplicate. The same logic can be applied to unfollowing.

The is_following() method issues a query on the followed relationship to check if a link between two users already exists. You have seen me use the filter_by() method of the SQLAlchemy query object before, for example to find a user given its username. The filter() method that I'm using here is similar, but lower level, as it can include arbitrary filtering conditions, unlike filter_by() which can only check for equality to a constant value. The condition that I'm using in is_following() looks for items in the association table that have the left side foreign key set to the self user, and the right side set to the user argument. The query is terminated with a count() method, which returns the number of results. The result of this query is going to be 0 or 1, so checking for the count being 1 or greater than 0 is actually equivalent. Other query terminators you have seen me use in the past are all() and first().

Obtaining the Posts from Followed Users

Support for followers in the database is almost complete, but I'm actually missing one important feature. In the index page of the application I'm going to show blog posts written by all the people that are followed by the logged in user, so I need to come up with a database query that returns these posts.
The most obvious solution is to run a query that returns the list of followed users, which as you already know, it would be user.followed.all(). Then for each of these returned users I can run a query to get the posts. Once I have all the posts I can merge them into a single list and sort them by date. Sounds good? Well, not really.
This approach has a couple of problems. What happens if a user is following a thousand people? I would need to execute a thousand database queries just to collect all the posts. And then I will need to merge and sort the thousand lists in memory. As a secondary problem, consider that the application's home page will eventually have pagination implemented, so it will not display all the available posts but just the first few, with a link to get more if desired. If I'm going to display posts sorted by their date, how can I know which posts are the most recent of all followed users combined, unless I get all the posts and sort them first? This is actually an awful solution that does not scale well.
There is really no way to avoid this merging and sorting of blog posts, but doing it in the application results in a very inefficient process. This kind of work is what relational databases excel at. The database has indexes that allow it to perform the queries and the sorting in a much more efficient way that I can possibly do from my side. So what I really want is to come up with a single database query that defines the information that I want to get, and then let the database figure out how to extract that information in the most efficient way.
Below you can see this query:

app/models.py: Followed posts query
class User(db.Model):
    #...
    def followed_posts(self):
        return Post.query.join(
            followers, (followers.c.followed_id == Post.user_id)).filter(
                followers.c.follower_id == self.id).order_by(
                    Post.timestamp.desc())
 
This is by far the most complex query I have used on this application. I'm going to try to decipher this query one piece at a time. If you look at the structure of this query, you are going to notice that there are three main sections designed by the join(), filter() and order_by() methods of the SQLAlchemy query object:
Post.query.join(...).filter(...).order_by(...)

Joins

To understand what a join operation does, let's look at an example. Let's assume that I have a User table with the following contents:
idusername
1john
2susan
3mary
4david


To keep things simple I am not showing all the fields in the user model, just the ones that are important for this query.
Let's say that the followers association table says that user john is following users susan and david, user susan is following mary and user mary is following david. The data that represents the above is this:
follower_idfollowed_id
12
14
23
34
Finally, the posts table contains one post from each user:
idtextuser_id
1post from susan2
2post from mary3
3post from david4
4post from john1

This table also omits some fields that are not part of this discussion.
Here is the join() call that I defined for this query once again:
Post.query.join(followers, (followers.c.followed_id == Post.user_id))
I'm invoking the join operation on the posts table. The first argument is the followers association table, and the second argument is the join condition. What I'm saying with this call is that I want the database to create a temporary table that combines data from posts and followers tables. The data is going to be merged according to the condition that I passed as argument.
The condition that I used says that the followed_id field of the followers table must be equal to the user_id of the posts table. To perform this merge, the database will take each record from the posts table (the left side of the join) and append any records from the followers table (the right side of the join) that match the condition. If multiple records in followers match the condition, then the post entry will be repeated for each. If for a given post there is no match in followers, then that post record is not part of the join.
With the example data I defined above, the result of the join operation is:
idtextuser_idfollower_idfollowed_id
1post from susan212
2post from mary323
3post from david414
3post from david434

Note how the user_id and followed_id columns are equal in all cases, as this was the join condition. The post from user john does not appear in the joined table because there are no entries in followers that have john as a followed user, or in other words, nobody is following john. And the post from david appears twice, because that user is followed by two different users.
It may not be immediately clear what do I gain by creating this join, but keep reading, as this is just one part of the bigger query.

Filters

The join operation gave me a list of all the posts that are followed by some user, which is a lot more data that I really want. I'm only interested in a subset of this list, the posts followed by a single user, so I need trim all the entries I don't need, which I can do with a filter() call.
Here is the filter portion of the query:
filter(followers.c.follower_id == self.id)
Since this query is in a method of class User, the self.id expression refers to the user ID of the user I'm interested in. The filter() call selects the items in the joined table that have the follower_id column set to this user, which in other words means that I'm keeping only the entries that have this user as a follower.
Let's say the user I'm interested in is john, which has its id field set to 1. Here is how the joined table looks after the filtering:
idtextuser_idfollower_idfollowed_id
1post from susan212
3post from david414

And these are exactly the posts that I wanted!
Remember that the query was issued on the Post class, so even though I ended up with a temporary table that was created by the database as part of this query, the result will be the posts that are included in this temporary table, without the extra columns added by the join operation.

Sorting

The final step of the process is to sort the results. The part of the query that does that says:
order_by(Post.timestamp.desc())
Here I'm saying that I want the results sorted by the timestamp field of the post in descending order. With this ordering, the first result will be the most recent blog post.

Combining Own and Followed Posts

The query that I'm using in the followed_posts() function is extremely useful, but has one limitation. People expect to see their own posts included in their timeline of followed users, and the query as it is does not have that capability.
There are two possible ways to expand this query to include the user's own posts. The most straightforward way is to leave the query as it is, but make sure all users are following themselves. If you are your own follower, then the query as shown above will find your own posts along with those of all the people you follow. The disadvantage of this method is that it affects the stats regarding followers. All follower counts are going to be inflated by one, so they'll have to be adjusted before they are shown. The second way to do this is by create a second query that returns the user's own posts, and then use the "union" operator to combine the two queries into a single one.
After considering both options I decided to go with the second one. Below you can see the followed_posts() function after it has been expanded to include the user's posts through a union:

app/models.py: Followed posts query with user's own posts.
    def followed_posts(self):
        followed = Post.query.join(
            followers, (followers.c.followed_id == Post.user_id)).filter(
                followers.c.follower_id == self.id)
        own = Post.query.filter_by(user_id=self.id)
        return followed.union(own).order_by(Post.timestamp.desc())

Note how the followed and own queries are combined into one, before the sorting is applied.