Flask-SQLAlchemyの使い方

最近仕事でFlask-SQLAlchemyをよく使うので自分のためにメモしておく。

Setup

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

...
db.create_all()

Basic (Insert,Update,Delete)

Insert

user = User(name="foo")
db.session.add(user)
db.session.commit()

Update

user = db.session.query(User).get(1)
user.name = "foooo"
db.session.flush()

Delete

user = db.session.query(User).get(1)
db.session.delete(user)
db.session.commit()

//Delete children of the user
user.posts.remove()

Queryの例

all()

users = db.session.query(User).all()

filter_by(**kwargs)

user = db.session.query(User).filter_by(name="foo").first()

get(id)

user = db.session.query(User).get(1)

Relationshipの例

One to Many (一対多)

ユーザーが複数の投稿を持っているケースを想定。

class User(db.Model):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    posts = db.retionship("Post",backref="uesr",lazy="dynamic")


class Post(db.Model):
    __tablename__ = "posts"
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

Many To Many (多対多)

投稿した記事がタグを持っていて、タグは複数のタグを持っているケースを想定。

posttags = db.Table("posttags",
        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)
    tags = db.relationship("Post",secondary=posttags,backref="posts")


class Tag(db.Model):
    __tablename__ = "tags"
    id = db.Column(db.Integer, primary_key=True)

Related Contents

Pickup Contents