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文を記述しても、正しく解釈される。）
尚、
◇１対１ １対多のデータベースのリレーションについては、
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)
#...
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には、データベースファイルまでのパスだけを記述すればよい。
(SQLITE３のようにメイン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
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()
>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( )
でも、ｄｂを作れる。
*注）１つの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は、MySQLdbとPyMySQLがある。
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/[データベース名]
(2) 先にDBは作成しておかないといけない
（１）の段階でpython アプリを実行すると、今度は、MySQLに接続して後で、DBがない、とエラーに。
(1049, "Unknown database 'msg.db'")
SQLITEの場合、ここでDBを自動で作ってくれるのですが、SQLは事前に作っておかないといけないみたい。
mysql -u root -p
mysql> create database [データベース名];
このDBさえ作っておけば、テーブルはFLASK-SQLAlchemyのモデルから自動に作成される。
尚、ｄｂを作成した後に接続しようとしても
sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1049, "Unknown database 'msg.db'")
というエラーに遭遇した。localhost/[データベース名]
[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:
ちょっと別ねた＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
0 件のコメント:
コメントを投稿