Flaskで簡単なWebアプリを作るときに、DBの持ち方をどうするか問題にぶち当たったので、忘備録。
* Flask-SQLAlchemy
* Flask-Migrate
を使って、DBを管理している。
モデルは
* User
* Followers
というテーブルを持っている。User間のフォロー機能を実装したい。
app/init.py
from flask import Flask
from config import Config, CONSUMER_KEY, CONSUMER_SECRET, CALLBACK_URL
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from flask_login import LoginManager
app = Flask(__name__)
db = SQLAlchemy(app)
migrate = Migrate(app, db)
app.config.from_object(Config)
login = LoginManager(app)
from app import routes, models
from app.models import User
でモデルが
models.py
from datetime import datetime
from app import db
from flask_login import UserMixin
from app import login
followers = db.Table('followers',
db.Column('follower_id', db.Integer, db.ForeignKey('user.id')),
db.Column('followed_id', db.Integer, db.ForeignKey('user.id'))
)
likers = db.Table('likers',
db.Column('liker_id', db.Integer, db.ForeignKey('user.id')),
db.Column('liked_id', db.Integer, db.ForeignKey('user.id'))
)
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
registered_at = db.Column(db.DateTime, index=True, default=datetime.utcnow)
last_updated_at = db.Column(db.DateTime, index=True, default=datetime.utcnow)
last_logined_at = db.Column(db.DateTime, index=True, default=datetime.utcnow)
twitter_id = db.Column(db.String(64), index=True, unique=True)
screen_name = db.Column(db.String(64), index=True, unique=True)
username = db.Column(db.String(64), index=True, unique=True)
image_url = db.Column(db.String(128), index=True, unique=True)
description = db.Column(db.String(300), index=True, unique=True)
followed = db.relationship(
'User', secondary=followers,
primaryjoin=(followers.c.follower_id == twitter_id),
secondaryjoin=(followers.c.followed_id == twitter_id),
backref=db.backref('followers', lazy='dynamic'), lazy='dynamic')
liked = db.relationship(
'User', secondary=likers,
primaryjoin=(likers.c.liker_id == twitter_id),
secondaryjoin=(likers.c.liked_id == twitter_id),
backref=db.backref('likers', lazy='dynamic'), lazy='dynamic')
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
def mutual_ids_list(self):
print ("twitter_id".format(self.twitter_id))
followed_ids = self.followed.filter(
followers.c.followed_id == self.twitter_id)
following_ids = self.followed.filter(
followers.c.follower_id == self.twitter_id)
print ("followed_ids:".format(followed_ids))
print ("follower_ids:".format(following_ids))
print (list(set(following_ids & set(followed_ids))))
def like(self, user):
if not self.is_liking(user):
self.liked.append(user)
def unlike(self, user):
if self.is_liking(user):
self.liked.remove(user)
def is_likeing(self, user):
return self.likeed.filter(
likers.c.liked_id == user.id).count() > 0
def __repr__(self):
return '<User {}>'.format(self.twitter_id)
class Follower(db.Model):
id = db.Column(db.Integer, primary_key=True)
class Liker(db.Model):
id = db.Column(db.Integer, primary_key=True)
的な感じ。これで、pythonから多対多のモデルが作れるのか確認。
python3
>>> from app.models import User, Follower
>>> from app import db
>>> u1 = User(twitter_id=3)
>>> u2 = User(twitter_id=4)
>>> db.session.add(u1)
>>> db.session.add(u2)
>>> db.session.commit()
>>> u1.follow(u2)
>>> u2.follow(u1)
>>> db.session.commit()
>>> u1.followed.all()
[<User 4>]
できてる。sqlite3にアクセスして、followerのテーブルにデータがあるのか確認。
bash
$ sqlite3 app.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> select * from followers
...> ;
|
|
1|2
2|1
1|2
1|2
2|1
3|4
4|3
できているはず。
あとはこのfollowersテーブルにsqlalchemyからアクセスしたいんだけど、そこがようわからんので調査が必要。