【(最も参照中) FLASK SQLAlchemyを使ってみる】
のスピンオフの投稿。
投稿のページが、書きすぎて重くなったので分割して書きます。
Simple Relationships
SQLAlchemy は、relational databases の連携を強力にサポートする（らしい）のでやってみる。
上記のapp.pyに以下のDBを
追加する。
from datetime import datetime
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(80), nullable=False)
body = db.Column(db.Text, nullable=False)
pub_date = db.Column(db.DateTime, nullable=False,
default=datetime.utcnow)
category_id = db.Column(db.Integer,
db.ForeignKey('category.id'),
nullable=False)
category =
db.relationship('Category',
backref=db.
backref('posts', lazy=True))
def __repr__(self):
return '<Post
%r>' % self.title
class Category(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), nullable=False)
def __repr__(self):
return '<Category %r>' % self.name
First let’s create some objects:
>>>
from app import Post
>>>
from app import Category
>>> py = Category(name='Python')
>>> Post(title='Hello Python!', body='Python is pretty cool', category=py)
<Post 'Hello Python!'>
<-- 何故かprintされる。
>>> p = Post(title='Snakes', body='Ssssssss')
>>> py.posts.append(p)
>>>
db.session.add(py)
*最初にやった、やり方は
(*インスタンスの生成）
>>> admin = User(username='admin', email='admin@example.com')
>>> db.session.add(admin)
>>> db.session.commit()
As you can see, there is no need to add the Post objects to the session. Since the Category is part of the session all objects associated with it through relationships will be added too. It does not matter whether db.session.add() is called before or after creating these objects. The association can also be done on either side of the relationship - so a post can be created with a category or it can be added to the list of posts of the category.
Let’s look at the posts. Accessing them will load them from the database since the relationship is lazy-loaded, but you will probably not notice the difference - loading a list is quite fast:
>>>
py.posts
[<Post 'Hello Python!'>, <Post 'Snakes'>]
While
lazy-loading a relationship is fast, it can easily become a major bottleneck when you end up triggering extra queries in a loop for more than a few objects. For this case, SQLAlchemy lets you override the loading strategy on the query level. If you wanted a single query to load all categories and their posts, you could do it like this:
>>> from sqlalchemy.orm import joinedload
>>> query = Category.query.options(joinedload('posts'))
>>> for category in query:
... print category, category.posts
<Category u'Python'> [<Post u'Hello Python!'>, <Post u'Snakes'>]
If you want to get a query object for that relationship, you can do so using with_parent(). Let’s exclude that post about Snakes
for example:
>>> Post.query.with_parent(py).filter(Post.title != 'Snakes').all()
[<Post 'Hello Python!'>]
One-to-Many Relationships
The most common relationships are one-to-many relationships. Because relationships are declared before they are established you can use strings to refer to classes that are not created yet (for instance if Person defines a relationship to Address which is declared later in the file).
Relationships are expressed with the relationship() function. However the foreign key has to be separately declared with the ForeignKey class:
class Person(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), nullable=False)
addresses = db.relationship('Address', backref='person', lazy=True)
class Address(db.Model):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(120), nullable=False)
person_id = db.Column(db.Integer, db.ForeignKey('person.id'),
nullable=False)
What does db.relationship() do? That function returns a new property that can do multiple things. In this case we told it to point to the Address class and load multiple of those. How does it know that this will return more than one address? Because SQLAlchemy guesses a useful default from your declaration. If you would want to have a one-to-one relationship you can pass uselist=False to
relationship().
Since a person with no name or an email address with no address associated makes no sense, nullable=False tells SQLAlchemy to create the column as NOT NULL. This is implied for primary key columns, but it’s a good idea to specify it for all other columns to make it clear to other people working on your code that you did actually want a nullable column and did not just forget to add it.
So what do backref and lazy mean? backref is a simple way to also declare a new property on the Address class. You can then also use my_address.person to get to the person at that address. lazy defines when SQLAlchemy will load the data from the database:
*'select' / True (which is the default, but explicit is better than implicit) means that SQLAlchemy will load the data as necessary in one go using a standard select statement.
*'joined' / False tells SQLAlchemy to load the relationship in the same query as the parent using a JOIN statement.
*'subquery' works like 'joined' but instead SQLAlchemy will use a subquery.
*'dynamic' is special and can be useful if you have many items and always want to apply additional SQL filters to them. Instead of loading the items SQLAlchemy will return another query object which you can further refine before loading the items. Note that this cannot be turned into a different loading strategy when querying so it’s often a good idea to avoid using this in favor of lazy=True. A query object equivalent to a dynamic user.addresses relationship can be created using Address.query.with_parent(user)
while still being able to use lazy or eager loading on the relationship itself as necessary.
How do you define the lazy status for backrefs? By using the backref() function:
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50), nullable=False)
addresses = db.relationship('Address', lazy='select',
backref=db.backref('person', lazy='joined'))
Many-to-Many Relationships
If you want to use many-to-many relationships you will need to define a helper table that is used for the relationship. For this
helper table it is strongly recommended to not use a model but an actual table:
tags = db.Table('tags',
db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True),
db.Column('page_id', db.Integer, db.ForeignKey('page.id'), primary_key=True)
)
class Page(db.Model):
id = db.Column(db.Integer, primary_key=True)
tags = db.relationship('Tag', secondary=tags, lazy='subquery',
backref=db.backref('pages', lazy=True))
class Tag(db.Model):
id = db.Column(db.Integer, primary_key=True)
Here we configured Page.tags to be loaded immediately after loading a Page, but using a separate query. This always results in two queries when retrieving a Page, but when querying for multiple pages you will not get additional queries.
The list of pages for a tag on the other hand is something that’s rarely needed. For example, you won’t need that list when retrieving the tags for a specific page. Therefore, the backref is set to be lazy-loaded so that accessing it for the first time will trigger a query to get the list of pages for that tag. If you need to apply further query options on that list, you could either switch to the 'dynamic' strategy - with the drawbacks mentioned above - or get a query object using Page.query.with_parent(some_tag) and then use it exactly as you would with the query object from a dynamic relationship.
Step1 : Creating Models
A Model is a Python class which represents the database table and its attributes map to the column of the table. A model class
inherits from db.Model and defines columns as an instance of db.Column class.
プロジェクト・ディレクトリ/main2.py
#...
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
#...
class Category(db.Model):
__tablename__ = 'categories'
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(255), nullable=False)
slug = db.Column(db.String(255), nullable=False)
created_on = db.Column(db.DateTime(), default=datetime.utcnow)
def __repr__(self):
return "<{}:{}>".format(id, self.name)
class Post(db.Model):
__tablename__ = 'posts'
id = db.Column(db
.Integer(), primary_key=True)
title = db.Column(db
.String(255), nullable=False)
slug = db.Column(db.String(255), nullable=False)
content = db.Column(db
.Text(), nullable=False)
created_on = db.Column(db
.DateTime(), default=datetime.utcnow)
updated_on = db.Column(db.DateTime(), default=datetime.utcnow, onupdate=datetime.utcnow)
def __repr__(self):
return "<{}:{}>".format(self.id, self.title[:10])
・
__tablename__ は、テーブル名を指定するクラス変数。 指定しなければ、SQLAlchemyは、modelの名前を自動的に使うが、複数系の名前はつけられない。
・db.Columnの代表的なConstraints.
nullable When set to False makes the column required. Its default value is True.
index A boolean attribute. If set to True creates an indexed column.
primary_key A boolean attribute. If set to True marks the column as the primary key of the table.
unique A boolean attribute. If set to True each value in the column must be unique.
You might have noticed that we have set the default value of created_on and updated_on to a method name ( datetime.utcnow ) instead of calling the method ( datetime.utcnow() ). This is because we don’t want to call datetime.utcnow() method when the code is executed. Instead, we want it to call it when an actual record is added or updated.
Defining Relationship
In the previous section, we have created a Post model with a couple of fields. In the real world, however, model classes seldom
exist on their own. Most of the time they are connected with other models through various relationships like one-to-one, one-to-many
and many-to-many.
Let’s expand on the analogy of a blog site. Generally, a blog post belongs to a category and one or more tags. In other words,
there is a one-to-many relationship between a category and a post and a many-to-many relationship between a post and a tag. The following figure demonstrates this relationship.
flask_app/models.py
#...
class Category(db.Model):
__tablename__ = 'categories'
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(255), nullable=False)
slug = db.Column(db.String(255), nullable=False)
created_on = db.Column(db.DateTime(), default=datetime.utcnow)
posts =
db.relationship('
Post',
backref='category')
def __repr__(self):
return "<{}:{}>".format(id, self.name)
class Post(db.Model):
__tablename__ = 'posts'
id = db.Column(db.Integer(), primary_key=True)
title = db.Column(db.String(255), nullable=False)
slug = db.Column(db.String(255), nullable=False)
content = db.Column(db.Text(), nullable=False)
created_on = db.Column(db.DateTime(), default=datetime.utcnow)
updated_on = db.Column(db.DateTime(), default=datetime.utcnow, onupdate=datetime.utcnow)
category_id = db.Column(db.Integer(), db.
ForeignKey('categories.id'))
class Tag(db.Model):
__tablename__ = 'tags'
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(255), nullable=False)
slug = db.Column(db.String(255), nullable=False)
created_on = db.Column(db.DateTime(), default=datetime.utcnow)
def __repr__(self):
return "<{}:{}>".format(id, self.name)
#...
One to Many Relationship (between a category and a post)
*db.relationship()：２つのテーブルが関連していることを明示。model classと関連するobjectsを指定。
*db.ForeignKey() : one to manyのmany側に記述する。参照するテーブルのcolumn名を指定する。db.relationship()は、one側、many側のどちらに記述しても良い。
db.relationship()がある方を親クラス、db.ForeignKey()があるクラスを子クラスと呼ぶことがある。
*
backref='category'
Now if we have a Category object (say c) then we can access all posts under it as c.posts. What if you want to access the data from the other side of the relationship i.e get category from a post object? This is where backref comes into play. So the code:
posts = db.relationship('Post', backref='category')
adds a category attribute to the Post object. That means if we have a Post object (say p) then we can access its category as
p.category.
The category and posts attributes on Post and Category object only exist for your convenience they are not actual columns in the
table.
Many to Many Relationship
many-to-many relationship を作る際には、もう一つ、
association table と呼ばれるテーブルを作る。
以下の例では、post.id と tag.id を含む、post_tagsという名前のアソシエーション・テーブルを作る。
A many-to-many relationship between the post and tags is implemented as a two one-to-many relationship. The first one-to-one relationship is between
①posts and post_tags table and the second is between
②tags and post_tags table.
flask_app/main2.py
# ...
class Category(db.Model):
# ...
① post_tags = db.Table('post_tags',
db.Column('post_id', db.Integer, db.ForeignKey('posts.id')),
db.Column('tag_id', db.Integer, db.ForeignKey('tags.id'))
)
class Post(db.Model):
__tablename__ = 'posts'
id = db.Column(db.Integer(), primary_key=True)
# ...
class Tag(db.Model):
# ...
created_on = db.Column(db.DateTime(), default=datetime.utcnow)
posts = db.relationship('Post', ②secondary=post_tags, backref='tags')
#...
① db.Table(): post_tagsという名前のアソシエーション・テーブルをdb.Table()のオブジェクトとして定義する。(Syntaxは、SQLAlchemyのもの
で、通常のclassの定義とは異なる。) db.Table()の最初の引数はテーブル名。次の引数は、参照されるテーブル Columnのインスタンス。.
②secondary=post_tags
モデル・classが使用するアソシエーション・テーブルの指定する。
Let’s say we have a Post object p, then we can access all its tags as p.tags. Similarly, Given a Tag object t, we can access all
the posts under it as t.posts.
One to One relationship
One to Manyモデルとの唯一の違いは、以下のように、argument uselist=False をdb.relationship() をディレクティブの中に指定する。
（従業員と免許書IDを関連づけたモデルの例）
class Employee(db.Model):
__tablename__ = 'employees'
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(255), nullable=False)
designation = db.Column(db.String(255), nullable=False)
doj = db.Column(db.Date(), nullable=False)
dl = db.relationship('DriverLicense', backref='employee', uselist=False)
class DriverLicense(db.Model):
__tablename__ = 'driverlicense'
id = db.Column(db.Integer(), primary_key=True)
license_number = db.Column(db.String(255), nullable=False)
renewed_on = db.Column(db.Date(), nullable=False)
expiry_date = db.Column(db.Date(), nullable=False)
employee_id = db.Column(db.Integer(), db.ForeignKey('employees.id'))
Now if we have an Employee object e then e.dl would return a DriverLicense object. If we hadn’t passed uselist=False to the db.relationship() directive then the relationship between Employee and DriverLicense would be one-to-many and e.dl would return a list of DriverLicense objects instead of a single object. The uselist=False argument doesn’t have any effect on the employee attribute of the DriverLicense object. As usual, it will return a single object.
Now its time to create our database and tables inside it.
